精選
[Excel VBA] 從 Yam 天空財金網匯入「券商進出」資料
2009/05/01 22:26
瀏覽15,272
迴響4
推薦0
引用0
應網友的要求寫了這個程式，底下 SaveTrans() 會新增一張工作表，然後從 Yam 天空財金網站把指定股號的 (當日) "券商進出" 資料匯入工作表，用法就像 GetTransInfo() 裡的方式。
Sub GetTransInfo()
SaveTrans ("2412")
SaveTrans ("2330")
End Sub
Sub SaveTrans(stockNo As String)
ActiveWorkbook.Worksheets.Add
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://yamstock.megatime.com.tw/asp/stockinfo/ps_top.asp?m=all&stockid=" & _
stockNo & "&name1=D2&index1=6", _ Destination:=ActiveSheet.Range("A1"))
.Name = "Part1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "8"
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://yamstock.megatime.com.tw/asp/stockinfo/ps_top.asp?m=all&stockid=" & _
stockNo & "&name1=D2&index1=6" , _
Destination:=ActiveSheet.Range("A3"))
.Name = "Part2"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "10"
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
RowToCut = ActiveSheet.[A65536].End(xlUp).Row + 1
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://yamstock.megatime.com.tw/asp/stockinfo/ps_top.asp?m=all&stockid=" & _
stockNo & "&name1=D2&index1=6" _
, Destination:=ActiveSheet.[A65536].End(xlUp).Offset(1, 0))
.Name = "Part3"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "11"
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
Rows(RowToCut).Delete Shift:=xlUp
End Sub
Sub GetTransInfo()
SaveTrans ("2412")
SaveTrans ("2330")
End Sub
Sub SaveTrans(stockNo As String)
ActiveWorkbook.Worksheets.Add
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://yamstock.megatime.com.tw/asp/stockinfo/ps_top.asp?m=all&stockid=" & _
stockNo & "&name1=D2&index1=6", _ Destination:=ActiveSheet.Range("A1"))
.Name = "Part1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "8"
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://yamstock.megatime.com.tw/asp/stockinfo/ps_top.asp?m=all&stockid=" & _
stockNo & "&name1=D2&index1=6" , _
Destination:=ActiveSheet.Range("A3"))
.Name = "Part2"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "10"
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
RowToCut = ActiveSheet.[A65536].End(xlUp).Row + 1
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://yamstock.megatime.com.tw/asp/stockinfo/ps_top.asp?m=all&stockid=" & _
stockNo & "&name1=D2&index1=6" _
, Destination:=ActiveSheet.[A65536].End(xlUp).Offset(1, 0))
.Name = "Part3"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "11"
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
Rows(RowToCut).Delete Shift:=xlUp
End Sub
你可能會有興趣的文章：
迴響（4） ：
4樓. 偉2009/08/31 23:53YAM網頁要登入會員才能進入
想請教格主~
現在YAM的股票網頁要先登入會員才能進入
如果小弟想讓EXCEL自動去匯入資料的話~(小弟想匯入的是自選股的部分)
似乎會無法進入
不知道這能解決嗎??
讓EXCEL去YAM網頁抓資料時能夠不卡在輸入帳號密碼哪??
或是會自動輸入~再跳到要抓取的頁面完成資料的抓取-
3樓. gohann2009/07/19 21:56excel回應語法錯誤
With ActiveSheet.QueryTables.Add(Connection:=_"URL;http//yanstock.megatime.com.tw/asp/stockinfo/ps_top.asp?m=all&name=D2&index1=6",_
Destination:ActiveSheet.Range("A1"))抱歉嘗試輸入但這一段excel回應是語法錯誤所以特來請教
網友gohann來信，已email回覆，內容貼在這裡：
在 VB 裡，若一個敘述句太長，我們可以把它分成數列，而用 _ (undercore 底線) 來連接它，例如：
原為
AAA BBB CCC
分成兩列可寫成：
AAA BBB _
CCC
來信中紅字的部份，是因您在列尾少打了 _ ，或 _ 不打在列尾的緣故。 ThisIsTheWay 於 2009/07/20 10:45回覆 -
2樓. Tony2009/07/01 13:55如何將Yam 天空財金網的"現金流量年報表(累計)"匯入EXCEL!?
請問格主:
如何將Yam 天空財金網的"現金流量年報表(累計)"匯入EXCEL!?
http://yamstock.money-link.com.tw/asp/stockinfo/GetReport.asp?Select_Table=html\YFinacs&StockID=1101
之前我都是用iqy的方式將一些網頁資料匯入到excel,然後再自行修改成可輸入股票代號自動更新,可是偏偏yam的網站不能直接匯入到excel,遍尋各大網站無所解,之後看了格主的所有文章後,決定自己用VBA試看看,結果......還是搞不出來,只好向格主您求救啦!!感謝!!
抱歉，您提供的 URL 我總是連不上，近日比較沒空，有空再試！ ThisIsTheWay 於 2009/07/06 00:46回覆 -
1樓. 呆呆2009/05/03 09:29請問大大假如要下載全部的股票號碼要如何宣告
大大你寫的vba匯入「券商進出」資料
只宣告2個股票號碼
那假如要下載全部的股票號碼
要如何宣告
可以教我嗎
