Determining "Local" Directory

[MWE]

I posted this earlier, but it seems to have disappeared ...

Virtually all MS appls provide a "path" property for the path of the current "file". For example, Excell has ActiveWorkbook.path and MSProject has ActiveProject.path. This approach works fine if the procedure needing the local path is application specific. But for more general procs that can be used with multiple appls, a more general approach is needed.

I wrote a brute force proc a while ago that simply tests for the application type and branches accordingly. The meat of the proc is:


Select Case LCase(Application.Name)
Case "microsoft access"
objPath = CurrentProject.Path
Case "microsoft excel"
objPath = ActiveWorkbook.Path
Case "microsoft outlook"
objPath = "**not defined**"
Case "microsoft powerpoint"
objPath = ActivePresentation.Path
Case "microsoft project"
objPath = ActiveProject.Path
Case "microsoft visio"
objPath = "**not defined**"
Case "microsoft word"
objPath = ActiveDocument.Path
Case Else
objPath = "**not defined**"
End Select

There must be a more elegant way to do this. Any ideas?

Thanks

[Killian]

Hiya,

i've given this some thought and I think you might be stuck with your "brute force" solution.

The missing link here is a common denominator across office apps that can lead you back to the path for the active doc/project/etc... object. i thought using the parent property of the selection object might help but there are issues to overcome which begin re-define the word "inelegant", which isn't what you're looking for.

Likewise with the "ActiveWindow" object, which seemed promising for a moment (with the filename in the caption property) but lead nowhere.

The only other common object I can think of that returns the activewhatever's path is the Web toolbar, which you may want to try out but I'm not sure it would be as reliable as your current procedure.

My feeling is, within the limitations of the various Office object models

[MWE]

Hiya,
i've given this some thought and I think you might be stuck with your "brute force" solution.

The missing link here is a common denominator across office apps that can lead you back to the path for the active doc/project/etc... object. i thought using the parent property of the selection object might help but there are issues to overcome which begin re-define the word "inelegant", which isn't what you're looking for.

Likewise with the "ActiveWindow" object, which seemed promising for a moment (with the filename in the caption property) but lead nowhere.

The only other common object I can think of that returns the activewhatever's path is the Web toolbar, which you may want to try out but I'm not sure it would be as reliable as your current procedure.

My feeling is, within the limitations of the various Office object models
Thanks for your thoughts. I have just gotten back to this ... The original code worked OK but had a major drawback -- it required that all appl libraries be referenced or the various "active" objects would not be understood and "Variable Not Defined" errors occured at compile. Referencing all libraries seems clumsy. The problem can be eliminated by removing the Option Explicit declaration, but I would prefer to not resort to that.

One solution I tried was rewriting the basic code and calling individual functions each handling a given appl type.
original code:


Function ActivePath() As String
' Title ActivePath
' Target objication: any
' Function returns the path to the current active "file" or object
' Limitations:
' 1. MS applications only
' 2. can not be used in a module which has Option Explicit in place
' Passed Values: NONE
' Public/Private Variables used: NONE
' MWETools Subroutines Called: NONE
' External Files Accessed: NONE
' Orig Date 10-Apr-2004
' Orig Author MWE
' HISTORY
' 15May'05 MWE renamed from objPath to ActivePath
On Error Resume Next
Select Case LCase(Application.Name)
Case "microsoft access"
ActivePath = CurrentProject.Path
Case "microsoft excel"
ActivePath = ActiveWorkbook.Path
Case "microsoft outlook"
ActivePath = "**not defined**"
Case "microsoft powerpoint"
ActivePath = ActivePresentation.Path
Case "microsoft project"
ActivePath = ActiveProject.Path
Case "microsoft visio"
ActivePath = "**not defined**"
Case "microsoft word"
ActivePath = ActiveDocument.Path
Case Else
ActivePath = "**not defined**"
End Select
End Function

revised code


Function ActivePath() As String
' Title ActivePath
' Target objication: any
' Function returns the path to the current active "file" or object
' Limitations: MS applications only
' Passed Values: NONE
' Passed Values: NONE
' Public/Private Variables used: NONE
' MWETools Subroutines Called: NONE
' External Files Accessed: NONE
' Orig Date 10-Apr-2004
' Orig Author MWE
' HISTORY
' 15May'05 MWE renamed from objPath to ActivePath
' 20May'05 MWE rewrote code to move active object references to separate functinos;
' this eliminated "variable not defined" errors and the proc can now
' be in a module with Option Explicit
Dim ApplName As String
' fetch application name and strip off "microsoft"
ApplName = LCase(Application.Name)
ApplName = Right(ApplName, Len(ApplName) - 10)
On Error Resume Next
Select Case ApplName
Case "access"
ActivePath = accPath
Case "excel"
ActivePath = xlPath
Case "outlook"
ActivePath = "**not defined**"
Case "powerpoint"
ActivePath = pptPath
Case "project"
ActivePath = projPath
Case "visio"
ActivePath = "**not defined**"
Case "word"
ActivePath = wrdPath
Case Else
ActivePath = "**not defined**"
End Select
End Function

Function accPath() As String
accPath = CurrentProject.Path
End Function

Function xlPath() As String
xlPath = ActiveWorkbook.Path
End Function

Function pptPath() As String
pptPath = ActivePresentation.Path
End Function

Function projPath() As String
projPath = ActiveProject.Path
End Function

Function wrdPath() As String
wrdPath = ActiveDocument.Path
End Function

This works, but requires that all functions be moved around as a group.

Any other ideas on how to solve the undeclared variables problem?

Thanks