Excel VBA error using WScript.Shell.Run

Question

After recently upgrading from Excel 2010 to Excel 2013, I moved a custom add-in (.xlam) to the new Application.LibraryPath directory (C:\Program Files\Microsoft Office 15\root\office15\Library\BTRTools). There is a bit of code that launches an executable (exe) file (located in sub directory of the add-in). However, since the upgrade/move, I am not getting an error:

PrettyPrintXml.exe - Application Error

The application was unable to start correctly (0xc000007b). Click OK to close the application.

I'm obviously pretty convinced it is file permissions. I have explicitly added myself permissions with full rights to the \Library folder (and all subs). Note that I think I had to do this even with Excel 2010 (folder at C:\Program Files (x86)\Microsoft Office\Office14\Library) to make things work.

However, after all this, I'm still stuck and can not launch the exe file. Any ideas/suggestions on how to make this work?

Code is pretty standard:

Public Sub RunShellExecute(sFile As String, Optional params As String = "", Optional wait As Boolean = False)

Dim wsh As Object: Set wsh = VBA.CreateObject("WScript.Shell")
Dim waitOnReturn As Boolean: waitOnReturn = wait
Dim windowStyle As Integer: windowStyle = 1
Dim exe As String: exe = IIf(Left(sFile, 1) <> """", """" & sFile & """", sFile)
Dim exeParams As String: exeParams = IIf(params <> "", " " & params, "")
Dim errorCode As Integer: errorCode = wsh.Run(exe & exeParams, windowStyle, waitOnReturn)

If errorCode = 0 Then
    '// MsgBox "Done! No error to report."
Else
    MsgBox "Program exited with error code " & errorCode & "."
End If

End Sub

Solution 1

I know your question is "Why doesn't this work", but I thought you might be interested in an alternate solution: There is a native VBA PrettyPrintXML. You need to add a reference to the MSXML library in your VBA project by clicking "Tools" ---> "References..." and then check the box next to Microsoft XML, v6.0 (or whatever version is included with your version of Office/Windows).

Solution 2

Please change the title of your question, because Excel VBA is able to use WScript.Shell.Run, otherwise you wouldn't be getting your error.

As for the actual issue, this looks like a 32-bit / 64-bit problem. Investigate whether the program you're calling is appropriate for your system and whether it tries to load the right DLLs.

The problem is not file permissions, then you would get a different status code.

Comments

[Terry]

As I said above, I need the addin in Excel's 'library' path, so I can't change it to your suggested path.

[Terry]

I'm confused what you mean with "32-bit / 64-bit problem"...as I mentioned earlier, if I put the entire folder (with my *.xlam and dependent files) in another path, everything works. However, this isn't possible because of other implications with Excel, most notably, if your *.xlam isn't in their 'Library' folder, Excel will break the link to your add-in every time you open the file on a different machine. If all users of the add-in install it under /Library, even if paths don't match between users sharing the file, the link stays intact. What 32/64 bit thing do you suggest I check/confirm?