Remove end folder from path found with ThisWorkbook.Path command ?

[dim]

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:

 

[Zack Barresse]

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

 

[Dave Peterson]

Check your previous thread.

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:

 

[Zack Barresse]

Hi Dave,

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

 

[dim]

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?

 

[Zack Barresse]

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

 

[Zack Barresse]

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.

 

[dim]

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

 

[Dave Peterson]

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]

LOL! <g>

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>