Transfer data from Excel file to Access File using VB6

Question

Dear All, I have an excel file (.xls) with data in the first Sheet that is named "Customers". I also have an Access.mdb database that contains a table called " CustomerDetails". I would like to transfer the data from the excel sheet into the Access database using VB6 code. How can I do that? Thanks in advance,

Answer 1

Can you use Access to import the worksheet from Excel?

If not, and you need to do it in VB, you'll need to either open the Excel file in VB or export from Excel to a csv format and open that, then write the data to an existing Access table.

Answer 2

Hello, this is quite simple to do, just place the below code in a module in Access and this will import it for you automatically. If you need the code to run you can place it in the form_open or something similar. You could even start it up with a batch file.

Function import()

basedir = "INSERT YOUR SPREADSHEET HERE"

DoCmd.TransferSpreadsheet _
            acImport, _
            acSpreadsheetTypeExcel9, _
            "TABLE NAME HERE", _
            basedir, _
            False
End Function

Answer 3

Go through this http://office.microsoft.com/en-ca/access-help/import-export-and-link-data-between-access-and-excel-HP001095095.aspx