Importing Variable column Spreadsheet to Access

[Guest]

I have a spreadsheet that has the first 4 colums the same every month but the
next 20 to 30 columns very in number. How do I upload this into access and
attach the relevant code to each column then load to a table with 6 fields?
I have a table listing all the column headings with their relevant code. It
is the VB required to automate this upload process

 

[John Nurick]

You say "VB". Do you mean you want to create a VB application that can
upload the data from Excel into an Access database, or do you want to create
a VBA procedure in your database that can import the data?

If the latter, it would probably mean writing VBA code to do the following:

1) Use DoCmd.TransferSpreadsheet acLink to create a linked table connected
to the worksheet.

2) Find out what columns are in the worksheet by examining the Fields
collection of the TableDef objct corresponding to the linked table.

3) Construct and execute one or more SQL INSERT INTO statements (append
queries) to pick the data you need from the 24-34 column worksheet and
append it to the 6-field table.

 

[Guest]

John

It was VBA and your suggestions crystalised my own thinking.

Thank you