J
JIM
When trying to convert excel files to access I use this subroutine:
Do While Len(strFileName) <> 0
'After Dir function returns all .xl files, it returns a zero length string ""
If IsNull((DLookup("[FileName]", "WOProduction", "[FileName] = '" &
strFileName & "'"))) Then 'If file is not found then enter loop
DoCmd.TransferSpreadsheet acLink, , "WOTempTable", strPath &
strFileName, True 'Link Excel file to Temptable
CurrentDb.Execute ("AppendQuery"), dbFailOnError
'Execute
AppendQuery-query that read from TempTable and Appends the records to
production table
DoCmd.DeleteObject acTable, "Temptable"
'Delete link, but not the file
CurrentDb.Execute ("INSERT INTO WOProduction( [FileName],[ImportDate] )
Values ('" & strFileName & "', #" & Date & "#);"), dbFailOnError
'Insert
file into work orders
End If
strFileName = Dir()
Loop
Access hangs up on the IsNull line and gives me message "The expression you
entered as a query parameter produced this error, '2471'. 'The object
doesn't contain the automation object 'Filename''
I've searched help for automation objects but do not understand the
connection. What do I need to do in this subroutine?
Thanks, JIM
Do While Len(strFileName) <> 0
'After Dir function returns all .xl files, it returns a zero length string ""
If IsNull((DLookup("[FileName]", "WOProduction", "[FileName] = '" &
strFileName & "'"))) Then 'If file is not found then enter loop
DoCmd.TransferSpreadsheet acLink, , "WOTempTable", strPath &
strFileName, True 'Link Excel file to Temptable
CurrentDb.Execute ("AppendQuery"), dbFailOnError
'Execute
AppendQuery-query that read from TempTable and Appends the records to
production table
DoCmd.DeleteObject acTable, "Temptable"
'Delete link, but not the file
CurrentDb.Execute ("INSERT INTO WOProduction( [FileName],[ImportDate] )
Values ('" & strFileName & "', #" & Date & "#);"), dbFailOnError
'Insert
file into work orders
End If
strFileName = Dir()
Loop
Access hangs up on the IsNull line and gives me message "The expression you
entered as a query parameter produced this error, '2471'. 'The object
doesn't contain the automation object 'Filename''
I've searched help for automation objects but do not understand the
connection. What do I need to do in this subroutine?
Thanks, JIM