1

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
Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
NAlexP
  • 183
  • 1
  • 16
  • 1. Find the property that tells you were the excel file is. First google reponse: https://stackoverflow.com/questions/2813925/how-to-get-the-path-of-current-worksheet-in-vba 2. Change `strPath` to refer to that instead – Nick.Mc Aug 17 '17 at 11:54
  • 1
    Possible duplicate of [How to get the path of current worksheet in VBA?](https://stackoverflow.com/questions/2813925/how-to-get-the-path-of-current-worksheet-in-vba) – Tim Schmidt Aug 17 '17 at 12:05
  • I'm not interested in finding the path to the workbook, I'm interested in finding the path to the macro, so I know where to read my text file from. – NAlexP Aug 17 '17 at 12:06

2 Answers2

1

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

strPath = Environ("userprofile") & "\AppData\Roaming\Microsoft\Outlook\"
Darren Bartrup-Cook
  • 18,362
  • 1
  • 23
  • 45
  • Yes, but is there no way to find the path using ol vba script? Say I have it in a folder located on my desktop, and then I move the folder in my documents or something. – NAlexP Aug 17 '17 at 12:43
  • I was looking into that. As far as I can tell it's pretty much got to be in that folder for that version otherwise it doesn't work. From Sue Moshers old page: _All Outlook macros are stored in a single file named VbaProject.otm in the user's %appdata%\Microsoft\Outlook folder_ http://www.outlookcode.com/article.aspx?id=28 – Darren Bartrup-Cook Aug 17 '17 at 12:53
  • So the macro simply doesn't work if it's not stored in said folder? – NAlexP Aug 17 '17 at 13:00
  • It doesn't look like it. I did find some posts saying you could use a switch when opening Outlook but it had been deprecated in 2010 (/altvba). https://social.msdn.microsoft.com/Forums/office/en-US/c64af38f-f333-4da0-ae9f-6e866ac58efc/moving-vbaprojectotm?forum=outlookdev – Darren Bartrup-Cook Aug 17 '17 at 13:05
0

Simply use:

    ThisWorkbook.Path

This returns the path of the workbook containing the code.

Olly
  • 7,749
  • 1
  • 19
  • 38
  • I use outlook vba, so when I try to `debug.print thisworkbook.path` it gives me an Object required error. – NAlexP Aug 17 '17 at 12:19
  • It does however return the appropriate path towards the workbook when i use `debug.print xlWB.Path`, but that is of no help to me. – NAlexP Aug 17 '17 at 12:22
  • Referring to https://stackoverflow.com/questions/233702/where-does-outlook-store-the-vba-code-files, it appears Outlook code is stored in the users `%appdata%\Microsoft\Outlook` folder. What path are you trying to get? – Olly Aug 17 '17 at 12:24
  • Say I move the .OTM to another folder, is there no way for me to use vba script to tell where it's located? – NAlexP Aug 17 '17 at 12:42