標題: vba匯入券商進出表 [打印本頁]

---

作者: lman    時間: 2010-8-21 19:57     標題: vba匯入券商進出表

[attach]2518[/attach]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    時間: 2010-8-21 21:29

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

1. Sub GetTransInfo()
   
2. Worksheets("sheet2").Activate
   
3. Call 券商進出("2885", "sheet2", "A1")
   
4. 'Call 券商進出("2409", "sheet2", "A60")
   
5. End Sub
   
6. Sub 券商進出(stock As String, tsheet As String, tcell As String)
   
7.     '"all&stockid",
   
8.     Call 券商進出2("8,10,11", stock, tsheet, tcell)
   
9. End Sub
   
10. 
    
11. '(all&stockid,
    
12. Sub 券商進出2(tbl As String, stock As String, tsheet As String, tcell As String)
    
13.      With Worksheets(tsheet).QueryTables.Add(Connection:="URL;http://yamstock.megatime.com.tw/asp/stockinfo/ps_top.asp?m=all&stockid=" & _
    
14.         stock & "&name1=D2&index1=6", _
    
15.         Destination:=Worksheets(tsheet).Range(tcell))
    
16.         .Name = all & "_" & stock
    
17.          .FieldNames = True
    
18.         .FieldNames = True
    
19.         .RowNumbers = False
    
20.         .FillAdjacentFormulas = False
    
21.         .PreserveFormatting = True
    
22.         .RefreshOnFileOpen = False
    
23.         .BackgroundQuery = False
    
24.         .RefreshStyle = xlOverwriteCells
    
25.         .SavePassword = False
    
26.         .SaveData = True
    
27.         .AdjustColumnWidth = True
    
28.         .RefreshPeriod = 0
    
29.         .WebSelectionType = xlSpecifiedTables
    
30.         .WebFormatting = xlWebFormattingNone
    
31.         .WebTables = tbl
    
32.         .WebPreFormattedTextToColumns = True
    
33.         .WebConsecutiveDelimitersAsOne = True
    
34.         .WebSingleBlockTextImport = False
    
35.         .WebDisableDateRecognition = False
    
36.         .WebDisableRedirections = False
    
37.         .Refresh BackgroundQuery:=False
    
38.     End With
    
39. 
    
40.     End Sub

複製代碼

---

作者: lman    時間: 2010-8-22 03:48

感謝版主kimbal
正確解決無誤
版主回覆
你可以試試一句一句試試了解一下意思
是呀新手就是一個字一個字的學習

---

作者: b4069588    時間: 2011-1-1 01:00

恩   版主用心了  謝謝

---

作者: FAlonso    時間: 2011-1-3 15:08

第8行的"8,10,11"
第16行的.Name = all & "_" & stock
第31行的.WebTables = tbl
是什麼意思?

---

歡迎光臨 麻辣家族討論版版 (http://forum.twbts.com/)