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