麻辣家族討論版版 - Powered by Discuz! Board

標題: 請教有關ADODB.STREAM和Microsoft.XMLHTTP的用法 [打印本頁]

作者: 小俠客 時間: 2012-8-30 00:14 標題: 請教有關ADODB.STREAM和Microsoft.XMLHTTP的用法

我打算將YAHOO FINANCE提供的CSV檔案下載作分析，於是在網上找到了混合用ADODB.STREAM和Microsoft.XMLHTTP來下載的方法，但我發現資料實在太多，根本不能方便使用。
所以打算把資料匯入ACCESS作整理，並根據網上的教學寫了下面的CODE：

Sub GetPrice()
Dim wks As Workspace
Dim dbs As Database
Dim rs As DAO.Recordset
Dim mySQL As String, myUrl As String
Dim WinHttpReq As Object
Set wks = DBEngine.Workspaces(0)
Set dbs = wks.OpenDatabase("D:\Shares\temp.mdb")
shareNo = Format(1, "0000")
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"
T1 = "D:\Shares\temp01\" & shareNo & ".csv"
Set WinHttpReq = CreateObject("Microsoft.XMLHTTP")
WinHttpReq.Open "GET", myUrl, False
WinHttpReq.Send
If WinHttpReq.Status = 200 Then
Set oStream = CreateObject("ADODB.Stream")
oStream.Open
oStream.Type = 1
oStream.Write WinHttpReq.ResponseBody
oStream.SaveToFile T1
oStream.Close
End If
Set r1 = Workbooks.Open(T1)
Set r2 = r1.Sheets(1).Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious)
If Not r2 Is Nothing Then
r = r2.Row
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 & "])"
dbs.Execute (mySQL)
r1.Close
Kill T1
End If
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 時間: 2012-8-30 15:09

回復 1# 小俠客

試試!
Excel VBA：
ThisWorkbook貼上

Private Sub Workbook_Open()
SaveCSV
AccessModule
Kill "D:\Sharestemp01\shareNo.csv"
End Sub

複製代碼

Excel Module：貼上

Sub SaveCSV()
Dim xml As Object
Dim stream
Dim URL As String
Dim path As String
path = "D:\Sharestemp01\"
Set xml = CreateObject("Microsoft.XMLHTTP")
Set stream = CreateObject("ADODB.stream")
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"
xml.Open "GET", URL, 0
xml.send
With stream
.Type = 1
.Open
.Write xml.responseBody
If Dir(path & "shareNo" & ".csv") <> "" Then Kill (path & "shareNo" & ".csv")
.SaveToFile (path & "shareNo" & ".csv")
.Close
End With
Set stream = Nothing
Set xml = Nothing
End Sub
Sub AccessModule()
Dim A As Object
Application.DisplayAlerts = False
Set A = CreateObject("Access.Application")
A.Visible = False
A.OpenCurrentDatabase filepath:="D:\Shares\temp.mdb", Exclusive:=False, bstrPassword:=""
A.Run "InPutData"
Application.DisplayAlerts = True
A.CloseCurrentDatabase
A.Quit
Set A = Nothing
End Sub

複製代碼

Temp.mdb 新增Module：貼上

Option Compare Database
Function InPutData()
DoCmd.SetWarnings False
DoCmd.TransferText acImportDelim, "", "Table", "D:\Sharestemp01\shareNo.csv", True, ""
End Function

複製代碼

作者: GBKEE 時間: 2012-8-30 16:21

回復 1# 小俠客
試試看

[attach]12325[/attach]

作者: 小俠客 時間: 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(因為我會抓數百間公司資料)。

作者: 小俠客 時間: 2012-8-31 02:25

回復 3# GBKEE

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

作者: GBKEE 時間: 2012-8-31 10:24

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

作者: HSIEN6001 時間: 2012-8-31 10:56

回復 4# 小俠客

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

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

作者: 小俠客 時間: 2012-8-31 11:00

回復 6# GBKEE

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

作者: 小俠客 時間: 2012-8-31 11:07

