Link or Import Data from Excel to Access

[ttp]

I have an Excel file that will be modified weekly. I would like to prompt
the user to enter the filename to be used weekly. This file will be imported
into Access to be used in a few queries. When the file is imported, I need
to ensure that the existing data in the table in Access is overwritten. I am
not sure how to prompt the user for a filename. I was trying to use a macro
using a TransferSpreadsheet method; but with this method, I would have to
have a standard filename. Can anyone assist me on how to prompt for a
filename and also overwrite the data in the existing table in Access.

 

[Ken Snell \(MVP\)]

With a macro, ask for the filename using the InputBox function. Put this
expression (including the = character) into the Filename argument of the
macro action:

="C:\FolderName\" & InputBox("Enter the filename of the EXCEL file")

or

="C:\FolderName\" & InputBox("Enter the filename (without .xls) of the EXCEL
file") & ".xls"

or

=InputBox("Enter the complete path and filename of the EXCEL file")

 

[ttp]

Thanks, Ken. I have tried this and I believe it is providing what I was
looking for.