12
返回列表 上一主題 發帖

請教有關ADODB.STREAM和Microsoft.XMLHTTP的用法

小俠客

帖子
83 
主題
17 
精華
0 
積分
105 
點名
0  
作業系統
XP 
軟體版本
XP 
閱讀權限
20 
註冊時間
2010-12-28 
最後登錄
2018-8-8 
1# 跳轉到 » 倒序看帖
打印
tT
發表於 2012-8-30 00:14 | 只看該作者

請教有關ADODB.STREAM和Microsoft.XMLHTTP的用法

我打算將YAHOO FINANCE提供的CSV檔案下載作分析，於是在網上找到了混合用ADODB.STREAM和Microsoft.XMLHTTP來下載的方法，但我發現資料實在太多，根本不能方便使用。
所以打算把資料匯入ACCESS作整理，並根據網上的教學寫了下面的CODE：
  1. Sub GetPrice()
  2. Dim wks As Workspace
  3. Dim dbs As Database
  4. Dim rs As DAO.Recordset
  5. Dim mySQL As String, myUrl As String
  6. Dim WinHttpReq As Object

  8. Set wks = DBEngine.Workspaces(0)
  9. Set dbs = wks.OpenDatabase("D:\Shares\temp.mdb")

  11. shareNo = Format(1, "0000")

  13. myUrl = "http://ichart.finance.yahoo.com/table.csv?s=" & shareNo & ".HK&a=09&b=27&c=2001&d=07&e=29&f=2012&g=d&ignore=.csv"

  15. T1 = "D:\Shares\temp01\" & shareNo & ".csv"

  17. Set WinHttpReq = CreateObject("Microsoft.XMLHTTP")
  18. WinHttpReq.Open "GET", myUrl, False
  19. WinHttpReq.Send

  21. If WinHttpReq.Status = 200 Then
  22.     Set oStream = CreateObject("ADODB.Stream")
  23.     oStream.Open
  24.     oStream.Type = 1
  25.     oStream.Write WinHttpReq.ResponseBody
  26.     oStream.SaveToFile T1
  27.     oStream.Close
  28. End If


  31. Set r1 = Workbooks.Open(T1)
  32. Set r2 = r1.Sheets(1).Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious)

  34. If Not r2 Is Nothing Then
  35.     r = r2.Row
  36.    
  37.     mySQL = "insert into Price select * from (select " & shareNo & " as Share, Date, Open, High, Low, Close, Volume as Vol, [Adj Close] as AClose from [Excel 8.0;hdr=yes;imex=1;Database=" & T1 & "].[" & shareNo & "$A1:G" & r & "])"

  39.     dbs.Execute (mySQL)

  41.     r1.Close
  42.     Kill T1
  43. End If


  46. End Sub
複製代碼
但這段CODE有些問題，希望各位可以多加指教：
1) 我不太明白ADODB.STREAM和Microsoft.XMLHTTP那部份的意思，網上和我手上的參考書都沒有提及，實在不了解當中的運作
2) 我這段CODE的流程應該是下載那個CSV，經過Microsoft.XMLHTTP讀取資料，再用ADODB.STREAM寫成一個CSV，然後我讀取CSV的資料，加上SHARENO，IMPORT到ACCESS，最後把CSV刪掉。
請問當中
讀取資料>儲存成CSV>讀取CSV>寫入ACCESS>刪除CSV的過程
能否改成，
讀取資料>>寫入ACCESS?
謝謝各位
收藏 分享

HSIEN6001

帖子
219 
主題
24 
精華
0 
積分
243 
點名
0  
作業系統
Windows10 
軟體版本
Office2016 
閱讀權限
20 
性別
女 
註冊時間
2012-4-18 
最後登錄
2022-2-7 

論壇榮譽勳章論壇贊助勳章
2#
發表於 2012-8-30 15:09 | 只看該作者
回復 1# 小俠客

試試!
Excel VBA：
ThisWorkbook貼上
  1. Private Sub Workbook_Open()
  2.     SaveCSV
  3.     AccessModule
  4.     Kill "D:\Sharestemp01\shareNo.csv"
  5. End Sub
