CODEWITHSUNDEEP
vbams-access
user3538043
user3538043

Reputation: 51

How to import multiple Excel files into an Access table with an extra column indicating Excel file name using VBA

I want to import two excel files (Book1.xlsx and Book2.xlsx) into an access database table (Table1), with an extra column indicating the data source:

1.xlsx

Name    Age
Paul    30

2.xlsx

Name    Age
John    20

Table1

Name    Age Source
Paul    30  Book1
John    20  Book2

What would be the best way to automate this via either Access VBA or Excel VBA? I will have dozens of excel files with each file containing around 50K rows.

Do I have to open every excel file to run an Excel VBA macro to insert records line-by-line using ADODB? If so, how should I write the macro?

But ideally I want to do it in Access VBA macro. I can use "DoCmd.TransferSpreadsheet" to import two Excel files into the table, but I don't know how to add the additional "Source" column inside the VBA.

Thank you very much.

Upvotes: 2

Views: 1950

Answers (1)

Yawar
Yawar

Reputation: 11607

You can do an insert into ... select ... query against Excel spreadsheets as if they were Access database tables. See https://stackoverflow.com/a/11866465/20371 for details.

You can adapt the code shown in the above to work with a collection of files--say, all files in a folder. And since you're doing it as a query, you can easily add a Source column and set its value to the file name or whatever.

My only warning with this method would be to carefully convert data types when selecting and inserting them from a spreadsheet. By this I mean wrap decimal numbers with cdbl(num), integers with cint(num), strings with cstr(str), etc. And if you do any logical comparisons with the data in the query, then account for missing values. So instead of:

insert into ...
select ...
from ...
where x = 1

do:

insert into ...
select ...
from ...
where iif(x is null, 0, x) = 1

Upvotes: 0

Related Questions