- 帖子
- 23
- 主題
- 1
- 精華
- 0
- 積分
- 58
- 點名
- 0
- 作業系統
- windows
- 軟體版本
- Office 2016
- 閱讀權限
- 20
- 性別
- 男
- 註冊時間
- 2014-2-26
- 最後登錄
- 2018-8-23
|
25#
發表於 2015-11-11 14:50
| 只看該作者
版大:我執行後,網頁資料都是從第一列開始排列,在執行到下一季時會把資料往右推
,但遇到財報第四季時,因其格式與其他三季不同,導致更新資料起始不同而改變資料位置
,所以我需要把每一季的下載位置,都放在固定儲存位置,資料才不會每次都不一樣
在勞煩版大了^^謝謝
Dim URL As String, xCo_Id As String, xSyear As String, xSseason As String
K = Application.InputBox("請輸入股票代號")
X = Application.InputBox("請輸入最新年度")
Y = X - 1
Z = X - 2
xCo_Id = "" & K '要求輸入網頁的參數:股票代號
xSyear = "" & Z 'Format(Date, "e")->中華民國的年度
xSseason = "2" 'Format(Date, "q")->當年度的季別
URL = "URL;http://mops.twse.com.tw/server-java/t164sb01?step=1&CO_ID=" & xCo_Id & "&SYEAR=" & xSyear & "&SSEASON=" & xSseason & "&REPORT_ID=C"
With ActiveSheet.QueryTables.Add(Connection:=URL, Destination:=Range("A1"))
.AdjustColumnWidth = True '自動調整欄寬
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "2,3,4" '資產負債表,綜合損益表,現金流量表
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
xCo_Id = "" & K '要求輸入網頁的參數:股票代號
xSyear = "" & Z 'Format(Date, "e")->中華民國的年度
xSseason = "3" 'Format(Date, "q")->當年度的季別
URL = "URL;http://mops.twse.com.tw/server-java/t164sb01?step=1&CO_ID=" & xCo_Id & "&SYEAR=" & xSyear & "&SSEASON=" & xSseason & "&REPORT_ID=C"
With ActiveSheet.QueryTables.Add(Connection:=URL, Destination:=Range("A1"))
.AdjustColumnWidth = True '自動調整欄寬
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "2,3,4" '資產負債表,綜合損益表,現金流量表
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
xCo_Id = "" & K '要求輸入網頁的參數:股票代號
xSyear = "" & Z 'Format(Date, "e")->中華民國的年度
xSseason = "4" 'Format(Date, "q")->當年度的季別
URL = "URL;http://mops.twse.com.tw/server-java/t164sb01?step=1&CO_ID=" & xCo_Id & "&SYEAR=" & xSyear & "&SSEASON=" & xSseason & "&REPORT_ID=C"
With ActiveSheet.QueryTables.Add(Connection:=URL, Destination:=Range("A1"))
.AdjustColumnWidth = True '自動調整欄寬
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "2,3,4" '資產負債表,綜合損益表,現金流量表
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
xCo_Id = "" & K '要求輸入網頁的參數:股票代號
xSyear = "" & Y 'Format(Date, "e")->中華民國的年度
xSseason = "1" 'Format(Date, "q")->當年度的季別
URL = "URL;http://mops.twse.com.tw/server-java/t164sb01?step=1&CO_ID=" & xCo_Id & "&SYEAR=" & xSyear & "&SSEASON=" & xSseason & "&REPORT_ID=C"
With ActiveSheet.QueryTables.Add(Connection:=URL, Destination:=Range("A1"))
.AdjustColumnWidth = True '自動調整欄寬
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "2,3,4" '資產負債表,綜合損益表,現金流量表
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With |
|