Problem with Excel Import Run-time error '2391'

[BrockWade]

I am having trouble getting the following code to load data from my Excel file "Book.xlsx" into my table called "ExcelDataBook":

Sub btnExcelImport_Click()
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "ExcelDataBook", _
"C:\Users\dacampb1\Desktop\Book.xlsx", , "Sheet1!A1:B12000"
End Sub



The Excel file only has one sheet "Sheet1' and has only 10,504 rows but this can vary so I set it in the code for 12,000 rows. The sheet has only two columns "A" and "B" with the first row of the two columns labeled "Store" and "Amount" and my tabel has only two fields "Store" and "Amount".

My error reads:

Run-time error '2391': Field 'F1' doesn't exist in the destination table 'ExcelDataBook.'

[lfpm062010]

I think you are missing the "Has Label" between the file name and range.

Sub btnExcelImport_Click()
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "ExcelDataBook", _
"C:\Users\dacampb1\Desktop\Book.xlsx", True , "Sheet1!A1:B12000"
End Sub

[BrockWade]

I decided to just link the Excel files and all is running fine except when I run a query on the file I get about 1,041 blank rows before my data is displayed in the query's datasheet view or any report based upon this query... the data seems good but why the leading blank rows? The data in the linked Excel sheet does not look like this and another Excel file's query looks fine and they seem set up indentically! Thoughts?

[lfpm062010]

It is hard to debug without your excel file.
The only thing I can suggest is to delete all the columns and rows that has no data (highlight them and delete). Sometimes if a cell is being formated or touched, it will consider that is a data cell by MS Access/MS Excel.