複製代碼
Excel Module：貼上
  1. Sub SaveCSV()
  2. Dim xml As Object
  3. Dim stream
  4. Dim URL As String
  5. Dim path As String
  6. path = "D:\Sharestemp01\"

  8. Set xml = CreateObject("Microsoft.XMLHTTP")
  9. Set stream = CreateObject("ADODB.stream")
  10.     URL = "http://ichart.finance.yahoo.com/table.csv?s=" & "shareNo" & ".HK&a=09&b=27&c=2001&d=07&e=29&f=2012&g=d&ignore=.csv"
  11.         xml.Open "GET", URL, 0
  12.         xml.send
  13.     With stream
  14.         .Type = 1
  15.         .Open
  16.         .Write xml.responseBody
  17.         If Dir(path & "shareNo" & ".csv") <> "" Then Kill (path & "shareNo" & ".csv")
  18.         .SaveToFile (path & "shareNo" & ".csv")
  19.         .Close
  20.     End With
  21. Set stream = Nothing
  22. Set xml = Nothing
  23. End Sub

  25. Sub AccessModule()
  26. Dim A As Object
  27. Application.DisplayAlerts = False
  28. Set A = CreateObject("Access.Application")
  29.         A.Visible = False
  30.         A.OpenCurrentDatabase filepath:="D:\Shares\temp.mdb", Exclusive:=False, bstrPassword:=""
  31.         A.Run "InPutData"
  32.     Application.DisplayAlerts = True
  33.     A.CloseCurrentDatabase
  34.     A.Quit
  35. Set A = Nothing
  36. End Sub
複製代碼
Temp.mdb 新增Module：貼上
  1. Option Compare Database

  3. Function InPutData()
  4.     DoCmd.SetWarnings False
  5.     DoCmd.TransferText acImportDelim, "", "Table", "D:\Sharestemp01\shareNo.csv", True, ""
  6. End Function
複製代碼

TOP

GBKEE

帖子
5923 
主題
13 
精華
1 
積分
5986 
點名
0  
作業系統
win10 
軟體版本
Office 2010 
閱讀權限
150 
性別
男 
來自
台灣基隆 
註冊時間
2010-5-1 
最後登錄
2022-1-23 

2017 雞年全勤獎章2016猴年全勤獎章2015羊年全勤獎章2014馬年全勤獎章2013蛇年全勤獎章2012龍年全勤獎章論壇貢獻勳章熱烈參與勳章2011兔年全勤獎章
3#
發表於 2012-8-30 16:21 | 只看該作者
回復 1# 小俠客
試試看

test.rar (224.78 KB)

TOP

小俠客

帖子
83 
主題
17 
精華
0 
積分
105 
點名
0  
作業系統
XP 
軟體版本
XP 
閱讀權限
20 
註冊時間
2010-12-28 
最後登錄
2018-8-8 
4#
發表於 2012-8-31 02:23 | 只看該作者
回復 2# HSIEN6001


謝謝你的指教，看起來你寫的CODE比我工整多了。
但我看到你好像把我的code分成三部份，第一個命令是當開這個EXCEL FILE時啟動
之後在"Microsoft.XMLHTTP"部份讀取CSV，先檢查檔案是否存在，如存在便先刪掉再建立一個新的(這個太好了，現時我是手動刪除的，謝謝)
然後用EXCEL VBA CALL ACCESS行MODULE，對嗎？

但當中有兩個問題，由於YAHOO提供的CSV沒有股票編號，如果不在寫入ACCESS時加上便無法確認這個RECORD是那只股票
另外，這個寫法還是需要，讀取YAHOO資料>儲存成CSV>讀取CSV>寫入ACCESS>刪除CSV
我上面的CODE，其實是可以做到的，但我希望當用"Microsoft.XMLHTTP"讀取YAHOO資料時，能夠不用儲存成CSV，直接把資料寫到ACCESS中，不用不斷重複建立和刪除CSV(因為我會抓數百間公司資料)。

TOP

小俠客

帖子
83 
主題
17 
精華
0 
積分
105 
點名
0  
作業系統
XP 
軟體版本
XP 
閱讀權限
20 
註冊時間
2010-12-28 
最後登錄
2018-8-8 
5#
發表於 2012-8-31 02:25 | 只看該作者
回復 3# GBKEE


    謝版大，可是我權限不夠，不能下載呢
另外，可以介紹Stream的用法嗎？好像不易理解.....

TOP

GBKEE

帖子
5923 
主題
13 
精華
1 
積分
5986 
點名
0  
作業系統
win10 
軟體版本
Office 2010 
閱讀權限
150 
性別
男 
來自
台灣基隆 
註冊時間
2010-5-1 
最後登錄
2022-1-23 

