I have been using the DoCmd.TransferSpreadsheet acImport option in VBA code and want to have the option of changing the file path of the excel file.



I have set up a table that stores the file path which is called Filep, the field name where the full path name is stored is called FilePath.

I have tried to amend the code but keep getting a "run-time error 424 - Object required"

Code is as follows :

msg = "Has today's file been processed in the Excel Workbook?"
Style = vbYesNo + vbInformation
Title = "Import Data"
response = MsgBox(msg, Style, Title, Help, Ctxt)

If response = vbYes Then
msg = "Are you sure you want to import the data"
Style = vbYesNo + vbInformation
Title = "Import Data"
response4 = MsgBox(msg, Style, Title, Help, Ctxt)

If response4 = vbYes Then



DoCmd.TransferSpreadsheet acImport, 8, "Sheet1", Table![Filep].[FilePath] & "Excel proc.xls", True, "Data!A:X"


'"D:\Documents and Settings\LaptopUser\My Documents\Winter 2005\Search Results\Excel proc.xls"

msg = "Data has been transferred"
Style = vbOKOnly + vbInformation
Title = "Import Data"
response2 = MsgBox(msg, Style, Title, Help, Ctxt)
End If
End If


If response = vbNo Then
msg = "Please ensure that the daily report has" & Chr(13) & "been compiled before proceeding"
Style = vbOKOnly + vbInformation
Title = "Import Data"
response3 = MsgBox(msg, Style, Title, Help, Ctxt)


End If
End Sub

Any help would be greatly appreciated.

J.