Hello

I am trying to write a macro that prompts the user to choose 2 files to
open. The problem is that the files have the same name, but they are located
under two different folders. (The folders are different years, the files are
of the same type with different data.) I really don't want to go back and
change all the names of the files. Is there a way to search within different
folders?

Thanks

If the situation is that it is always this year and last year as an example,
have the user choose the file name, then open that filename from each
folder.

nm = InputBox("Enter filename")
sStr1 = "C:\2006\" & nm
sStr2 = "C:\2005\" & nm

Note that you can't have two files open with the same name, so you would
have to work with them sequentially.

--
Regards,
Tom Ogilvy



"MSHO" wrote in message
...
Hello

I am trying to write a macro that prompts the user to choose 2 files to
open. The problem is that the files have the same name, but they are
located
under two different folders. (The folders are different years, the files
are
of the same type with different data.) I really don't want to go back and
change all the names of the files. Is there a way to search within
different
folders?

Thanks

There are a whole bundle of different ways to do what you want so it is
really down to what you want to achieve. If you can clarify that then
we can help a bit further

Meanwhile; I presume that what you are trying to do is to ask which
year to open and then open just that file.

If the folders are arranged in a tidy form eg ...\2005\wkb.xls and
....\2006\wkb.xls then what I would do is not to search for the file but
to ask for the year. Then you can form the folder name using the reply
and open the wkb as normal.

If you really need a selection list then I suggest using a form with a
listbox populated with file and folder names (use the filesystem object
to compile the list) Then using a selection on the form open the file or
files as appropriate.

A


--
tony h
------------------------------------------------------------------------
tony h's Profile: http://www.excelforum.com/member.php...o&userid=21074
View this thread: http://www.excelforum.com/showthread...hreadid=500135

If I want to prompt for the year too, can i do that with an input box and put
it into the "C:\...."?

Thanks so much!

"Tom Ogilvy" wrote:

If the situation is that it is always this year and last year as an example,
have the user choose the file name, then open that filename from each
folder.

nm = InputBox("Enter filename")
sStr1 = "C:\2006\" & nm
sStr2 = "C:\2005\" & nm

Note that you can't have two files open with the same name, so you would
have to work with them sequentially.

--
Regards,
Tom Ogilvy



"MSHO" wrote in message
...
Hello

I am trying to write a macro that prompts the user to choose 2 files to
open. The problem is that the files have the same name, but they are
located
under two different folders. (The folders are different years, the files
are
of the same type with different data.) I really don't want to go back and
change all the names of the files. Is there a way to search within
different
folders?

Thanks

This is what I have so far... and it's not working... any clue?

Sub Macro2()

Dim filetypenm
filetypenm = InputBox("Enter File Name you would like to compare")

Dim nm
nm = InputBox("Enter Year you would like to compare")
Dim sStr1
sStr1 = "C:\Desktop\Coop Students\Small Package Rates\UPS Rates\" & nm \ " &
filetypenm"

Dim nm1
nm1 = InputBox("Enter Year you would like to compare it to")
Dim sStr2
sStr2 = "C:\Desktop\Coop Students\Small Package Rates\UPS Rates\" & nm1 \ "
& filetypenm"


End Sub

"tony h" wrote:


There are a whole bundle of different ways to do what you want so it is
really down to what you want to achieve. If you can clarify that then
we can help a bit further

Meanwhile; I presume that what you are trying to do is to ask which
year to open and then open just that file.

If the folders are arranged in a tidy form eg ...\2005\wkb.xls and
...\2006\wkb.xls then what I would do is not to search for the file but
to ask for the year. Then you can form the folder name using the reply
and open the wkb as normal.

If you really need a selection list then I suggest using a form with a
listbox populated with file and folder names (use the filesystem object
to compile the list) Then using a selection on the form open the file or
files as appropriate.

A


--
tony h
------------------------------------------------------------------------
tony h's Profile: http://www.excelforum.com/member.php...o&userid=21074
View this thread: http://www.excelforum.com/showthread...hreadid=500135

I added application.getopenfilename("C:\Desktop\Coop Students\Small Package
Rates\UPS Rates\" & nm \ " & filetypenm") instead of sStr1 but it still
doesnt work

I think it's my code. Any corrections would be appreciated

"MSHO" wrote:

This is what I have so far... and it's not working... any clue?

Sub Macro2()

Dim filetypenm
filetypenm = InputBox("Enter File Name you would like to compare")

Dim nm
nm = InputBox("Enter Year you would like to compare")
Dim sStr1
sStr1 = "C:\Desktop\Coop Students\Small Package Rates\UPS Rates\" & nm \ " &
filetypenm"

Dim nm1
nm1 = InputBox("Enter Year you would like to compare it to")
Dim sStr2
sStr2 = "C:\Desktop\Coop Students\Small Package Rates\UPS Rates\" & nm1 \ "
& filetypenm"


End Sub

"tony h" wrote:


There are a whole bundle of different ways to do what you want so it is
really down to what you want to achieve. If you can clarify that then
we can help a bit further

Meanwhile; I presume that what you are trying to do is to ask which
year to open and then open just that file.

If the folders are arranged in a tidy form eg ...\2005\wkb.xls and
...\2006\wkb.xls then what I would do is not to search for the file but
to ask for the year. Then you can form the folder name using the reply
and open the wkb as normal.

If you really need a selection list then I suggest using a form with a
listbox populated with file and folder names (use the filesystem object
to compile the list) Then using a selection on the form open the file or
files as appropriate.

A


--
tony h
------------------------------------------------------------------------
tony h's Profile: http://www.excelforum.com/member.php...o&userid=21074
View this thread: http://www.excelforum.com/showthread...hreadid=500135