Relative file path VBA

[Schadenfreud]

I have a WinForm, which I'm calling via VBA's Shell command:

Sub Button1_Click()
    Shell "C:\\TemplateFuncitons.exe " + ActiveWorkbook.FullName, 1
End Sub

I made this module an Excel Add-in and added it as a button in the quick access toolbar. Now, the idea is for the WinForm.exe and the Excel.xla to always be in the same directory (go hand in hand). I want to make a relative path for the .exe which will be the path to the folder where the .xla is + the string "TemplateFunctions.exe" in the end.

Does anyone have any idea how/if I can achieve this?

 

[BrianMH]

You can get the path of the addin with ThisWorkbook.Path

Sub Button1_Click()
    Dim sEXEPath As String
    Dim sCmd As String
    sEXEPath = ThisWorkbook.Path & "\TemplateFunctions.exe"
    sCmd = sEXEPath & " " & ActiveWorkbook.FullName & ", 1"
    Shell sCmd
End Sub

 

[Schadenfreud]

This code gives me a file not found error. Last time I fixed it with making all the '\' into '\\', is there any way to do this here, without, for example, using a regex?

Also, would ThisWorkbook.Path get me the path to the .xlsx file invoking the module or the path to the .xla which contains the module?

 

[BrianMH]

It would get the path of the xla that contains the module.

You could try surrounding the whole exe path with double quotes.

Sub Button1_Click()    Dim sEXEPath As String
    Dim sCmd As String
    sEXEPath = Chr(34) & ThisWorkbook.Path & "\TemplateFunctions.exe" & Chr(34)
    sCmd = sEXEPath & " " & ActiveWorkbook.FullName & ", 1"
    Shell sCmd
End Sub

 

[Schadenfreud]

Thanks, worked like a charm!

 

[BrianMH]

Glad to help!