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

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

--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"ArielZusya" wrote:

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

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" wrote:

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

--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"ArielZusya" wrote:

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

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

--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"ArielZusya" wrote:

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" wrote:

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

--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"ArielZusya" wrote:

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

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" wrote in message
...
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" wrote:

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

--
-John
Please rate when your question is answered to help us and others know
what
is helpful.


"ArielZusya" wrote:

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

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