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" <(E-Mail Removed)> wrote in message
news:16AAED31-403D-466E-9BD3-(E-Mail Removed)...
> 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