Excel VBA How to Run a Command Line with VBA

Posted on 2010-09-09
Last Modified: 2012-05-10
I have the following command line attached to a desktop shortcut icon (in the 'Target' field of the shortcut properties) so that when I click on the shortcut icon, the command line gets executed. The command line below is shown exactly the way it appears in the 'Target' field of the shortcut properties window (double-quote marks included):

"C:\Program Files (x86)\Benthic\Golden6.exe" -unameduser@PROD -puserpassword -a"G:\Golden32401kScriptAE.sql" -x -s"

What I want to do is create a named Excel workbook (.xlsx file), and place a button control on Sheet1 in it. Then, I will attach the VBA script to that button so that when the user clicks on the button, Excel will run the VBA script which will execute this command line. Once the VBA script has executed the command line, then I need this workbook to close (which it should do without any prompting since running the script associated with the control button makes no changes to this workbook. If this is not the case, then the VBA code will need to force-close the workbook when the VBA script completes execution).

The command line itself runs a SQL script using a SQL tool called Golden6. This command line executes the SQL script, which creates and names an Excel worksheet (.xlsx file), and exports to it the SQL result set, formatted for Excel. When the SQL script finishes execution in Golden6, it closes the Golden6 tool and all that remains on the screen is the named open Excel sheet with the query results in it. From that point, the user proceeds to use the opened spreadsheet for further processing in the same way the user would do if the user had entered all the data into the open worksheet manually.

In short, then, I need the VBA code (macro) that I can attach to a control button on the sheet which, when clicked on, will run the VBA script containing the command line above, and, when the VBA script has been executed, the workbook containing the sheet with the control button on it will close.

I am running Excel 2010 (64-bit) on a machine with Windows 7 Enterprise (64-bit) as the OS. However, the user will be running the script on a 32-bit machine using Excel 2007 with Windows XP Pro as the OS (if this matters).

Question by:glennes
LVL 85

Accepted Solution

Rory Archibald earned 200 total points
ID: 33637719
Presumably the target path will be different for the user then, so something like:

Shell """C:\Program Files\Benthic\Golden6.exe"" -unameduser@PROD -puserpassword -a""G:\Golden32401kScriptAE.sql"" -x -s", vbnormalfocus
ThisWorkbook.Close False


Assisted Solution

craigajhicks earned 200 total points
ID: 33638366
You might need the cmd /c switch in there like:

Shell "cmd /c ""C:\Program Files\Benthic\Golden6.exe"" -unameduser@PROD -puserpassword -a""G:\Golden32401kScriptAE.sql"" -x -s", vbnormalfocus
LVL 10

Assisted Solution

SANTABABY earned 100 total points
ID: 33639569
Another alternative: Use a function
See the function RunCmd below
Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Function RunCmd(CmdLine As String) As String
    Dim csResult As String
    Dim csError As String
    Dim oWsc As Object
    Dim oExec As Object
    Set oWsc = CreateObject("WScript.Shell")
    Set oExec = oWsc.Exec(CmdLine)
    While oExec.Status <> 1 ' Wait for process
        Sleep 1000
    csResult = oExec.StdOut.ReadAll()
    csError = oExec.StdErr.ReadAll()
    Set oWsc = Nothing
    RunCmd = csResult
End Function

Open in new window


Author Comment

ID: 33645135
Actually, after doing some more research on the SHELL command suggested by rorya and craigajhicks, and doing some trial and error, I've found that the following VBA sub does just what I need:

Private Sub CommandButton1_Click()
cmdLine = """C:\Program Files\Benthic\Golden6.exe"" -a""G:\Golden32401kScriptAE.sql"" -x -s -m -unameduser@PROD -puserpassword "
Shell cmdLine, 1
ThisWorkbook.Close False
End Sub

When I tried running only the SHELL commands as posted by rorya and craigajhicks, I discovered that they will run, but they will only execute 'cmd' and not the actual 'Golden6.exe' part of the command line.  By including 'cmdLine =' before the execution string, and then following it with 'Shell cmdLine, 1', the command will execute 'cmd' and then execute the 'Golden6.exe' command with all the switches following it.  In the 'Shell cmdLine, 1' line, the '1' means the same thing as 'vbnormalfocus', so it is not necessary to include 'vbnormalfocus' in the cmdLine text.

I did not try to run the RunCmd function posted by SANTABABY, but I will keep it in case I should need to create a function for this task at some future date.

Thanks to all of you for your great help!

Author Closing Comment

ID: 33645140
Thanks, guys!

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question