Importing 4 excel worksheets into 4 tables using VBscript

[craig1988]

Hi

I am looking for some help in automating an import process from excel into acsess.

I currently have a database with 4 tables; DDin, DDOut, CTIn, CTOut. The users of this database receive 3 daily excel files with the corresponding worksheets; DDin, DDOut, CTIn, CTOut. The idea is that the users can click a command button and then be prompted to choose the file (The file location couldd change and the file name will be different each time). When the users choose the file, it will import the cells from each worksheet into the corresponding tables i.e. DDIn worksheet into the DDIn table etc...

Another issue is that we are not in control of the excel sheets they are created by a Third party that we have no control over. I have found that when I was importing them manually it was importing thousands of empty records. I researched this and found that an easy solution is below.

DELETE * FROM YourTable
WHERE YourField IS NULL;

Can this be added to the same piece of code so it was to run at the end of every import.

Regards


Craig

[ranman256]

Save 1 old excel file to a generic file like File2Import.xls
LINK this file as an attached table in access.
build your queries to import the data to the various tables (these queries should have WHERE [field] is not null, so you dont get the blanks)
put all this in a macro: mImportXLfiles
now the import process is just 2 steps:

1. Get a new excel file, save it to the same import name everytime, File2Import.xls
2. run the import macro.
done

All tables are filled.

[craig1988]

Thanks Ranman

That sounds like a simple process.

This might be a silly question but how do I "LINK this file as an attached table in access".

[craig1988]

Hi Ranman

I may have an issue with this method.

If I link my generic excel file to 4 tables it will always change the records in the tables of the database. Correct?

The idea of importing the data from excel to the database is so that we can build an archive of data. So as opposed to just mirroring what the excel file holds, I need to add the records from the excel files to already existing data in the tables of the database from previous excel files.

[ranman256]

When you overwrite the data , the link will remain, but the data is new and will import.
(link via external data, excel, link)

NOW, this assumes everytime you get a new excel file, the tabs have the exact same names. If so, all links remain.
So overwrite excel files
run import macro. (it will keep adding to the archive, not change)