GBKEE

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

¤p«L«È

¦^´_ 3# GBKEE


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

¤p«L«È

¦^´_ 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

GBKEE

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

test.rar (224.78 KB)

¤U¸ü¦¸¼Æ: 50

2012-8-30 16:21

HSIEN6001

¦^´_ 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. 
   
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

½Æ»s¥N½X

Temp.mdb ·s¼WModule¡G¶K¤W

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

½Æ»s¥N½X