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

複製代碼

diabo

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

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

diabo

完整程式碼....

1. Sub 將CSV檔資料寫入ACCESS()
   
2. 
   
3.      Dim cn As ADODB.Connection
   
4.    
   
5.     '建立ADODB Connection物件變數
   
6.      Set cn = New ADODB.Connection
   
7.    
   
8.      TARGET_DB = "TaiwanDB.mdb"
   
9.      tbl = "PRICE_Daily"
   
10.      股票代號 = "1101"
    
11.      theCsvFolder = ThisWorkbook.Path
    
12.    
    
13.     '建立ACCESS(*.mdb)連結
    
14.      cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    
15.              "Data Source=" & ThisWorkbook.Path & "\" & TARGET_DB & ";" & _
    
16.              "Persist Security Info=False"
    
17.          
    
18.      strFields = "[Date],[Open],[High],[Low],[Close],[Volume],[Adj Close] as [AdjClose]"
    
19.      strSQL = "INSERT INTO [" & tbl & "] SELECT " & 股票代號 & " as [StockID]," & strFields & " FROM [Text;Database=" & theCsvFolder & "].[" & 股票代號 & "#CSV]"
    
20. 
    
21.     '執行SQL command
    
22.      cn.Execute strSQL
    
23.      
    
24.     '關閉ADODB connection
    
25.      cn.Close
    
26.      
    
27.     '清除佔用記憶體
    
28.      Set cn = Nothing
    
29. 
    
30. End Sub

複製代碼