Append Excel data into existing Access Table

Praloy Sangma

New Member
Joined
Aug 4, 2009
Messages
9
Hi Team,

I am an intermediate VBA programmer in Excel. I do have knowledge about access tables and queries and I do know that it is easier to import excel tables from Access using import function.
Is there any efficient way to append excel tables into existing access table with the same table format (considering that access and excel table is a mirror to each other) via VBA in excel on monthly or weekly basis?

Kindly mention the VBA Objects to be used(Tools/VBA Reference) in vb editor if required.

Regards and thanks in advance.

Praloy
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
The way that I prefer to do it is to import the Excel data into a specific import table, and then append and / or update from there to the main table. That lets you do any required data checks / validation / cleanup before putting the data into the 'real' table.

First, create an import specification by manually importing the data using the External Data features.
To import Excel data into Access use Docmd.TransferSpreadsheet. You can reference the Import Spec so that the data types are defined correctly, and ignore any columns that you don't want to import.
To select the Excel file to import you can use Application.FileDialog

You may find this tutorial helpful.

Hope that gets you started.
 
Upvote 0
Hi There,

Thank you for responding this question. I do know how to import the data from Access manually.
But My question is how do i use macro in excel to append/export to Access table.
This requirement is for automation, which i just have to run the macro in excel to append on a monthly or weekly basis.

Regards,
Praloy M Sangma.
 
Upvote 0
Did you look at the tutorial? It covers creating the Import Spec, and writing / running code to import the files, using the specification you created.

Denis
 
Upvote 0
Hi Denis,

Yes the link you have shared did have both creating and appending tables into access by using excel vba. I really liked the sample file which you have shared to download, it made easier for me to understand the code better.

Thank you for sharing the link and I appreciate your help on this. May God bless you.

Regards,
Praloy M Sangma.
 
Last edited:
Upvote 0
The FileSearch object seems to come and go in different releases of Access (as I recall). In any case, the thing to do is to replace that part of the code with something else that allows you to iterate through the files you want to import.
 
Upvote 0

Forum statistics

Threads
1,213,486
Messages
6,113,932
Members
448,533
Latest member
thietbibeboiwasaco

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
Back
Top