- 帖子
- 23
- 主題
- 1
- 精華
- 0
- 積分
- 58
- 點名
- 0
- 作業系統
- windows
- 軟體版本
- Office 2016
- 閱讀權限
- 20
- 性別
- 男
- 註冊時間
- 2014-2-26
- 最後登錄
- 2018-8-23
|
- Sub Ex1()
- Cells.Clear
- Dim Ar(), a As Variant, i As Integer, stkno As String, Qur As String, DateVar As Integer, Sh As Worksheet
- With CreateObject("InternetExplorer.Application")
- .Navigate "http://www.tdcc.com.tw/smWeb/QryStock.jsp"
- Do While .Busy Or .ReadyState <> 4: DoEvents: Loop
- Set a = .Document.ALL.tags("option") '資料日期的內容
- ReDim Ar(a.Length - 1)
- For i = 0 To a.Length - 1
- Ar(i) = a(i).innerHTML
- Next
- .Quit
- End With
- stkno = Sheets("目標股票").Range("B2").Value '
- If stkno = "" Then Exit Sub
- Set Sh = ActiveSheet '指定工作表
- With Sh
- For DateVar = 0 To UBound(Ar)
- Qur = "http://www.tdcc.com.tw/smWeb/QryStock.jsp?SCA_DATE=" & Ar(DateVar) & "&SqlMethod=StockNo&StockNo=" & stkno & "&StockName=&sub=%ACd%B8%DF"
- .QueryTables.Add "URL;" & Qur, .Cells(1 + (DateVar * 32), "A")
- '.Cells(1 + (DateVar * 32), "A") A欄間隔 32列
- With .QueryTables(1)
- .WebSelectionType = xlSpecifiedTables
- .WebFormatting = xlWebFormattingNone
- .WebTables = "6,7,8"
- .WebPreFormattedTextToColumns = True
- .WebConsecutiveDelimitersAsOne = True
- .WebSingleBlockTextImport = False
- .WebDisableDateRecognition = False
- .WebDisableRedirections = False
- .PreserveFormatting = False
- .Refresh BackgroundQuery:=False
- Sh.Names(.Name).Delete '刪掉工作表上的名稱
- .Delete '刪掉這QueryTable
- End With
- Next
- End With
|
|
發表於 2017-7-15 15:42
| 
