Import data rows only

I have an Excel spreadsheet that I link to from Access.  The spreadsheet has 8 rows of header data and the 9th row is the actual field header.  Also the last row has a Total that I do now want to import either.  How do I make Access recognize the header row and then import the recordsets into a table with appropriately named columns?  The spreadsheet is attached.
12037-bom-assembly.zip
gibneytAsked:
Who is Participating?
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor Infotrakker SoftwareCommented:
Also note that in many cases it's simpler to import to a "staging" table in Access, and then use standard SQL and VBA to parse out the data you want.
0
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
you can use the TransferSpreadsheet command

docmd.transferspreadsheet acimport, 8, "tblBOM", "c:\FolderName\12037_bom_assembly.xls",True,"BOM!A9:P91"
0
 
gibneytAuthor Commented:
The staging process will work.  As it turns out the header data is useful so another query will parse the db specific records into another table and leave the raw table for cherry picking that information.  capricorn1's suggested transferspreadsheet would also work to bring in the whole sheeet so partial credit is due there too.  Thanks both for the quick turn around.

Tim
0
All Courses

From novice to tech pro — start learning today.