Use Excel VB to prompt for user to browse for a text file then import

[wabahn]

I would like to build a macro that prompts a user to locate a text file then import it into a work sheet in Excel. I wasn't able to fine any information on how to do this by browsing with Windows Explorer

 

[SkipVought]

Hi,

No need for VBA.

Just check out the Data > Import External Data > Import ...[/b[ feature.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[wabahn]

Thanks SkipVought, the data import is how I normally pull the data in. I have several users that are not Excel savvy so I am trying to make the spreadsheet very user friendly for them by prompting them to find the file then importing the information.

I used the macro recorder, and got this code however, it sets a fixed location for the file.

Range("A1").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Documents and Settings\Desktop\1852793_rev_1 txt.1", _
Destination:=Range("A1"))

Can I replace the c:\documents and setting\desktop\1852793_rev_1 txt.1 with a generic command that allows the user to browse for a target file?

While I am at it, the target file will always have a product number and rev number in the name. I would like to extract that information from the filename and use it later in the process to populate two cells in the spreadsheet. Right now, I have the user entering the information manually, but of course that has other problems ie keystroke errors, etc.

 

[bubba100]

Look into GetOpenFilename.

 

[SkipVought]

This approch is not different to the user than what you are attempting to do with VBA.

1. YOU set up the Import QueryTable.

2. You could but an IMPORT button on the sheet that would simple do Activesheet.QueryTables(1).Refresh False. Altermatively, the user does [/b] Data > Refresh[/b]

3. This refresh results in a PROMPT for th user (just like GetOpenFilename) to find a file for the import.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[wabahn]

Sorry it took so long to get back.

I ended up with a FindFile command.

As I started digging into my Txt files, they were generically identical, but the column spacings needed to be moved a character or 2 in some cases. The find file allowed me to verify that the column breaks were in the correct location. I ended up opening the file, running text to columns wizard then copy and paste process into my other spread sheet.

Not as elegant as I wanted, but functional for the short term.