How to create a Macro or VBA that imports an Excel file (variable name) which has a variable location.

[Galapagos15]

How can I create a Macro or VBA that imports an Excel file (has variable name) and the location of the file is also variable? i.e When the user runs the Macro/VBA it prompts the user to locate the file and then once it's selected Access will import it into the table named "Table1". Thanks in advance!

 

[Micron]

Check out the File Picker. It will enable user to located and select a file and the path is returned to your code if a file is selected. I think the latest version is 4 and can be called by Application.FileDialog(4) but I might be wrong about that. I presume you want to import the file as a linked table, or put the path in some table. I doubt you are saying you actually want to import the file as an object in a table. To link, use DoCmd.Transferspreadsheet, acLink,, "Table1", "Path to file", True, "SheetName!RangeOfData"
My guess is that your sheet will need a named range that is dynamic unless you set some arbitrary large range so that you don't leave data behind.

 

[Galapagos15]

Okay, thank you I will look into the File Picker.