Hi,

I have a problem using the code below:

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

Im trying to use the above command within 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: :(

Hi there,

Checking for the path separator should get you what you want. I don't
believe 97 has this feature (InStrRev) so you'll have to loop backwards if
that is the case. I'm assuming you're not on 97?? If so, this line should
get you what you need ...

Left(ThisWorkbook.Path, Len(ThisWorkbook.Path) - InStrRev(ThisWorkbook.Path,
Application.PathSeparator))

Set it to a variable (probably easier in lieu of its length) and add it to
your line of code instead of ThisWorkbook.Path, i.e. ....

Dim sPath As String
sPath = Left(ThisWorkbook.Path, Len(ThisWorkbook.Path) -
InStrRev(ThisWorkbook.Path, Application.PathSeparator))
Workbooks.Open ThisWorkbook.Path & "\Data1\Book3.xls"

HTH

--
Zack Barresse



"dim" wrote in message
...
Hi,

I have a problem using the code below:

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

Im trying to use the above command within 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: :(

Check your previous thread.

dim wrote:

Hi,

I have a problem using the code below:

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

Im trying to use the above command within 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

Hi Dave,

Where do you find these threads? I certainly would have thought twice for
double- or cross-posting(s)...

--
Zack Barresse



"Dave Peterson" wrote in message
...
Check your previous thread.

dim wrote:

Hi,

I have a problem using the code below:

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

Im trying to use the above command within 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

Hi Zach,

I entered it as below and was told that C:\Pr\Data\Data1\Book3.xls could not
be found ?

Dim sPath As String
sPath = Left(ThisWorkbook.Path, Len(ThisWorkbook.Path) -
InStrRev(ThisWorkbook.Path, Application.PathSeparator))
Workbooks.Open sPath & "\Data1\Book3.xls", UpdateLinks:=3

Any ideas?

What version are you working with and what is your full code?


"dim" wrote in message
...
Hi Zach,

I entered it as below and was told that C:\Pr\Data\Data1\Book3.xls could
not
be found ?

Dim sPath As String
sPath = Left(ThisWorkbook.Path, Len(ThisWorkbook.Path) -
InStrRev(ThisWorkbook.Path, Application.PathSeparator))
Workbooks.Open sPath & "\Data1\Book3.xls", UpdateLinks:=3

Any ideas?

Oops, I'm sorry, you wouldn't be taking the length of it away. Wrong
direction of travel for that! Just use ....

sPath = Left(ThisWorkbook.Path, InStrRev(ThisWorkbook.Path,
Application.PathSeparator))

HTH.. and sorry about the confusion.

--
Zack Barresse



"dim" wrote in message
...
Hi Zach,

I entered it as below and was told that C:\Pr\Data\Data1\Book3.xls could
not
be found ?

Dim sPath As String
sPath = Left(ThisWorkbook.Path, Len(ThisWorkbook.Path) -
InStrRev(ThisWorkbook.Path, Application.PathSeparator))
Workbooks.Open sPath & "\Data1\Book3.xls", UpdateLinks:=3

Any ideas?

Sorry about the second post, but I thought no-one would see the previous one
since it had be left as answered. Cheers Dave.

For anyone who sees this post in the future Dave kindly answered the
question thus:

Thanks to Zack for the help also.

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

I happened to answer Dim(!)'s followup post right before I saw this one.

The subject is:
Find WkBk Path, Then use this path to open another WkBk in Sub

===
But your response was much more VBAish. <bg

Zack Barresse wrote:

Hi Dave,

Where do you find these threads? I certainly would have thought twice for
double- or cross-posting(s)...

--
Zack Barresse

"Dave Peterson" wrote in message
...
Check your previous thread.

dim wrote:

Hi,

I have a problem using the code below:

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

Im trying to use the above command within 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

--

Dave Peterson

LOL! <g


"Dave Peterson" wrote in message
...
I happened to answer Dim(!)'s followup post right before I saw this one.

The subject is:
Find WkBk Path, Then use this path to open another WkBk in Sub

===
But your response was much more VBAish. <bg

Zack Barresse wrote:

Hi Dave,

Where do you find these threads? I certainly would have thought twice
for
double- or cross-posting(s)...

--
Zack Barresse

"Dave Peterson" wrote in message
...
Check your previous thread.

dim wrote:

Hi,

I have a problem using the code below:

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

Im trying to use the above command within 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

--

Dave Peterson