vba匯入券商進出表

thanks.rar (12.69 KB) http://yamstock.megatime.com.tw/asp/stockinfo/ps_top.asp?m=all&stockid=3474&name1=D2&index1=6
這是yan券商進出表
想用vba匯入excel 指定工作簿及存儲格
如2888新光金匯入"sheet2", "A1"
2409友達匯入"sheet2", "A60"
以下程式碼是我網路搜尋並依我的需要自行俢改
但是還是無法執行
請前輩幫忙看看
到底那裡錯誤謝謝

Sub GetTransInfo()
Worksheets("sheet2").Activate
Call 券商進出("2888", "sheet2", "A1")
Call 券商進出("2409", "sheet2", "A60")
End Sub
   Sub 券商進出(stock As String, tsheet As String, tcell As String)
Call券商進出("all&stockid", "8,10,11", stockinfo, tsheet, tcell)
End Sub
Sub券商進出(all&stockid, tbl As String, stock As String, tsheet As String, tcell As String)
   With Worksheets(tsheet).QueryTables.Add(Connection:="URL;http://yamstock.megatime.com.tw/asp/stockinfo/ps_top.asp?m=all&stockid=" & _
   stockNo &  "&name1=D2&index1=6, _
      .Destination:=Worksheets(tsheet).Range(tcell))
      .Name = all & "_" & stock
      .FieldNames = True
      .FieldNames = True
      .RowNumbers = False
      .FillAdjacentFormulas = False
      .PreserveFormatting = True
      .RefreshOnFileOpen = False
      .BackgroundQuery = False
      .RefreshStyle = xlOverwriteCells
      .SavePassword = False
      .SaveData = True
      .AdjustColumnWidth = True
      .RefreshPeriod = 0
      .WebSelectionType = xlSpecifiedTables
      .WebFormatting = xlWebFormattingNone
      .WebTables = tbl
      .WebPreFormattedTextToColumns = True
      .WebConsecutiveDelimitersAsOne = True
      .WebSingleBlockTextImport = False
      .WebDisableDateRecognition = False
      .WebDisableRedirections = False
      .Refresh BackgroundQuery:=False
End With

End Sub

樓主熱帖

kimbal

版主 Rank: 7 Rank: 7 Rank: 7

帖子: 472
主題: 5
精華: 0
積分: 485
點名: 0
作業系統: Windows
軟體版本: MS Office
閱讀權限: 100
性別: 男
來自: 香港
註冊時間: 2010-7-4
最後登錄: 2014-12-28

論壇貢獻勳章

2^#

發表於 2010-8-21 21:29 | 只看該作者

你可以試試一句一句試試了解一下意思

Sub GetTransInfo()
Worksheets("sheet2").Activate
Call 券商進出("2885", "sheet2", "A1")
'Call 券商進出("2409", "sheet2", "A60")
End Sub
Sub 券商進出(stock As String, tsheet As String, tcell As String)
'"all&stockid",
Call 券商進出2("8,10,11", stock, tsheet, tcell)
End Sub
'(all&stockid,
Sub 券商進出2(tbl As String, stock As String, tsheet As String, tcell As String)
With Worksheets(tsheet).QueryTables.Add(Connection:="URL;http://yamstock.megatime.com.tw/asp/stockinfo/ps_top.asp?m=all&stockid=" & _
stock & "&name1=D2&index1=6", _
Destination:=Worksheets(tsheet).Range(tcell))
.Name = all & "_" & stock
.FieldNames = True
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = False
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = tbl
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End Sub