½Ð±Ð¦³ÃöADODB.STREAM©MMicrosoft.XMLHTTPªº¥Îªk
- ©«¤l
- 83
- ¥DÃD
- 17
- ºëµØ
- 0
- ¿n¤À
- 105
- ÂI¦W
- 0
- §@·~¨t²Î
- XP
- ³nÅ骩¥»
- XP
- ¾\ŪÅv
- 20
- µù¥U®É¶¡
- 2010-12-28
- ³Ì«áµn¿ý
- 2018-8-8
|
½Ð±Ð¦³Ãö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- 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
½Æ»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¦ì |
|
|
|
|
|
|
- ©«¤l
- 219
- ¥DÃD
- 24
- ºëµØ
- 0
- ¿n¤À
- 243
- ÂI¦W
- 0
- §@·~¨t²Î
- Windows10
- ³nÅ骩¥»
- Office2016
- ¾\ŪÅv
- 20
- ©Ê§O
- ¤k
- µù¥U®É¶¡
- 2012-4-18
- ³Ì«áµn¿ý
- 2022-2-7
|
¦^´_ 1# ¤p«L«È
¸Õ¸Õ!
Excel VBA¡G
ThisWorkbook¶K¤W- Private Sub Workbook_Open()
- SaveCSV
- AccessModule
- Kill "D:\Sharestemp01\shareNo.csv"
- End Sub
½Æ»s¥N½X Excel Module¡G¶K¤W- 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
½Æ»s¥N½X Temp.mdb ·s¼WModule¡G¶K¤W- Option Compare Database
- Function InPutData()
- DoCmd.SetWarnings False
- DoCmd.TransferText acImportDelim, "", "Table", "D:\Sharestemp01\shareNo.csv", True, ""
- End Function
½Æ»s¥N½X |
|
|
|
|
|
|
- ©«¤l
- 83
- ¥DÃD
- 17
- ºëµØ
- 0
- ¿n¤À
- 105
- ÂI¦W
- 0
- §@·~¨t²Î
- XP
- ³nÅ骩¥»
- XP
- ¾\ŪÅv
- 20
- µù¥U®É¶¡
- 2010-12-28
- ³Ì«áµn¿ý
- 2018-8-8
|
¦^´_ 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 |
|
|
|
|
|
|
- ©«¤l
- 83
- ¥DÃD
- 17
- ºëµØ
- 0
- ¿n¤À
- 105
- ÂI¦W
- 0
- §@·~¨t²Î
- XP
- ³nÅ骩¥»
- XP
- ¾\ŪÅv
- 20
- µù¥U®É¶¡
- 2010-12-28
- ³Ì«áµn¿ý
- 2018-8-8
|
¦^´_ 3# GBKEE
Áª©¤j¡A¥i¬O§ÚÅv¤£°÷¡A¤£¯à¤U¸ü©O
¥t¥~¡A¥i¥H¤¶²ÐStreamªº¥Îªk¶Ü¡H¦n¹³¤£©ö²z¸Ñ..... |
|
|
|
|
|
|
- ©«¤l
- 219
- ¥DÃD
- 24
- ºëµØ
- 0
- ¿n¤À
- 243
- ÂI¦W
- 0
- §@·~¨t²Î
- Windows10
- ³nÅ骩¥»
- Office2016
- ¾\ŪÅv
- 20
- ©Ê§O
- ¤k
- µù¥U®É¶¡
- 2012-4-18
- ³Ì«áµn¿ý
- 2022-2-7
|
¦^´_ 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¦Þ¶Ì¯º! |
|
|
|
|
|
|
- ©«¤l
- 83
- ¥DÃD
- 17
- ºëµØ
- 0
- ¿n¤À
- 105
- ÂI¦W
- 0
- §@·~¨t²Î
- XP
- ³nÅ骩¥»
- XP
- ¾\ŪÅv
- 20
- µù¥U®É¶¡
- 2010-12-28
- ³Ì«áµn¿ý
- 2018-8-8
|
¦^´_ 6# GBKEE
¦nªº¡A«Ý§Ú¥ý¦^À³¤j®aªºPOST¦A§@¬ã¨s¡A¥ýÁ¤F |
|
|
|
|
|
|
- ©«¤l
- 83
- ¥DÃD
- 17
- ºëµØ
- 0
- ¿n¤À
- 105
- ÂI¦W
- 0
- §@·~¨t²Î
- XP
- ³nÅ骩¥»
- XP
- ¾\ŪÅv
- 20
- µù¥U®É¶¡
- 2010-12-28
- ³Ì«áµn¿ý
- 2018-8-8
|
¥»©«³Ì«á¥Ñ ¤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- 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«½Æ...... |
|
|
|
|
|
|
- ©«¤l
- 79
- ¥DÃD
- 2
- ºëµØ
- 0
- ¿n¤À
- 193
- ÂI¦W
- 0
- §@·~¨t²Î
- Winwos 7 64 bits
- ³nÅ骩¥»
- Excel 2003/2007
- ¾\ŪÅv
- 20
- ©Ê§O
- ¨k
- ¨Ó¦Û
- TAIPEI
- µù¥U®É¶¡
- 2010-8-25
- ³Ì«áµn¿ý
- 2019-9-20
|
¦^´_ 9# ¤p«L«È
ª½±µ±NCSVÀÉ¡A·í§@TABLEªº¥t¤@ºØ¼gªk...- theCsvFolder = ThisWorkbook.Path & "\temp\" '¤U¸üªºCSV¼È¦s¸ê®Æ§¨¡AÀɦW«h¬°¡iªÑ²¼¥N¸¹.csv¡j
- tbl = "PRICE_Daily" ' ACCESS ¸Ì¦s¤é¦¨¥æ¸ê®ÆªºTABLE
- strFields = "[Date],[Open],[High],[Low],[Close],[Volume],[Adj Close] as [AdjClose]"
- strSQL = "INSERT INTO [" & tbl & "] SELECT " & ªÑ²¼¥N¸¹ & " as [StockID]," & strFields & " FROM [Text;Database=" & theCsvFolder & "].[" & ªÑ²¼¥N¸¹ & "#CSV]"
- cn.Execute strSQL
½Æ»s¥N½X |
|
diabo
|
|
|
|
|