Multiple Apps

Determine the current local path for an application

Ease of Use

Intermediate

Version tested with

2000, 2003 

Submitted by:

MWE

Description:

Determines the ?local path?, i.e., the directory in which the currently executing or active program file resides. 

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. 

Code:

instructions for use 

			
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

How to use:

  1. Copy the above code.
  2. Open any workbook.
  3. Press Alt + F11 to open the Visual Basic Editor (VBE).
  4. In the left side window, hi-lite the target spreadsheet [it will likely be called VBAProject(name.xls) where name is the name of the spreadsheet]
  5. Select an existing code module for the target worksheet; or from the Insert Menu, choose Insert | Module.
  6. Paste the code into the right-hand code window.
  7. Add user-defined code as appropriate
  8. Close the VBE, save the file if desired.
  9. See ?Test The Code? below
 

Test the code:

  1. Open any of the example files
  2. Click on the command button
 

Sample File:

ActivePath.zip 32.62KB 

Approved by mdmackillop


This entry has been viewed 81 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express