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

Xl

0xc000007b is a STATUS_INVALID_IMAGE_FORMAT error. It may be a 32- vs. 64-bit dll problem rather than a permissions problem. – Decorate

@NateHekman Not 100% sure of that. If I move my entire add-in directory to the old location (..Office14\Library) everything runs. The problem with doing this is then 'automatic link updating' doesn't work b/c Excel only looks for add-ins in the LibraryPath and UserLibraryPath directories. You think it might be because even though my Excel install is 32-bit, it made its LibraryPath directory the c:\Program Files\ instead of c:\Program Files (x86)? I'm pretty sure I tried the UserLibraryPath and got same result, but I'll check that out today. – Xl

Answer 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).

Lumbye

Answer 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.

Entirety

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? – Xl

Answer 3

You should use a path without spaces in it, something simple like 'C:\BTRTools'. Then it should work.

Trouveur

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