Auto-Import XLS or XLSB workbooks with multiple sheets and append data to tables. (Access Tables and Excel Sheets = same name)

Manerlao

Board Regular
Joined
Apr 14, 2020
Messages
55
Office Version
  1. 2019
Platform
  1. Windows
Dear MS Access Community!

I am actually quite new to this forum (a few months) and very new to MS Access (1 month or so).

Earlier this week, I was having trouble with Excel, and now I'm having trouble importing data into my MS Access Tables.
Please allow me to explain:

Structure and Background:

I have 30 MS Access Tables named (Tbl1, Tbl2, Tbl3, ... Tbl30) which correspond exactly to my sheet names within multiple XLS or XLSB workbooks (Tbl1, Tbl2, Tbl3, ... Tbl30).
Each workbook contains different datasets, but within corresponding headers.
I have around 190 workbooks with 30 sheets to import/append into MS Access Tables. Each sheet has the same name as my MS Acess tables as mentioned.
I have already ensured that the MS Access Table headers per table are corresponding to headers in my MS Excel Workbooks so the import runs smoothly.

My Progress:

I created one Macro to append for a single XLSB workbook located on my C:// drive and it works (Macro is shown in the image attached)
I created it using the "Design Tab" and it appends new data to each table from the corresponding worksheets in my XLS or XLSB workbooks.

HOWEVER, every time I run it I get an error message stating:

1592297890489.png


There is actually no error at all. It just doesn't want to import the data that is already in my table. Which is great!
This is what I already need, but I don't want this message to come up each time.

My Objective is 2-fold:


1. I would like to set a parameter as the filename extension so I can select different workbooks to import as and when needed to import.
2. I would like the import to only import the most recent data, not contained within the MS Access Tables
(This works for me quite good so far because I have already set my primary keys as 4 headers: for "Date, Time, Description1, Description2" since these will be unique each time as a combined group)
3. I don't want this message to come up each time.

Sorry, I'm quite new to Access, perhaps there is an easy fix?
If anyone can provide some guidance, this would be great! I spent my whole evening on youtube and forums trying to figure this out! I'll try get some sleep on the weekend.....(If I can!)

I look forward to hearing from the community! Thank you all and stay safe all.

Best regards,
Manerlao.
 

Attachments

  • MS_ACCESS_2020-06-16_165434.png
    MS_ACCESS_2020-06-16_165434.png
    16.1 KB · Views: 20

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Manerlao

Board Regular
Joined
Apr 14, 2020
Messages
55
Office Version
  1. 2019
Platform
  1. Windows
Edit: Written more clearly

Dear MS Access Community!

I am actually quite new to this forum (a few months) and very new to MS Access (1 month or so).

Earlier this week, I was having trouble with Excel, and now I'm having trouble importing data into my MS Access Tables.
Please allow me to explain:

Structure and Background:

I have 30 MS Access Tables named (Tbl1, Tbl2, Tbl3, ... Tbl30) which correspond exactly to my sheet names within multiple XLS or XLSB workbooks (Tbl1, Tbl2, Tbl3, ... Tbl30).
Each XLS or XLSB workbook contains different data, but within corresponding headers.
My headers in each sheet within the workbooks match the headers in each table in MS Access. I ensured this.

I have around 190 workbooks with 30 sheets to import/append into MS Access Tables. Each sheet has the same name as my MS Acess tables as mentioned.
I have already ensured that the MS Access Table headers per table are corresponding to headers in my MS Excel Workbooks so the import runs smoothly.

My Progress:

I created one Macro to append all my 30 MS Access tables but only for a single XLSB workbook located here: C:\Users\administrator\documents\DataBaseFilesAll\Workbook1.xlsb and it works (Macro is shown in the image attached)
I created it using the "Design Tab" and it appends new data to each table from the corresponding worksheets in my XLS or XLSB workbooks.
View attachment 16300

HOWEVER, every time I run it I get an error message stating:
View attachment 16299

There is actually no error at all. It just doesn't want to import the data that is already in my table. Which is great!
This is what I already need, but I don't want this message to come up each time.

My Primary Keys:
I have already set my primary keys as 4 headers: for "Date, Time, Description1, Description2" since these will be unique each time as a combined group
Sometimes the date and time can be the same but Description1 can be different, this is why I have set all 4 headings as my primary key. Otherwise, I get duplicated data. Is this ok? Is it correct?

My Objectives:

1. I would like to set a parameter as the filename extension so I can select different workbooks to import when I have to import the data.
2. I would like to import only the most recent data which is within the XLS or XLSB workbooks. Subsequently, I don't want to import the same data which is already contained within the MS Access Tables
3. I don't want this error message to come up each time.

Sorry, I'm quite new to Access, perhaps there is an easy fix? Perhaps you can provide additional guidance as to whether this logic/plan is correct?

If anyone can provide some guidance, this would be great! I spent my whole evening on YouTube and forums trying to figure this out! I'll try get some sleep on the weekend.....(If I can!)
For now, this is my mission.

I look forward to hearing from the community! Thank you all and stay safe all.

Best regards,
Manerlao.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,593
Messages
5,838,257
Members
430,536
Latest member
Manoj Gaidhankar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top