如何把EXCEL資料輸入到Access

tsengs

各位大大我用EXCEL VB寫了程式後，不懂怎麼把Access當作EXCEL的DB，該如何將EXCEL資料自動拋到ACCESS
感恩

starry1314

回復 1# tsengs


    附上檔案

c_c_lai

回復 1# tsengs
提供你參考：

1. Option Explicit   
   
2. ' ACCDB 版
   
3. Option Base 1
   
4. 
   
5. '  Dim cnn As Object       '  New ADODB.Connection
   
6. '  Dim cmd As Object       '  New ADODB.Command
   
7. '  Dim rs As Object        '  New ADODB.Recordset
   
8. Dim cnn As ADODB.Connection, rs As ADODB.Recordset, cmd As ADODB.Command
   
9. Dim strSQL As String
   
10. Dim editMode As Boolean
    
11. Public dic3 As Object   '  在 ThisWorkbook 引用，此處要宣告 Public
    
12. 
    
13. '  設定引用項目 - VBAProject -> 可引用的項目 (A)：
    
14. '  Visual Basic For Applications
    
15. '  Microsoft Excel 14.0 Object Library
    
16. '  OLE Automation
    
17. '  Microsoft Office 14.0 Object Library
    
18. '  Microsoft Forms 2.0 Object Library
    
19. '  Microsoft DAO 3.6 Object Library
    
20. '  Microsoft ADO Ext 6.0 for DDL and Security
    
21. '  Microsoft ActiveX Data Object 6.1 Library
    
22. 
    
23. Sub OpenDB()
    
24.     Set cnn = New ADODB.Connection            
    
25.     Set rs = New ADODB.Recordset
    
26.     Set cmd = New ADODB.Command
    
27.     '  ObjectStateEnum Values
    
28.     '  Returns a value describing if the connection is open or closed
    
29.     '  Specifies whether an object is open or closed, connecting to a data source,
    
30.     '  executing a command, or retrieving data.
    
31.     '  --------------------------------------------------------------------------
    
32.     '  Constant             Value     Description
    
33.     '  --------------------------------------------------------------------------
    
34.     '  adStateClosed          0       The object is closed
    
35.     '  adStateOpen            1       The object is open
    
36.     '  adStateConnecting      2       The object is connecting
    
37.     '  adStateExecuting       4       The object is executing a command
    
38.     '  adStateFetching        8       The rows of the object are being retrieved
    
39.     With cnn
    
40.         If .State = 1 Then .Close    '  adStateOpen
    
41.         
    
42.         '  .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & _
    
43.         '                    ThisWorkbook.Path & Application.PathSeparator & "記錄明細表.mdb" & ";"
    
44.         '  For Microsoft.ACE.OLEDB.12.0,you need Microsoft Office 12.0 Access Database Engine to be installed.
    
45.         .ConnectionString = "Provider=Microsoft.Ace.OLEDB.12.0;" & "Data Source=" & _
    
46.                           ThisWorkbook.Path & Application.PathSeparator & "記錄明細表檔案.accdb" & ";"
    
47.         .Open
    
48.     End With
    
49. End Sub
    
50. 
    
51. Sub closeRS()
    
52.     With rs
    
53.         If .State = 1 Then .Close    '  adStateOpen
    
54.         '  The CursorLocation property is used to set or return the location of the cursor.
    
55.         '  This property can be set to one of the CursorLocationEnum constants listed in the following table.
    
56.         '  Enumeratio0n     Value   Description
    
57.         '  adUseNone          1     This value indicates no cursor location.
    
58.         '                           This value is not supported by the MicrosoftR OLE DB Provider for AS/400 and VSAM.
    
59.         '  adUseServer        2     This value indicates that the data provider or driver-supplied cursor is used.
    
60.         '  adUseClient        3     This value indicates that a client-side cursor supplied by a local cursor library
    
61.         '                           is to be used.
    
62.         '  adUseClientBatch   3     For backward compatibility, this value indicates that a client-side cursor supplied by
    
63.         '                           a local cursor library is to be used.
    
64.         .CursorLocation = 3          '  adUseClient
    
65.     End With
    
66. End Sub

複製代碼

提問問題時，請同時將你的附件一併帶上，否則你的提問會石沉大海的。

yautat123321

經ADODB.Connection 再直接用SQL 控制DB

man0821

回復 3# c_c_lai

請問大大是否有檔案可供下載觀摩，感謝先。