How can I find the installation directory of a specific program?

Question

I have successfully coded some VBA macros for work which basically create a data file, feed it to a program and post-treat the output from this program. My issue is that the program installation path is hard coded in the macro and the installation may vary accross my colleagues computers.

The first thing I thought is that I can gather from everyone the different installation directories and test for all of them in the code. Hopefully, one of them will work. But it doesn't feel that clean.

So my other idea was to somehow get the installation directory in the code. I thought it would be possible as in Windows, if I right click on a shortcut, I can ask to open the file's directory. What I'm basically looking for is an equivalent in VBA of this right click action in Windows. And that's where I'm stuck. From what I found, Windows API may get the job done but that's really out of what I know about VBA.

The API FindExecutable seemed not too far from what I wanted but I still can't manage to use it right. So far, I can only get the program running if I already know its directory.

Could you give me some pointers ? Thanks.

Answer 1

Here's another method for you to try. Note that you might see a black box pop up for a moment, that's normal.

Function GetInstallDirectory(appName As String) As String

    Dim retVal As String
    retVal = Split(CreateObject("WScript.Shell").Exec("CMD /C FOR /r ""C:\"" %i IN (*" & appName & ") DO (ECHO %i)").StdOut.ReadAll, vbCrLf)(2)
    GetInstallDirectory = Left$(retVal, InStrRev(retVal, "\"))

End Function

It's not as clean as using API but should get the trick done.

---

Summary:

retVal = Split(CreateObject("WScript.Shell").Exec("CMD /C FOR /r ""C:\"" %i IN (*" & appName & ") DO (ECHO %i)").StdOut.ReadAll, vbCrLf)(1)

• "CMD /C FOR /r ""C:\"" %i IN (*" & appName & ") DO (ECHO %i)" is a command that works in CMD to loop through files rooted at a defined path. We use the wildcard with the appName variable to test for the program we want. (more info on FOR /R here) Here, we have created the CMD application using a Shell object (WScript.Shell) and Executed the command prompt CMD passing arguments to it directly after. The /C switch means that we want to pass a command to CMD and then close the window immediately after it's processed.

• We then use .StdOut.ReadAll to read all of the output from that command via the Standard Output stream.

• Next, we wrap that in a Split() method and split the output on vbCrLf (Carriage return & Line feed) so that we have a single dimension array with each line of the output. Because the command outputs each hit on a new line in CMD this is ideal.

• The output looks something like this:

C:\Users\MM\Documents>(ECHO C:\Program Files\Microsoft Office\Office14\EXCEL.EXE ) C:\Program Files\Microsoft Office\Office14\EXCEL.EXE

C:\Users\MM\Documents>(ECHO C:\Windows\Installer\$PatchCache$\Managed\00004109110000000000000000F01FEC\14.0.4763\EXCEL.EXE ) C:\Windows\Installer\$PatchCache$\Managed\00004109110000000000000000F01FEC\14.0.4763\EXCEL.EXE

C:\Users\olearysa\Documents>(ECHO C:\Windows\Installer\$PatchCache$\Managed\00004109110000000000000000F01FEC\14.0.7015\EXCEL.EXE ) C:\Windows\Installer\$PatchCache$\Managed\00004109110000000000000000F01FEC\14.0.7015\EXCEL.EXE

• We're only interested in the third line of the output (the first line is actually blank), so we can access that index of the array directly by using (2) after it (because arrays are zero-indexed by default)

• Finally, we only want the path so we use a combination of Left$() (which will return n amount of characters from the left of a string) and InStrRev() (which returns the position of a substring starting from the end and moving backwards). This means we can specify everything from the left until the first occurrence of \ when searching backwards through the string.

Answer 2

Give this a try, assuming you know the name of the .exe:

#If Win64 Then
    Declare PtrSafe Function FindExecutable Lib "shell32.dll" Alias "FindExecutableA" _
        (ByVal lpFile As String, ByVal lpDirectory As String, ByVal lpResult As String) As Long
#Else
    Declare Function FindExecutable Lib "shell32.dll" Alias "FindExecutableA" _
        (ByVal lpFile As String, ByVal lpDirectory As String, ByVal lpResult As String) As Long
#End If

Const SYS_OUT_OF_MEM        As Long = &H0
Const ERROR_FILE_NOT_FOUND  As Long = &H2
Const ERROR_PATH_NOT_FOUND  As Long = &H3
Const ERROR_BAD_FORMAT      As Long = &HB
Const NO_ASSOC_FILE         As Long = &H1F
Const MIN_SUCCESS_LNG       As Long = &H20
Const MAX_PATH              As Long = &H104

Const USR_NULL              As String = "NULL"
Const S_DIR                 As String = "C:\" '// Change as required (drive that .exe will be on)


Function GetInstallDirectory(ByVal usProgName As String) As String

    Dim fRetPath As String * MAX_PATH
    Dim fRetLng As Long

    fRetLng = FindExecutable(usProgName, S_DIR, fRetPath)

    If fRetLng >= MIN_SUCCESS_LNG Then
        GetInstallDirectory = Left$(Trim$(fRetPath), InStrRev(Trim$(fRetPath), "\"))
    End If

End Function

Example of how to use, let's try looking for Excel:

Sub ExampleUse()

Dim x As String

x = "EXCEL.EXE"

Debug.Print GetInstallDirectory(x)

End Sub

Output (on my machine anyway) is

C:\Program Files\Microsoft Office\Office14\

Answer 3

Assuming you are working on PC only and the people are working with their own copies and not a shared network copy. I would recommend the following.

1. Create a Sheet called 'Config', place the path with the exe in there, and then hide it.

2. Use use FileScriptingObject ('Tools' > 'References' > 'Microsoft Scripting Runtime') to see if the path in 'Config' exists

3. If it does not, ask the user for the location using a 'open file dialog box' and remember that in the 'Config' Sheet for next time.

The below code may help as a pointer.

Dim FSO As New FileSystemObject

Private Function GetFilePath() As String
Dim FlDlg           As FileDialog
Dim StrPath         As String
Set FlDlg = Application.FileDialog(msoFileDialogOpen)
    With FlDlg
        .Filters.Clear
        .Filters.Add "Executable Files", "*.exe"
        .AllowMultiSelect = False
        .ButtonName = "Select"
        .Title = "Select the executable"
        .Show
        If .SelectedItems.Count <> 0 Then GetFilePath = .SelectedItems(1)
    End With
Set FlDlg = Nothing
End Function

Private Function FileExists(ByVal StrPath As String) As Boolean
FileExists = FSO.FileExists(StrPath)
End Function

Answer 4

Here is another, much faster method, for comparison the "SierraOscar" solution took me 20 seconds, and the next solution took less than a second.

Function GetInstallFullPath(AppName) As String
    GetInstallFullPath = CreateObject("WScript.Shell").Exec("cmd.exe /c where " & AppName).StdOut.ReadAll
End Function

use like this:

    AppName = "gswin64c"
    AppPath = GetInstallFullPath (AppName)