jmac001
asked on
VBA File Import, Error 2391
Receiveing error 2391 - Field Store Name: does not exist in destination table tmpShipping. The field is not in the table or the spreadsheet not sure how to troubleshoot.
Private Sub Command3_Click()
Dim strFile As String 'Filename
Dim strFileList() As String 'File Array
Dim intFile As Integer 'File Number
Dim filename As String
Dim path As String
DoCmd.SetWarnings False
path = "G:\Store Planning\Projects\Parts Tracker\ToImport\"
'Loop through the folder & build file list
strFile = Dir(path & "*.xls")
While strFile <> ""
'add files to the list
intFile = intFile + 1
ReDim Preserve strFileList(1 To intFile)
strFileList(intFile) = strFile
strFile = Dir()
Wend
'see if any files were found
If intFile = 0 Then
MsgBox "No files found"
End If
'cycle through the list of files
For intFile = 1 To UBound(strFileList)
filename = path & strFileList(intFile)
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tmpShipping", filename, True 'Erroring on this line
Next intFile
DoCmd.SetWarnings True
End Sub
Anthony Berenguel
I'm guessing your table 'tmpShipping' has a field called 'Store Name', but your spreadsheet does not have a matching header called 'Store Name'. I would start by checking those two things.
actually, the error codes states the reverse.
you don't have field named "Store Name" in your table tblShipping.
Open your table tblShipping in design view and check the names of the fields,
you might have a field for "store Name", but check the spelling, space or underscore.
if you don't have the field, add it to your table.
you don't have field named "Store Name" in your table tblShipping.
Open your table tblShipping in design view and check the names of the fields,
you might have a field for "store Name", but check the spelling, space or underscore.
if you don't have the field, add it to your table.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Try importing the spreadsheet manually, and look at the resulting table to see what the field names and data types are. It might be best to first import the Excel data into a new table, and then run an append query to add the data to the regular table, converting data types as needed, since sometimes Excel values are not imported as the right data types for Access tables.
ASKER
Thanks capricorn1 adding the sheetname and cleaning up the file fixed the issue and it now importing to the temp table.