Tuesday, October 7, 2014

Executing an executable from VBA

For people in the software industries such as consultants, developers and even managers, they will very often need to handle different versions of software. Many a times, these versions are stored in different places. This means we will need to memorize these directories and every time we need to access them, we will need to navigate to these directories. The best way to overcome these is to tabulate the versions in Excel. But it is insufficient as it we are not able to start the software from the tables in excel natively.

By using VBA you can do this with the "Shell" command. The problem is with this is that you can only execute files that has no dependency on the folder you are executing it from. This means the environment paths and parameters needs to be setup correctly in Windows.

To overcome this limitation, I combined what I posted previously to build a table having the ability to execute a software from Excel. Instead of building your own DLL, what you need is to use the existing DLL (Refer to here for more details on how this is done in VBA):

Public Declare Function ShellExecute Lib "Shell32.dll" Alias "ShellExecuteA" _
  (ByVal hwnd As Long, _
   ByVal lpOperation As String, _
   ByVal lpFile As String, _
   ByVal lpParameters As String, _
   ByVal lpDirectory As String, _
   ByVal nShowCmd As Long) As Long

By declaring this you are effectively using the "ShellExecute" library from Shell32.dll to execute lpFile from lpDirectory.This will allow user to have more flexibility in building UI that will execute files upon clicking in VBA. An example of using the above is as follows:


RetVal = ShellExecute(0, "open", "C:\folder\client.cmd", "<arguments>", _
                        "C:\folder\", SW_SHOWMAXIMIZED)