Allowing user to locate and select a file and save results in stri

[ArielZusya]

I've got an excel workbook that imports a text file, formats the data it
imports, and posts the text into multiple sheets sorting the data on those
sheet. Right now I have the workbook look for a specific file in a specific
location and consequently my VBA uses the location of the imported file (full
path: c:\documents and settings\username\desktop\filename.txt) in some spots
of the code and the file name (with no path: filename.txt) in other spots.
I'd really like to make this more dynamic and allow the user to actually
browse to and select the file and then have the location of the imported file
and the file name be saved into two strings to be used in the VBA as needed.
Is there an easy way to do this? Thanks!

-Ariel

 

[John Bundy]

Give this a try, put it where you want to ask for the file, the msgbox is
just to show you what that string will contain.

myFilename1 = Application.GetOpenFilename
MsgBox myFilename1

 

[ArielZusya]

Hi John,

Thanks for your help. So... that seems to cover half of what I want to
do... the other half is to parse the results into file name seperate from
path with file name. I'm still new with all of this. Is there an easy way
to break the myFilename1 into two strings: myFilename1 as it is now and then
myFile1 for the file name without the path? I feel a bit like I'm watching
powerball results and I've matched all but the last number which they are
about to read. Thanks!

-Ariel

 

[John Bundy]

I'm sure there are more simple ways, but I don't quite remember since there
is no Find but here is the whole shabang, mypath is the entire path
myfilename is just the filename
mypath = Application.GetOpenFilename

For i = Len(mypath) To 1 Step -1
myfilename = Mid(mypath, i, 1) & myfilename
If Mid(mypath, i - 1, 1) = "\" Then Exit For
Next


MsgBox myfilename

 

[Steve Yandl]

As John says, there are different ways to get at the file name. Below is
one option.

tgtFullNm = Application.GetOpenFilename
pathArray = Split(tgtFullNm, "\")
fileNm = pathArray(UBound(pathArray))
MsgBox tgtFullNm
MsgBox fileNm


Steve

 

[ArielZusya]

Steve and John,

Thank you both for your help. This is exactly what I was hoping to learn.
If only winning the powerball was as easy. Thanks again!

-Ariel