Find WkBk Path, Then use this path to open another WkBk in Subfold
 

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.

Find WkBk Path, Then use this path to open another WkBk in Subfold
 

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

Find WkBk Path, Then use this path to open another WkBk in Sub
 

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

Find WkBk Path, Then use this path to open another WkBk in Sub
 

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

Find WkBk Path, Then use this path to open another WkBk in Sub
 

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

Find WkBk Path, Then use this path to open another WkBk in Sub
 

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

Find WkBk Path, Then use this path to open another WkBk in Sub
 

Even Better! :) Tx.

"Dave Peterson" wrote:

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

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

Find WkBk Path, Then use this path to open another WkBk in Sub
 

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

Find WkBk Path, Then use this path to open another WkBk in Sub
 

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

Find WkBk Path, Then use this path to open another WkBk in Sub
 

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\

Find WkBk Path, Then use this path to open another WkBk in Sub
 

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