請教有關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
   
7. 
   
8. Set wks = DBEngine.Workspaces(0)
   
9. Set dbs = wks.OpenDatabase("D:\Shares\temp.mdb")
   
10. 
    
11. shareNo = Format(1, "0000")
    
12. 
    
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"
    
14. 
    
15. T1 = "D:\Shares\temp01\" & shareNo & ".csv"
    
16. 
    
17. Set WinHttpReq = CreateObject("Microsoft.XMLHTTP")
    
18. WinHttpReq.Open "GET", myUrl, False
    
19. WinHttpReq.Send
    
20. 
    
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
    
29. 
    
30. 
    
31. Set r1 = Workbooks.Open(T1)
    
32. Set r2 = r1.Sheets(1).Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious)
    
33. 
    
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 & "])"
    
38. 
    
39.     dbs.Execute (mySQL)
    
40. 
    
41.     r1.Close
    
42.     Kill T1
    
43. End If
    
44. 
    
45. 
    
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

回復 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\"
   
7. 
   
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
    
24. 
    
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
   
2. 
   
3. Function InPutData()
   
4.     DoCmd.SetWarnings False
   
5.     DoCmd.TransferText acImportDelim, "", "Table", "D:\Sharestemp01\shareNo.csv", True, ""
   
6. End Function

複製代碼

GBKEE

回復 1# 小俠客
試試看

test.rar (224.78 KB)

下載次數: 50

2012-8-30 16:21

小俠客

回復 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(因為我會抓數百間公司資料)。

小俠客

回復 3# GBKEE


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

GBKEE

回復 5# 小俠客
你已有積分44分  版規: 檔案附件限等級中學生（積分50）以上才能下載
加油 加油!

HSIEN6001

回復 4# 小俠客

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

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

小俠客

回復 6# GBKEE


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

小俠客

回復  小俠客

[參考]
順序可改成：
下載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的工序有點重複......

diabo

回復 9# 小俠客

直接將CSV檔，當作TABLE的另一種寫法...

1. theCsvFolder = ThisWorkbook.Path & "\temp\"   '下載的CSV暫存資料夾，檔名則為【股票代號.csv】
   
2. tbl = "PRICE_Daily"   ' ACCESS 裡存日成交資料的TABLE
   
3. 
   
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]"
   
6. 
   
7. cn.Execute strSQL

複製代碼