I use this method., first remove the unwanted rows until the field headers are in row 1.
save this workbook to a generic filename everytime, i.e: "c:\folder\file2import.xls"
link the workbook as an external table.
then everytime you get a new file, overwrite the old one, and run the code below,
it will open the excel file, remove the unwanted rows, and save, then close excel, and import the table.
steps:
1. overwrite old generic file,
2. click Import button
3. done.
'you MUST put Excel in the program REFERENCES, in VBE menu (Ctl-G): TOOLS , REFERENCES
'check the 'Microsoft Excel X.xx Object library'
Code:
sub btnImport_click()
vFile = "c:\folder\file2import.xls"
FixSheet vFile 'fix the data first
DoCmd.openquery "qaImportData" 'NOW import the data
end sub
'============
Sub CallXLsheet(pvFile)
'============
Dim xl As Excel.Application
Set xl = CreateObject("excel.application")
With xl
.Workbooks.Open pvFile
.Rows("1:3").delete
.Range("A1").Select
.ActiveWorkbook.Save
.Quit
End With
Set xl = Nothing
End Sub