ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

½Ð±Ð¦³ÃöADODB.STREAM©MMicrosoft.XMLHTTPªº¥Îªk

½Ð±Ð¦³ÃöADODB.STREAM©MMicrosoft.XMLHTTPªº¥Îªk

§Ú¥´ºâ±NYAHOO FINANCE´£¨ÑªºCSVÀɮפU¸ü§@¤ÀªR¡A©ó¬O¦bºô¤W§ä¨ì¤F²V¦X¥ÎADODB.STREAM©MMicrosoft.XMLHTTP¨Ó¤U¸üªº¤èªk¡A¦ý§Úµo²{¸ê®Æ¹ê¦b¤Ó¦h¡A®Ú¥»¤£¯à¤è«K¨Ï¥Î¡C
©Ò¥H¥´ºâ§â¸ê®Æ¶×¤JACCESS§@¾ã²z¡A¨Ã®Ú¾Úºô¤Wªº±Ð¾Ç¼g¤F¤U­±ªºCODE¡G
  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. Set wks = DBEngine.Workspaces(0)
  8. Set dbs = wks.OpenDatabase("D:\Shares\temp.mdb")

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

  10. 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"

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

  12. Set WinHttpReq = CreateObject("Microsoft.XMLHTTP")
  13. WinHttpReq.Open "GET", myUrl, False
  14. WinHttpReq.Send

  15. If WinHttpReq.Status = 200 Then
  16.     Set oStream = CreateObject("ADODB.Stream")
  17.     oStream.Open
  18.     oStream.Type = 1
  19.     oStream.Write WinHttpReq.ResponseBody
  20.     oStream.SaveToFile T1
  21.     oStream.Close
  22. End If


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

  25. If Not r2 Is Nothing Then
  26.     r = r2.Row
  27.    
  28.     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 & "])"

  29.     dbs.Execute (mySQL)

  30.     r1.Close
  31.     Kill T1
  32. End If


  33. End Sub
½Æ»s¥N½X
¦ý³o¬qCODE¦³¨Ç°ÝÃD¡A§Æ±æ¦U¦ì¥i¥H¦h¥[«ü±Ð¡G
1) §Ú¤£¤Ó©ú¥ÕADODB.STREAM©MMicrosoft.XMLHTTP¨º³¡¥÷ªº·N«ä¡Aºô¤W©M§Ú¤â¤Wªº°Ñ¦Ò®Ñ³£¨S¦³´£¤Î¡A¹ê¦b¤£¤F¸Ñ·í¤¤ªº¹B§@
2) §Ú³o¬qCODEªº¬yµ{À³¸Ó¬O¤U¸ü¨º­ÓCSV¡A¸g¹LMicrosoft.XMLHTTPŪ¨ú¸ê®Æ¡A¦A¥ÎADODB.STREAM¼g¦¨¤@­ÓCSV¡AµM«á§ÚŪ¨úCSVªº¸ê®Æ¡A¥[¤WSHARENO¡AIMPORT¨ìACCESS¡A³Ì«á§âCSV§R±¼¡C
½Ð°Ý·í¤¤
Ū¨ú¸ê®Æ>Àx¦s¦¨CSV>Ū¨úCSV>¼g¤JACCESS>§R°£CSVªº¹Lµ{
¯à§_§ï¦¨¡A
Ū¨ú¸ê®Æ>>¼g¤JACCESS?
ÁÂÁ¦U¦ì

¦^´_ 1# ¤p«L«È

¸Õ¸Õ!
Excel VBA¡G
ThisWorkbook¶K¤W
  1. Private Sub Workbook_Open()
  2.     SaveCSV
  3.     AccessModule
  4.     Kill "D:\Sharestemp01\shareNo.csv"
  5. End Sub
½Æ»s¥N½X
Excel Module¡G¶K¤W
  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. Set xml = CreateObject("Microsoft.XMLHTTP")
  8. Set stream = CreateObject("ADODB.stream")
  9.     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"
  10.         xml.Open "GET", URL, 0
  11.         xml.send
  12.     With stream
  13.         .Type = 1
  14.         .Open
  15.         .Write xml.responseBody
  16.         If Dir(path & "shareNo" & ".csv") <> "" Then Kill (path & "shareNo" & ".csv")
  17.         .SaveToFile (path & "shareNo" & ".csv")
  18.         .Close
  19.     End With
  20. Set stream = Nothing
  21. Set xml = Nothing
  22. End Sub

  23. Sub AccessModule()
  24. Dim A As Object
  25. Application.DisplayAlerts = False
  26. Set A = CreateObject("Access.Application")
  27.         A.Visible = False
  28.         A.OpenCurrentDatabase filepath:="D:\Shares\temp.mdb", Exclusive:=False, bstrPassword:=""
  29.         A.Run "InPutData"
  30.     Application.DisplayAlerts = True
  31.     A.CloseCurrentDatabase
  32.     A.Quit
  33. Set A = Nothing
  34. End Sub
½Æ»s¥N½X
Temp.mdb ·s¼WModule¡G¶K¤W
  1. Option Compare Database

  2. Function InPutData()
  3.     DoCmd.SetWarnings False
  4.     DoCmd.TransferText acImportDelim, "", "Table", "D:\Sharestemp01\shareNo.csv", True, ""
  5. End Function
½Æ»s¥N½X

TOP

¦^´_ 1# ¤p«L«È
¸Õ¸Õ¬Ý

test.rar (224.78 KB)

TOP

¦^´_ 2# HSIEN6001


ÁÂÁ§Aªº«ü±Ð¡A¬Ý°_¨Ó§A¼gªºCODE¤ñ§Ú¤u¾ã¦h¤F¡C
¦ý§Ú¬Ý¨ì§A¦n¹³§â§Úªºcode¤À¦¨¤T³¡¥÷¡A²Ä¤@­Ó©R¥O¬O·í¶}³o­ÓEXCEL FILE®É±Ò°Ê
¤§«á¦b"Microsoft.XMLHTTP"³¡¥÷Ū¨úCSV¡A¥ýÀˬdÀɮ׬O§_¦s¦b¡A¦p¦s¦b«K¥ý§R±¼¦A«Ø¥ß¤@­Ó·sªº(³o­Ó¤Ó¦n¤F¡A²{®É§Ú¬O¤â°Ê§R°£ªº¡AÁÂÁÂ)
µM«á¥ÎEXCEL VBA CALL ACCESS¦æMODULE¡A¹ï¶Ü¡H

¦ý·í¤¤¦³¨â­Ó°ÝÃD¡A¥Ñ©óYAHOO´£¨ÑªºCSV¨S¦³ªÑ²¼½s¸¹¡A¦pªG¤£¦b¼g¤JACCESS®É¥[¤W«KµLªk½T»{³o­ÓRECORD¬O¨º¥uªÑ²¼
¥t¥~¡A³o­Ó¼gªkÁÙ¬O»Ý­n¡AŪ¨úYAHOO¸ê®Æ>Àx¦s¦¨CSV>Ū¨úCSV>¼g¤JACCESS>§R°£CSV
§Ú¤W­±ªºCODE¡A¨ä¹ê¬O¥i¥H°µ¨ìªº¡A¦ý§Ú§Æ±æ·í¥Î"Microsoft.XMLHTTP"Ū¨úYAHOO¸ê®Æ®É¡A¯à°÷¤£¥ÎÀx¦s¦¨CSV¡Aª½±µ§â¸ê®Æ¼g¨ìACCESS¤¤¡A¤£¥Î¤£Â_­«½Æ«Ø¥ß©M§R°£CSV(¦]¬°§Ú·|§ì¼Æ¦Ê¶¡¤½¥q¸ê®Æ)¡C

TOP

¦^´_ 3# GBKEE


    Áª©¤j¡A¥i¬O§ÚÅv­­¤£°÷¡A¤£¯à¤U¸ü©O
¥t¥~¡A¥i¥H¤¶²ÐStreamªº¥Îªk¶Ü¡H¦n¹³¤£©ö²z¸Ñ.....

TOP

¦^´_ 5# ¤p«L«È
§A¤w¦³¿n¤À44¤À  ª©³W: Àɮתþ¥ó­­µ¥¯Å¤¤¾Ç¥Í¡]¿n¤À50¡^¥H¤W¤~¯à¤U¸ü
¥[ªo ¥[ªo!

TOP

¦^´_ 4# ¤p«L«È

[°Ñ¦Ò]
¶¶§Ç¥i§ï¦¨¡G
¤U¸üCSV ¡÷ ¸g¹LVBA¾ã²z¥[¤J¥N¸¹ ¡÷ ¶×¤JAccess¡C

¦Ü©ó¤U¸ü«á¡A¨Ì§t¦³¡i¥N¸¹¡j¤§ÀɮצWºÙµ¹¸ÓÀÉ·s¼W¤@­Ó¡i¥N¸¹Äæ¦ì¡j¡A³o¤è­±§A¥i¥t¥~¸ß°Ý¥ý¶i¡A´M¨D¸Ñ¨M¤è®×¡F³o³¡¥÷§ÚÁÙ¨S¾Ç¨ì¡C
¡ã ^___^ ¡ã¦R¦Þ¶Ì¯º!

TOP

¦^´_ 6# GBKEE


    ¦nªº¡A«Ý§Ú¥ý¦^À³¤j®aªºPOST¦A§@¬ã¨s¡A¥ýÁ¤F

TOP

¥»©«³Ì«á¥Ñ ¤p«L«È ©ó 2012-8-31 11:09 ½s¿è
¦^´_  ¤p«L«È

[°Ñ¦Ò]
¶¶§Ç¥i§ï¦¨¡G
¤U¸üCSV ¡÷ ¸g¹LVBA¾ã²z¥[¤J¥N¸¹ ¡÷ ¶×¤JAccess¡C

¦Ü©ó¤U¸ü«á¡A ...
HSIEN6001 µoªí©ó 2012-8-31 10:56



    §Ú¤]¤£À´¦p¦ó¦bCSV¥[¤J·sÄæ¦ì¡A©Ò¥H§Ú¥Î¤F¤@­Ó´À¥N¤èªk¡G
  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 & "])"
½Æ»s¥N½X
¥ý§âCSV·í¦¨¤@­ÓDATABASEªºTABLE¡A¦A¥ÎSQL INSERT¨ì§ÚªºACCESS¤¤¡ASQL COMMAND·í¤¤¥[¤W§Ú»Ý­nªºSHARE NO¡A³o¼Ë«K¤£¥Î¥[Äæ¦ì¨ìCSV¤F¡C
¦ý§ÚÁÙ¬Oı±o§Ú²{¦bªº°µªk¦³ÂIÄø¡Aªá¤@½ü¥\¤Ò±N¸ê®Æ¼g¦¨¤@­ÓCSV¡A¦A¥ÎSQL§â¨S¦³ªº¸ê®Æ(SHARE NO)¥[¤W¡AIMPORT¨ìACCESS¡A¦A§R±¼¨º­ÓCSV¡A¦ý§ÚÁ`ı±o«Ø¥ßCSVªº¤u§Ç¦³ÂI­«½Æ......

TOP

¦^´_ 9# ¤p«L«È

ª½±µ±NCSVÀÉ¡A·í§@TABLEªº¥t¤@ºØ¼gªk...
  1. theCsvFolder = ThisWorkbook.Path & "\temp\"   '¤U¸üªºCSV¼È¦s¸ê®Æ§¨¡AÀɦW«h¬°¡iªÑ²¼¥N¸¹.csv¡j
  2. tbl = "PRICE_Daily"   ' ACCESS ¸Ì¦s¤é¦¨¥æ¸ê®ÆªºTABLE

  3.         strFields = "[Date],[Open],[High],[Low],[Close],[Volume],[Adj Close] as [AdjClose]"
  4.         strSQL = "INSERT INTO [" & tbl & "] SELECT " & ªÑ²¼¥N¸¹ & " as [StockID]," & strFields & " FROM [Text;Database=" & theCsvFolder & "].[" & ªÑ²¼¥N¸¹ & "#CSV]"

  5. cn.Execute strSQL
½Æ»s¥N½X
diabo

TOP

        ÀR«ä¦Û¦b : µêªÅ¦³ºÉ¡D§ÚÄ@µL½a¡AµoÄ@®e©ö¦æÄ@Ãø¡C
ªð¦^¦Cªí ¤W¤@¥DÃD