Link or Import Data from Excel to Access

  • Thread starter Thread starter ttp
  • Start date Start date
T

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.
 
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")
 
Back
Top