Import Excel Columns Into Access Tables

[dar149]

Hi,
I know how to specify what fields in a Word form go into various tables in Acccess. Can the same thing be done in Excel?

I have an Excel Spreadsheet which I want specific columns to be imported into various tables. For Example, I want columns A and B to go into one table and columns C and D to go into another table, etc. I receive text files and bring them into Excel on a monthly basis, so I would like to automate the process of importing into Access. The new data would be appended into existing tables.

Thanks...

 

[lewds]

This response assumes that your Excel sheet is always the same name and in the same location with the same column names:

step1 create a link table to the excel sheet from access

step2 create an append query for each table that you want your new information to go to.

step3 run all append queries from one command button click event

such as:

private sub command1_click()

docmd.openquery "appendTable1" ,acNormal, acedit
docmd.openquery "appendTable2" ,acNormal, acedit
ECT....

end sub

 

[lewds]

Just some additional:

TonyJollons Proposed a method for importing delimited text files directly into Access using:

DoCmd.TransferText_acImportDelim, "Your Spec File", "TableName", "Path\yourfile.txt

 

[dar149]

Thanks for your help on this issue...