Importing from Excel to Access via VBA

[utahjzz98]

I need to create a program that will allow a user to export information from an excel spreadsheet and load that info into an Access table. The program uses Access forms for the front-end and Access back-end. I am vaguely familiar with VBA, but I am not sure how to begin this project. Any feedback would be greatly appreciated.

Regards,
Corey

 

[ETID]

It is real easy to link to a spreadsheet from access so that it acts as though it's a table within your database.


In Access, File>Get external data>Link tables...

follow the wizard....you can make it a read only link or a read write.

 

[utahjzz98]

Unfortunately that wouldn't work for what I am trying to accomplish. The amount of information that would be kept in the Excel spreadsheet would be much to large and this information comes in daily in a "dump" type format. What I need, is to be able to take that data and append it to a table in Access via VBA.

 

[ETID]

What is the format of the dump...is it delimited by commas or tabs or spaces or other?

 

[utahjzz98]

The information that comes in is a compliation of information from other documents and it will not remain in the same Excel document and that's why I won't be able to link the file. I need to be able to import the data to Access and then not have to worry about what happens to it after that.

 

[ETID]

I'm not thinking about a link at this point...

But what I am thinking about is an append query from within access.

that looks to your "Drive:\file_dump.???" file and appends it to an existing table in access.

but I need to know how the dump file is layed out.

 

[utahjzz98]

That sounds like exactly what I am looking for. The data contains column headings and rows of info, similar to a table structure in Access.

 

[ETID]

Can you open it with notepad?

if so that will tell me a bit about how the data in the dump is layed out.