open .xls one directory up

[arie ribbens]

Hi,

I have an excel file that needs to open another .xls file located one directory higher.
This is the code i have so far:

Dim StrPath, StrName As String

StrName = "file.xls"
StrPath = ActiveWorkbook.Path & "\"

ChDir (StrPath)

ChDir ".."

Workbooks.Open (StrName)

The strange thing is that sometimes this code works perfect, but other times it returns an error saying it can't find the file. I'm not sure, but i think it has something to do with the systems memory.

Anyone has an idea how i can put the new path (one folder higher after the ChDir ".." line) in a string so i can change:

Workbooks.Open (StrName)
to
Workbooks.Open (StrPath + StrName)

?

Thanks.

Cheers,
Arie

[markf]

Hi Arie,

All versions of M$ Windows can resolve the path ...

Workbooks.Open (ActiveWorkbook.Path & "\..\" & StrName)

or, to always return a legal path in unusual circumstances ...

Workbooks.Open (ActiveWorkbook.Path & IIf(Right$(ActiveWorkbook.Path, 1) = "\", "", "\..\") & StrName)