excel 連接 SQL 2008語法 如何改進 與迴圈
暱稱: joey0415
中學生
- 帖子
- 361
- 主題
- 57
- 精華
- 0
- 積分
- 426
- 點名
- 0
- 作業系統
- win7
- 軟體版本
- 2003,2010
- 閱讀權限
- 20
- 性別
- 男
- 註冊時間
- 2010-5-13
- 最後登錄
- 2022-12-8
|
excel 連接 SQL 2008語法 如何改進 與迴圈
小弟利用EXCEL讀取SQL2008的資料，想要從資料庫中抓出來計算，最後想再按照股票代碼跑迴圈，下面是小弟錄製的代碼，我覺得好像可以更簡單一些，請高手指點一下，當然錄製的代碼是2330，最後2330會用成變數來跑，不知道有人可以讓下面的連接方式更好或更快一些！謝謝
小弟只會這樣寫，當然有高手可以幫小弟改的更漂亮一些- Sub 股票資料1()
- '
- '
- Sheets("Sheet1").Select
- Range("A1").Select
- With ActiveSheet.QueryTables.Add(Connection:= _
- "ODBC;DRIVER=SQL Server;SERVER=.;UID=sa;PWD=WXYZ1234;APP=Microsoft Office 2003;WSID=CS5111;DATABASE=stock" _
- , Destination:=Range("A1"))
- .CommandText = Array( _
- "select * from (SELECT TOP 12 * FROM stock.dbo.StockStore WHERE stockid=2330 ORDER BY sdate DESC) AAA ORDER BY sdate")
- .Name = "來自 "
- .FieldNames = True
- .RowNumbers = False
- .FillAdjacentFormulas = False
- .PreserveFormatting = True
- .RefreshOnFileOpen = False
- .BackgroundQuery = True
- .RefreshStyle = xlInsertDeleteCells
- .SavePassword = True
- .SaveData = True
- .AdjustColumnWidth = True
- .RefreshPeriod = 0
- .PreserveColumnInfo = True
- .Refresh BackgroundQuery:=False
- End With
- End Sub
- Sub 股票資料2()
- '
- '
- '
- Sheets("Sheet2").Select
- Range("A1").Select
- With ActiveSheet.QueryTables.Add(Connection:= _
- "ODBC;DRIVER=SQL Server;SERVER=.;UID=sa;PWD=WXYZ1234;APP=Microsoft Office 2003;WSID=CS5111;DATABASE=stock" _
- , Destination:=Range("A1"))
- .CommandText = Array( _
- " select * from (SELECT TOP 12 sdate,stockid, CreditMoney,CreditTick FROM stock.dbo.CreditExchangeMonth WHERE stockid=2330 ORDER BY sdate DESC) AAA ORDER BY sdate" _
- )
- .Name = "來自 "
- .FieldNames = True
- .RowNumbers = False
- .FillAdjacentFormulas = False
- .PreserveFormatting = True
- .RefreshOnFileOpen = False
- .BackgroundQuery = True
- .RefreshStyle = xlInsertDeleteCells
- .SavePassword = True
- .SaveData = True
- .AdjustColumnWidth = True
- .RefreshPeriod = 0
- .PreserveColumnInfo = True
- .Refresh BackgroundQuery:=False
- End With
- End Sub
|
|
|
|
|
|
|
|
暱稱: joey0415
中學生
- 帖子
- 361
- 主題
- 57
- 精華
- 0
- 積分
- 426
- 點名
- 0
- 作業系統
- win7
- 軟體版本
- 2003,2010
- 閱讀權限
- 20
- 性別
- 男
- 註冊時間
- 2010-5-13
- 最後登錄
- 2022-12-8
|
3#
發表於 2010-6-4 21:40
| 只看該作者
本帖最後由 joey0415 於 2010-6-4 21:44 編輯
[quote]回復 joey0415
查詢已經存在不再使用Add
假設代號是2030~2032
就同一個查詢使之變化- # Sub 股票資料1()
- # For Each MyID In Array("2030","2031",2032")
- # With Sheets("Sheet1").QueryTables(1)
- # .CommandText = Array( _
- # "select * from (SELECT TOP 12 * FROM stock.dbo.StockStore WHERE stockid=" & MyID & " ORDER BY sdate DESC) AAA ORDER BY sdate")
- # .Name = "來自 "
- # .FieldNames = True
- # .RowNumbers = False
- # .FillAdjacentFormulas = False
- # .PreserveFormatting = True
- # .RefreshOnFileOpen = False
- # .BackgroundQuery = True
- # .RefreshStyle = xlInsertDeleteCells
- # .SavePassword = True
- # .SaveData = True
- # .AdjustColumnWidth = True
- # .RefreshPeriod = 0
- # .PreserveColumnInfo = True
- # .Refresh BackgroundQuery:=False
- # End With
- # Next
|
|
|
|
|
|
|
|
發表於 2010-6-4 18:31
| 
