Pulling files in using Macros

[MSHO]

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

[Tom Ogilvy]

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>

[tony h]

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

[MSHO]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >
> >
>
>
>

[MSHO]

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
>
>

[MSHO]

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
> >
> >