Append Excel data into existing Access Table

[Praloy Sangma]

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

 

[SydneyGeek]

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.

 

[Praloy Sangma]

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.

 

[SydneyGeek]

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

 

[Praloy Sangma]

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.

 

[SydneyGeek]

Thank you, you are welcome

Denis

 

[GeekGirl]

Hi There!

I'm using Access 2010 and I got run-time error 2455: You entered an expression that has an invalid reference to the property FileSearch. Any ideas what I'm doing wrong?

Thank you!

Thank you, you are welcome

Denis

 

[xenou]

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.