本帖最後由小俠客於 2012-8-31 11:09 編輯

回復小俠客

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

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

我也不懂如何在CSV加入新欄位，所以我用了一個替代方法：

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 時間: 2012-9-2 11:27

回復 9# 小俠客

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

theCsvFolder = ThisWorkbook.Path & "\temp\" '下載的CSV暫存資料夾，檔名則為【股票代號.csv】
tbl = "PRICE_Daily" ' ACCESS 裡存日成交資料的TABLE
strFields = "[Date],[Open],[High],[Low],[Close],[Volume],[Adj Close] as [AdjClose]"
strSQL = "INSERT INTO [" & tbl & "] SELECT " & 股票代號 & " as [StockID]," & strFields & " FROM [Text;Database=" & theCsvFolder & "].[" & 股票代號 & "#CSV]"
cn.Execute strSQL

複製代碼

作者: diabo 時間: 2012-9-2 11:31

#10 的寫法，只能省一個步驟...

讀取資料 > 儲存成CSV > (可省略)讀取CSV(可省略) > CSV直接寫入ACCESS > 刪除CSV的過程[code]

作者: diabo 時間: 2012-9-2 11:35

完整程式碼....

Sub 將CSV檔資料寫入ACCESS()
Dim cn As ADODB.Connection
'建立ADODB Connection物件變數
Set cn = New ADODB.Connection
TARGET_DB = "TaiwanDB.mdb"
tbl = "PRICE_Daily"
股票代號 = "1101"
theCsvFolder = ThisWorkbook.Path
'建立ACCESS(*.mdb)連結
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & ThisWorkbook.Path & "\" & TARGET_DB & ";" & _
"Persist Security Info=False"
strFields = "[Date],[Open],[High],[Low],[Close],[Volume],[Adj Close] as [AdjClose]"
strSQL = "INSERT INTO [" & tbl & "] SELECT " & 股票代號 & " as [StockID]," & strFields & " FROM [Text;Database=" & theCsvFolder & "].[" & 股票代號 & "#CSV]"
'執行SQL command
cn.Execute strSQL
'關閉ADODB connection
cn.Close
'清除佔用記憶體
Set cn = Nothing
End Sub

複製代碼

作者: 小俠客 時間: 2012-9-4 13:29

這個方法好像不錯，待我回家改成這個試試，謝謝

作者: 小俠客 時間: 2012-9-7 14:57

回復小俠客
你已有積分44分版規: 檔案附件限等級中學生（積分50）以上才能下載
加油加油!
GBKEE 發表於 2012-8-31 10:24

終於成為中學生了，可以載附件了
太利害了，原來可以這樣簡化的，謝謝！內容太多，要點時間消化

但可以多了解一下，用DAO 和ADODB在執行效率上有沒有分別？
因為DAO不用像ADODB要寫provider=?????，容易記住。

另外，我看到「資料導入資料庫()」中，你是用FOR LOOP+.AddNew +.UPDATE的方法寫入資料，這個和用SQL COMMAND的INSERT，在執行速度上有沒有分別？謝謝

作者: 小俠客 時間: 2012-9-12 11:38

回復小俠客
試試看
GBKEE 發表於 2012-8-30 16:21

我剛剛試比較用.ADDNEW和.UPDATE和SQL INSERT 速度比較，我發現用SQL INSERT比前者快很多

Set wb = Workbooks.Open(myUrl)
With myRS
For Each a In ActiveSheet.Range([A2], [A2].End(xlDown))
.AddNew '添加各個欄位的資料
For i = 1 To .Fields.Count - 1
.Fields(0) = shareNo
.Fields(i) = a(1, i)
Next i
.Update '更新資料表
Next
End With

複製代碼

所以我希望用SQL作資料寫入，但請問這句如何改寫成用 wb 或activebook作DATABASE呢？

"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=" & wb & "].[table$A1:G" & r & "])"

複製代碼

謝謝指教

歡迎光臨麻辣家族討論版版 (http://forum.twbts.com/)