access vba error 2391 f1 field not found (1 Viewer)

[Trevor G]

I have created the following code to loop through all excel workbooks in a folder but it is failing on the transferspreadsheet as it states the F1 field not found, having checked several times the workbooks are all structured correctly and the access table has the same field names as I created a temp table and copied the field names from the workbook.

Am I missing something simple or is this an issue with Access 2007 ?

Sub AddFileNew()
Dim strPath As String
Dim strFile As String
Dim i As Integer
strPath = CurrentProject.Path & "\"
strFile = Dir(strPath & "*.xls")
Do While strFile <> ""
i = i + 1
Debug.Print strFile
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "tblTempImport", strPath & strFile, True
strFile = Dir
Loop
MsgBox "How many files found " & i
End Sub

 

[Trevor G]

I have created an ADO work around. It would be nice to know about the TransferSpreadsheet issue though if anyone knows.

 

[gemma-the-husky]

Excel workbooks seem to carry a vestige of cells that were once used

so the imported columns and rows are likely to extend beyond the populated area.

I always try and avoid importing excel directly, and use csv's if at all possible. However, if you import a xls to a new table WITH column headers - you can then test the columns that you did get, by iterating the new tables fields collection - and make sure you got the right table!

 

[Trevor G]

Thanks for your comments Dave,

I found no issue in the spreadsheet even when I have copied the data to another sheet but I received the spreadsheet to work on from another person so who knows what they might have done.

My work around is fine and they are happy with what it does for them.

Perhaps I will stay away from the TransferSpreadsheet option.

Regards