How to copy multiple excel files into an access 2010 database using vba [closed]

Question

How to copy multiple excel files into an access 2010 database using vba [closed]

Navigation

#1 by (1 votes)

1

I'm new to VBA programming. I need a vba code in excel that copies several excel files to an access table.

excel office-2013 vba ms-access

asked by anonymous 18.05.2016 / 20:41

1 answer

Check if an ID exists on a data-list Select from all companies that have catalogs

score 1 · Answer 1

This maybe is a possible solution, using ACE.OLEDB:

Sub Test()
    accessFilePath = "C:\someDB.accdb"
    Call ExecuteSQLCmd("INSERT INTO '" & accessFilePath & "'.'Table' (col1,col2,col3) SELECT col1,col2,col3 FROM [Sheet1$]", accessFilePath)
 End Sub

Sub ExecuteSQLCmd(cmd As String, accessFilePath as String )

    Dim cnn As ADODB.Connection
    Dim sql As String

    Set cnn = New ADODB.Connection
    cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & accessFilePath & ";Persist Security Info=False;"

    If Not (cnn Is Nothing) Then
        'Execute Sql
        cnn.Execute (cmd)
        'Close
        cnn.Close
    End If
    Set cnn = Nothing
End Sub