Hi guys

I'm really struggling here, hope you can point me in the right direction. I am OK with Excel VBA code but am now looking at Access for the first time.

I pasted my code below and need to combine all sheets in all excel files into one table. Sheets all have different structures i.e. used range is different for each sheet by rows and by columns.

_________________________
CONTEXT
I tried using PowerQuery but it is taking waaaaaayyyyy too long.

Here is the scenario - I have about 150 excel workbooks.
Each workbook has between 40 and 90 sheets.

I want to
1. import all data from each sheet in each workbook into one single database
2. include a column with the filename
3. include a column with the sheetname

Once imported into access, I can then filter the data as needed. I have a separate Excel file listing every single file name, sheet name and a description of that sheet. This will help me to group the sheets together (into 150 group types).
_________________________________

My CODE to get files from one folder. I can't get the loop to work correctly. It is repeatedly bringing back data from the first file

Please Login or Register  to view this content.

NOTE: I created a dummy excel file with 1 line and 80 columns which gets imported first. All subsequent workbooks will have <80 columns.
I did this so that the Table is created with enough columns to accommodate all subsequent files that are imported.

Any help appreciated.