|
Discussion:
|
Knowing the current local directory can be quite valuable. For a given application, e.g., Excel, one can use ActiveWorkbook.Path. Or if the application is Word, you can use ActiveDocument.Path. These approaches are fine if the target code is specific to an application. But when the code is more general and could be used in multiple applications, then the task of determining the local directory is a bit more involved. LocalPath solves this problem using a brute force approach. It tests for the application name and then uses the relevant .Path property. For example, if LocalPath determines that the application is Excel, it uses ActiveWorkbook.Path. If LocalPath determines that the application is MSProject, it uses ActiveProject.Path. LocalPath is limited to MS applications. The demonstration consists of individual Excel, PowerPoint and Word files.
|
Sub Test_ActivePath()
MsgBox "Current active path is " & ActivePath
End Sub
Function ActivePath() As String
'
'****************************************************************************************
' Function returns the local path, i.e., the path to the current active "file"
' or object
' Limitations: MS applications only
'
'****************************************************************************************
'
'
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 = visPath
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 visPath() As String
visPath = ActiveDocument.Path
End Function
Function wrdPath() As String
wrdPath = ActiveDocument.Path
End Function
|