How can I import an unstandard Excel worksheet into Access 2007?

[M Skabialka]

I would like to bring an Excel spreadsheet into Access where I will analyze
it line by line and create multiple tables from it. Worksheet rows have
many different kinds of information, which I can extract logically in
Access.

I can copy and paste from Excel to Access, but when I try to use code I get
errors

strTable = "tblStatus"
strPathFile = "C:\import.xls"
WorkSheetName = "Dallas"
blnHasFieldNames = False
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
strTable, strPathFile, blnHasFieldNames, Range:=WorkSheetName &
"!"

2391 Field 'F1' doesn't exist in destination table 'tblStatus.'

I just want to dump the whole worksheet into Access, matching columns and
rows without regard to original field names, then do my analysis.
In Excel I imported it into another worksheet to remove all formatting,
merged cells, etc, and that is what I want to import into Access.

How can I do that without getting errors?

 

[KARL DEWEY]

Why not just link it?

 

[M Skabialka]

I ended up writing an import specification to copy it form Excel into a new
Access table, then copied that line by line into my import table, then used
the code I had already written to break out the data.

The user clicks a button and browses to the Current file and selects it, and
then VBA code pulls it from that spreadsheet to another with a standard
sheet name, uses the import spec to pull that sheet into Access, and
completes the data transformation into tables without further user
intervention.

It would have been easier though to find some way to use TransferSpreadsheet
to ignore field names!

Mich

 

[KARL DEWEY]

If you link the Excel and then append you can align any field name to any so
long as datatype is a match.