Hi folks, I'm really hoping someone can help me with this question, it has
two parts.

I have a folder called 'MyProgram', and within it I have a folder called
'Data'. In the MyProgram folder I have the currently active workbook,
'Book1.xls', and in the Data folder I have 'Book2.xls'.

Firstly I need to locate where the currently active workbook Book1.xls is on
the current hard drive? e.g: C:\Program Files\MyFolder...or D:\Example\The
Program\MyFolder etc

Then I need to adapt and use this information in a Workbooks.Open statement
to open Book2.xls in the Data subfolder?

Any help is much appreciated.

Sub WhereAmI()
MsgBox (ThisWorkbook.Path)
End Sub
--
Gary''s Student - gsnu200781

Thank GS,

So If I put into my auto_open macro:

Location = (ThisWorkbook.Path)

Where the path is C:\Program Files\Test\MyProgram\Book1.xls

How do I then use the variable 'Location' to open the workbook Book2.xls in
C:\Program Files\Test\MyProgram\Data\. Something like:

Workbooks.Open ($ Location $ "\" Data\Book2.xls)

??? Thanks for any more advice?


"Gary''s Student" wrote:

Sub WhereAmI()
MsgBox (ThisWorkbook.Path)
End Sub
--
Gary''s Student - gsnu200781

thisworkbook.path
will not include the filename.

It'll return something like:
C:\Program Files\Test\MyProgram

So maybe you could use:

Dim myFileName as string
dim wkbk2 as workbook
myfilename = thisworkbook.path & "\data\book2.xls"
set wkbk2 = workbooks.open(filename:=myfilename)


dim wrote:

Thank GS,

So If I put into my auto_open macro:

Location = (ThisWorkbook.Path)

Where the path is C:\Program Files\Test\MyProgram\Book1.xls

How do I then use the variable 'Location' to open the workbook Book2.xls in
C:\Program Files\Test\MyProgram\Data\. Something like:

Workbooks.Open ($ Location $ "\" Data\Book2.xls)

??? Thanks for any more advice?

"Gary''s Student" wrote:

Sub WhereAmI()
MsgBox (ThisWorkbook.Path)
End Sub
--
Gary''s Student - gsnu200781

--

Dave Peterson

Thanks Dave, that worked a charm, but I simplified it slightly for my needs.

Workbooks.Open (ThisWorkbook.Path & "\Data\Book2.xls")

Cheers. I appreciate it. :-)

"Dave Peterson" wrote:

thisworkbook.path
will not include the filename.

It'll return something like:
C:\Program Files\Test\MyProgram

So maybe you could use:

Dim myFileName as string
dim wkbk2 as workbook
myfilename = thisworkbook.path & "\data\book2.xls"
set wkbk2 = workbooks.open(filename:=myfilename)


dim wrote:

Thank GS,

So If I put into my auto_open macro:

Location = (ThisWorkbook.Path)

Where the path is C:\Program Files\Test\MyProgram\Book1.xls

How do I then use the variable 'Location' to open the workbook Book2.xls in
C:\Program Files\Test\MyProgram\Data\. Something like:

Workbooks.Open ($ Location $ "\" Data\Book2.xls)

??? Thanks for any more advice?

"Gary''s Student" wrote:

Sub WhereAmI()
MsgBox (ThisWorkbook.Path)
End Sub
--
Gary''s Student - gsnu200781

--

Dave Peterson

You don't even need the ()'s:

Workbooks.Open ThisWorkbook.Path & "\Data\Book2.xls"



dim wrote:

Thanks Dave, that worked a charm, but I simplified it slightly for my needs.

Workbooks.Open (ThisWorkbook.Path & "\Data\Book2.xls")

Cheers. I appreciate it. :-)

"Dave Peterson" wrote:

thisworkbook.path
will not include the filename.

It'll return something like:
C:\Program Files\Test\MyProgram

So maybe you could use:

Dim myFileName as string
dim wkbk2 as workbook
myfilename = thisworkbook.path & "\data\book2.xls"
set wkbk2 = workbooks.open(filename:=myfilename)


dim wrote:

Thank GS,

So If I put into my auto_open macro:

Location = (ThisWorkbook.Path)

Where the path is C:\Program Files\Test\MyProgram\Book1.xls

How do I then use the variable 'Location' to open the workbook Book2.xls in
C:\Program Files\Test\MyProgram\Data\. Something like:

Workbooks.Open ($ Location $ "\" Data\Book2.xls)

??? Thanks for any more advice?

"Gary''s Student" wrote:

Sub WhereAmI()
MsgBox (ThisWorkbook.Path)
End Sub
--
Gary''s Student - gsnu200781

--

Dave Peterson


--

Dave Peterson

Even Better! :) Tx.

"Dave Peterson" wrote:

You don't even need the ()'s:

Workbooks.Open ThisWorkbook.Path & "\Data\Book2.xls"

Hi Again!

I have another problem using the code below:

Workbooks.Open ThisWorkbook.Path & "\Data\Book2.xls"

Im now trying to use the same command in Book2.xls. The file and path is:
C:\Program Files\MyProgram\Data\Book2.xls

But I want to open the following:
C:\Program Files\MyProgram\Data1\Book3.xls

When I use:
Workbooks.Open ThisWorkbook.Path & "\Data1\Book3.xls"
Im told that the path C:\Program Files\MyProgram\Data\Data1\Book3.xls could
not be found.

How can I determine the path, then remove the last folder section from it
before adding in the new folder and file to open?....Is this possible?

I Need Help! :drowning: :(

You want to go up one level?

Workbooks.Open ThisWorkbook.Path & "\..\Book2.xls"

If you're old enough (ahem!)--before windows, you may remember your old DOS
commands.

C:
CD C:\Program Files\MyProgram\Data1

would go to the c: drive
then change to that data1 subdirectory.

CD ..
would come up a level.

cd ..\..
would come up 2 levels.

In some versions of windows,

CD ...
would back up 2 levels (IIRC)

======
You could also parse the string looking for the final backslash and strip things
that way, too.



dim wrote:

Hi Again!

I have another problem using the code below:

Workbooks.Open ThisWorkbook.Path & "\Data\Book2.xls"

Im now trying to use the same command in Book2.xls. The file and path is:
C:\Program Files\MyProgram\Data\Book2.xls

But I want to open the following:
C:\Program Files\MyProgram\Data1\Book3.xls

When I use:
Workbooks.Open ThisWorkbook.Path & "\Data1\Book3.xls"
Im told that the path C:\Program Files\MyProgram\Data\Data1\Book3.xls could
not be found.

How can I determine the path, then remove the last folder section from it
before adding in the new folder and file to open?....Is this possible?

I Need Help! :drowning: :(

--

Dave Peterson

Wow! LOL, yeah somewhere in the dark recesses of my mind I remember the Dos
prompt.....and I mean the REAL Dos prompt, not the pretend 'Dos Prompt'
within Windows which never seemed to work for me, and which I can't even
remember how to find anymore! :)

I have some not very fond memories of trying to learn Q-Basic. I think I
wrote one of those text only games where your told "Your standing at a
crossroads, ahead of you is a dark forest, to the left is a sunny paved road,
which way to yo u want to go?" etc etc....do you remember them? :-D lol

Thanks again Dave......your some sort of incredible Excel Guru (Compliment).
:-D

It worked a charm, but before the workbook I had to put in my new folder,
\..\Data1\

Glad you got it working. (I didn't get that you still wanted to use data1--but
who cares <vbg.)

I remember the game--I don't recall the name.

But in my googling, I found this.
http://en.wikipedia.org/wiki/Star_Trek_(text_game)

We played it on a mainframe in the 70's (IIRC).

Zork????

http://en.wikipedia.org/wiki/Zork_I

======
The internet is not a waste of time.
The internet is not a waste of time.
The internet is not a waste of time.
The internet is not a waste of time.
The internet is not a waste of time.



dim wrote:

Wow! LOL, yeah somewhere in the dark recesses of my mind I remember the Dos
prompt.....and I mean the REAL Dos prompt, not the pretend 'Dos Prompt'
within Windows which never seemed to work for me, and which I can't even
remember how to find anymore! :)

I have some not very fond memories of trying to learn Q-Basic. I think I
wrote one of those text only games where your told "Your standing at a
crossroads, ahead of you is a dark forest, to the left is a sunny paved road,
which way to yo u want to go?" etc etc....do you remember them? :-D lol

Thanks again Dave......your some sort of incredible Excel Guru (Compliment).
:-D

It worked a charm, but before the workbook I had to put in my new folder,
\..\Data1\

--

Dave Peterson