Error with DoCmd.TransferSpreadsheet (1 Viewer)

[Andre Bruin]

Hi there,

I want to import several Excel sheets into a Access table.

I'm using below code, but for some reason I get an error message "error 3270 property not found"

code:
#Sub Import()
Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String
Dim blnHasFieldNames As Boolean
' Change this next line to True if the first row in EXCEL worksheet
' has field names
blnHasFieldNames = True

' Replace C:\Documents\ with the real path to the folder that
' contains the EXCEL files
strPath = "C:\Users\nlbruand\Documents\expeditie_opdrachten\ritbestanden\"

' Replace tablename with the real name of the table into which
' the data are to be imported
strTable = "tablename"

strFile = Dir(strPath & "*.xls")
Do While Len(strFile) > 0

strPathFile = strPath & strFile
MsgBox strPathFile
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "tablename", strPathFile, True, "deliveries!A1:C2"
' Uncomment out the next code step if you want to delete the
' EXCEL file after it's been imported
' Kill strPathFile

strFile = Dir()
Loop
End Sub#

Please, can somebody help me?

thanks,

André

 

[JHB]

In which code line do you get the error?
Is the name of the table you want to import into really "tablename"?

 

[Andre Bruin]

I get the error in below line:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "tablename", strPathFile, True, "deliveries!A1:C2"

for now the name of the table is really "tablename"

 

[JHB]

Try by doing a "Compact & Repair".
Else post your database + Excel sheet with some sample data, zip it.

 

[Andre Bruin]

Hi,

it's solved. The problem was the headers of the table in Access and the Excel sheet. They were not equal and that causes the error.

rg,
André