Folks,

I need to accomplish a very simple thing. When I load a workbook from a
directory, I need to know the directory that the workbook was loaded from
and change the current directory to that directory. Most of the time the
load directory turns out to be the current directory, but NOT always. My
app will fail unless it knows where its related files are located.

I do not want to force the user to load the workbooks in any particular
directory, any directory should be OK as long as all the workbooks,
documents and PowerPoint files in the app are on that same directory.

My solution is convoluted and ridicules:

Private Sub Workbook_Open()
ChDir (Left(ActiveWorkbook.FullName, Len(ActiveWorkbook.FullName) -
(Len(Dir(ActiveWorkbook.FullName)))))
End Sub

I am embarrassed that I cant seem to find a simple solution to this common
problem - e.g.Activeworkbook.WhereAmI. There must be a better way to
accomplish this than having to manipulate the ActiveWorkbook.FullName
string?

Thanks for your help.

Allan P. London, CPA
San Francisco, CA

Hello Allan,
Maybe this example will help.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware

Sub WhereAreWe()
Dim strHere As String
Dim strThere As String

'We are here.
strHere = CurDir

'Change location
strThere = ActiveWorkbook.Path
ChDrive strThere
ChDir strThere
MsgBox CurDir

'Return home
ChDrive strHere
ChDir strHere
MsgBox CurDir
End Sub
----------


"alondon"

wrote in message
Folks,
I need to accomplish a very simple thing. When I load a workbook from a
directory, I need to know the directory that the workbook was loaded from
and change the current directory to that directory. Most of the time the
load directory turns out to be the current directory, but NOT always. My
app will fail unless it knows where its related files are located.

I do not want to force the user to load the workbooks in any particular
directory, any directory should be OK as long as all the workbooks,
documents and PowerPoint files in the app are on that same directory.
My solution is convoluted and ridicules:

Private Sub Workbook_Open()
ChDir (Left(ActiveWorkbook.FullName, Len(ActiveWorkbook.FullName) -
(Len(Dir(ActiveWorkbook.FullName)))))
End Sub

I am embarrassed that I cant seem to find a simple solution to this common
problem - e.g.Activeworkbook.WhereAmI. There must be a better way to
accomplish this than having to manipulate the ActiveWorkbook.FullName
string?
Thanks for your help.
Allan P. London, CPA
San Francisco, CA

Hi alondon -

I suspect you are looking for the Path property. Try one of these:

ChDir ActiveWorkbook.Path
or
ChDir ThisWorkbook.Path

--
Jay


"alondon" wrote:

Folks,

I need to accomplish a very simple thing. When I load a workbook from a
directory, I need to know the directory that the workbook was loaded from
and change the current directory to that directory. Most of the time the
load directory turns out to be the current directory, but NOT always. My
app will fail unless it knows where its related files are located.

I do not want to force the user to load the workbooks in any particular
directory, any directory should be OK as long as all the workbooks,
documents and PowerPoint files in the app are on that same directory.

My solution is convoluted and ridicules:

Private Sub Workbook_Open()
ChDir (Left(ActiveWorkbook.FullName, Len(ActiveWorkbook.FullName) -
(Len(Dir(ActiveWorkbook.FullName)))))
End Sub

I am embarrassed that I cant seem to find a simple solution to this common
problem - e.g.Activeworkbook.WhereAmI. There must be a better way to
accomplish this than having to manipulate the ActiveWorkbook.FullName
string?

Thanks for your help.

Allan P. London, CPA
San Francisco, CA

There are some advantages in using the API to change the current directory:

Option Explicit
Private Declare Function SetCurrentDirectoryA _
Lib "kernel32" (ByVal lpPathName As String) As
Long

Function ChDirAPI(strFolder As String) As Long
'will return 1 on success and 0 on failure
'will work with a UNC path as well
'-----------------------------------------
ChDirAPI = SetCurrentDirectoryA(strFolder)
End Function


RBS


"alondon" wrote in message
...
Folks,

I need to accomplish a very simple thing. When I load a workbook from a
directory, I need to know the directory that the workbook was loaded from
and change the current directory to that directory. Most of the time the
load directory turns out to be the current directory, but NOT always. My
app will fail unless it knows where its related files are located.

I do not want to force the user to load the workbooks in any particular
directory, any directory should be OK as long as all the workbooks,
documents and PowerPoint files in the app are on that same directory.

My solution is convoluted and ridicules:

Private Sub Workbook_Open()
ChDir (Left(ActiveWorkbook.FullName, Len(ActiveWorkbook.FullName) -
(Len(Dir(ActiveWorkbook.FullName)))))
End Sub

I am embarrassed that I cant seem to find a simple solution to this common
problem - e.g.Activeworkbook.WhereAmI. There must be a better way to
accomplish this than having to manipulate the ActiveWorkbook.FullName
string?

Thanks for your help.

Allan P. London, CPA
San Francisco, CA

Dim varPath As String
varPath = Activeworkbook.Path

Regards,

Alan


"alondon" wrote in message
...
Folks,

I need to accomplish a very simple thing. When I load a workbook from a
directory, I need to know the directory that the workbook was loaded from
and change the current directory to that directory. Most of the time the
load directory turns out to be the current directory, but NOT always. My
app will fail unless it knows where its related files are located.

I do not want to force the user to load the workbooks in any particular
directory, any directory should be OK as long as all the workbooks,
documents and PowerPoint files in the app are on that same directory.

My solution is convoluted and ridicules:

Private Sub Workbook_Open()
ChDir (Left(ActiveWorkbook.FullName, Len(ActiveWorkbook.FullName) -
(Len(Dir(ActiveWorkbook.FullName)))))
End Sub

I am embarrassed that I cant seem to find a simple solution to this common
problem - e.g.Activeworkbook.WhereAmI. There must be a better way to
accomplish this than having to manipulate the ActiveWorkbook.FullName
string?

Thanks for your help.

Allan P. London, CPA
San Francisco, CA

Thanks Folks,

The Path property is exactly what I was looking for. I just forgot then got
really frustrated with the on-line help.

Also, it never occured to me to use the API. Thanks RB

Cheers,

Allan P. London, CPA


"alondon" wrote in message
...
Folks,

I need to accomplish a very simple thing. When I load a workbook from a
directory, I need to know the directory that the workbook was loaded from
and change the current directory to that directory. Most of the time the
load directory turns out to be the current directory, but NOT always. My
app will fail unless it knows where its related files are located.

I do not want to force the user to load the workbooks in any particular
directory, any directory should be OK as long as all the workbooks,
documents and PowerPoint files in the app are on that same directory.

My solution is convoluted and ridicules:

Private Sub Workbook_Open()
ChDir (Left(ActiveWorkbook.FullName, Len(ActiveWorkbook.FullName) -
(Len(Dir(ActiveWorkbook.FullName)))))
End Sub

I am embarrassed that I cant seem to find a simple solution to this common
problem - e.g.Activeworkbook.WhereAmI. There must be a better way to
accomplish this than having to manipulate the ActiveWorkbook.FullName
string?

Thanks for your help.

Allan P. London, CPA
San Francisco, CA