How to copy multiple excel files in an access 2010 database using vba

Asked

Viewed 182 times

1

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

excel vba ms-access office-2013

  • Hello, welcome to SOPT. This site is not a forum. Please do the [tour]. And edit your question to make it more specific (for example: what exactly is your difficulty in implementing such code?).

1 answer

1

That one 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

Browser other questions tagged excel vba ms-access office-2013

You are not signed in. Login or sign up in order to post.