2017 雞年全勤獎章2016猴年全勤獎章2015羊年全勤獎章2014馬年全勤獎章2013蛇年全勤獎章2012龍年全勤獎章論壇貢獻勳章熱烈參與勳章2011兔年全勤獎章
6#
發表於 2012-8-31 10:24 | 只看該作者
回復 5# 小俠客
你已有積分44分  版規: 檔案附件限等級中學生（積分50）以上才能下載
加油 加油!

TOP

HSIEN6001

帖子
219 
主題
24 
精華
0 
積分
243 
點名
0  
作業系統
Windows10 
軟體版本
Office2016 
閱讀權限
20 
性別
女 
註冊時間
2012-4-18 
最後登錄
2022-2-7 

論壇榮譽勳章論壇贊助勳章
7#
發表於 2012-8-31 10:56 | 只看該作者
回復 4# 小俠客

[參考]
順序可改成：
下載CSV → 經過VBA整理加入代號 → 匯入Access。

至於下載後，依含有【代號】之檔案名稱給該檔新增一個【代號欄位】，這方面你可另外詢問先進，尋求解決方案；這部份我還沒學到。
∼ ^___^ ∼吐舌傻笑!

TOP

小俠客

帖子
83 
主題
17 
精華
0 
積分
105 
點名
0  
作業系統
XP 
軟體版本
XP 
閱讀權限
20 
註冊時間
2010-12-28 
最後登錄
2018-8-8 
8#
發表於 2012-8-31 11:00 | 只看該作者
回復 6# GBKEE


    好的，待我先回應大家的POST再作研究，先謝了

TOP

小俠客

帖子
83 
主題
17 
精華
0 
積分
105 
點名
0  
作業系統
XP 
軟體版本
XP 
閱讀權限
20 
註冊時間
2010-12-28 
最後登錄
2018-8-8 
9#
發表於 2012-8-31 11:07 | 只看該作者
本帖最後由 小俠客 於 2012-8-31 11:09 編輯
回復  小俠客

[參考]
順序可改成：
下載CSV → 經過VBA整理加入代號 → 匯入Access。

至於下載後， ...
HSIEN6001 發表於 2012-8-31 10:56



    我也不懂如何在CSV加入新欄位，所以我用了一個替代方法：
  1. mySQL = "insert into Price select * from (select " & shareNo & " as Share, Date, Open, High, Low, Close, Volume as Vol, [Adj Close] as AClose from [Excel 8.0;hdr=yes;imex=1;Database=" & T1 & "].[" & shareNo & "$A1:G" & r & "])"
複製代碼
先把CSV當成一個DATABASE的TABLE，再用SQL INSERT到我的ACCESS中，SQL COMMAND當中加上我需要的SHARE NO，這樣便不用加欄位到CSV了。
但我還是覺得我現在的做法有點蠢，花一輪功夫將資料寫成一個CSV，再用SQL把沒有的資料(SHARE NO)加上，IMPORT到ACCESS，再刪掉那個CSV，但我總覺得建立CSV的工序有點重複......

TOP

diabo

帖子
79 
主題
2 
精華
0 
積分
193 
點名
0  
作業系統
Winwos 7 64 bits 
軟體版本
Excel 2003/2007 
閱讀權限
20 
性別
男 
來自
TAIPEI 
註冊時間
2010-8-25 
最後登錄
2019-9-20 
10#
發表於 2012-9-2 11:27 | 只看該作者
回復 9# 小俠客

直接將CSV檔，當作TABLE的另一種寫法...
  1. theCsvFolder = ThisWorkbook.Path & "\temp\"   '下載的CSV暫存資料夾，檔名則為【股票代號.csv】
  2. tbl = "PRICE_Daily"   ' ACCESS 裡存日成交資料的TABLE

  4.         strFields = "[Date],[Open],[High],[Low],[Close],[Volume],[Adj Close] as [AdjClose]"
  5.         strSQL = "INSERT INTO [" & tbl & "] SELECT " & 股票代號 & " as [StockID]," & strFields & " FROM [Text;Database=" & theCsvFolder & "].[" & 股票代號 & "#CSV]"

  7. cn.Execute strSQL
複製代碼
diabo

TOP

        靜思自在 : 【行善要及時】行善要及時，功德要持續。如燒開水一般，未燒開之前千萬不要停熄火候，否則重來就太費事了。
12
返回列表 上一主題