Importing a text file to an existing sheet, with a dialog to pick the text file

[titushanke]

Hi everyone,

I have been trying to find information on this but haven't succeeded in all parts. Here is what I want to do in Excel (2003 compatible VBA).

I have an Excel workbook with 2 worksheets: SEG and IMPORT
SEG: contains formulas and VLOOKUPs to the Import sheet.
IMPORT: contains a text file (comma separated), and is not modified in any way from when it is opened.

I would like a macro to open a dialog window (asking me for the text file), then open the text file and copy it's contents into the IMPORT worksheet of the workbook.

Some issues:

• the master Excel Workbook can have different names and be saved in different locations.
• The text file to be imported can also have different names (hence the dialog)

Does anybody have an idea how to achieve this or could lend me a hand?
Thanks in advance...

Best regards,
Titus

 

[RichardMGreen]

If you want to use the import wizard, make sure the "Prompt for filename on refresh" box is ticked. That will ask you for the filename when the query is refreshed and isn't dependant on anything being in the same place every time.

 

[titushanke]

Given that some of the users won't be very sophisticated Excel users, I was thinking of a VBA macro to accomplish pretty much that task:

1.) Dialog to open file
2.) automatic copying and pasting to the sheet needed (IMPORT)
3.) Closing of text file

They will get confused if they have to use the wizard, I am afraid...
Besides, there are different Excel versions around and it's a mess

Thanks for any help with the macro...

Titus

 

[RichardMGreen]

If you import the data using the wizard for initial import and save the query with the "Prompt...." option ticked, you can use a macro to refresh all the items in the workbook which will prompt for the location and filename for each import.
The code is thisworkbook.refreshall

Just make sure that for each import, enable background refresh in not ticked or Excel can do some peculiar things.

 

[titushanke]

Cool stuff, wasn't aware it's that easy. Is this backwards compatible between Excel 2007 (that I use) and Excel 2003?

It's reaaally cool, thanks a lot

 

[RichardMGreen]

I think so, but it would be worth double checking.