Button to import file

[helpexcel]

Hi,

I know there is a lot of data on these boards about this, but i'm totally confused. I would like to but a button in A1 that will ask for the file location, then import the selected excel file into the current worksheet starting at A3.

Thanks!!!

 

[John_w]

Put the button in A1 manually. Use FileDialog (msoFileDialogFilePicker) or GetOpenFilename to prompt for the file. Using the macro recorder, import an Excel file manually to A3 using the Data - Import External Data - Import Data menu (Excel 2003, should be similar for other versions). Edit the VBA generated to include the file prompt and use the file name selected by the user instead of the hard-coded file name used by the macro recorder. Finally, assign the button to the macro.

 

[helpexcel]

Thanks!! I am able to record the Macro, but i get lost when I try to edit it. Also, the file will be different each time. When i use the Import Data function, the query is attached to my worksheet and updated automatically?

 

[John_w]

Also, the file will be different each time.

That's why I said to edit the macro.

When i use the Import Data function, the query is attached to my worksheet and updated automatically?

Delete the query after it has imported the data using something like:

ActiveSheet.QueryTables(1).Delete

which assumes the query is on the active sheet - if not, change the ActiveSheet sheet reference to the appropriate sheet.

Post the code inside CODE tags if you need help with it.