CODEWITHSUNDEEP
vbaoutlook
NAlexP
NAlexP

Reputation: 183

How do I obtain the path towards the macro using the macro (vba code) itself?

Note: I am not interested in finding the path towards the worksheet, I intend to write the path to the worksheet in a text file that is located in the same folder as the .OTM file. I need to transform this code from hardcoded path to a path read from a text file located in the same folder as the macro.

How do I obtain the path towards the macro using the macro (vba code) itself?

Public xlApp As Object
Public xlWB As Object
Public xlSheet As Object
Sub OpenXl()
    Dim enviro As String
    Dim strPath As String
enviro = CStr(Environ("USERPROFILE"))
'the path of the workbook
 strPath = enviro & "\Documents\test2.xlsx"
     On Error Resume Next
     Set xlApp = GetObject(, "Excel.Application")
     If Err <> 0 Then
         Application.StatusBar = "Please wait while Excel source is opened ... "
         Set xlApp = CreateObject("Excel.Application")
         bXStarted = True
     End If
     On Error GoTo 0
     'Open the workbook to input the data
     Set xlWB = xlApp.Workbooks.Open(strPath)
     Set xlSheet = xlWB.Sheets("Sheet1")
    ' Process the message record
    On Error Resume Next
End Sub

Upvotes: 1

Views: 980

Answers (2)

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19782

The OTM file is stored here on my PC (Windows 7/Outlook 2010): 

strPath = Environ("userprofile") & "\AppData\Roaming\Microsoft\Outlook\"

Upvotes: 1

Olly
Olly

Reputation: 7891

Simply use:

    ThisWorkbook.Path

This returns the path of the workbook containing the code.

Upvotes: 0

Related Questions