Solved

Excel VBA How to Run a Command Line with VBA

Posted on 2010-09-09
5
1,778 Views
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).

0
Comment
Question by:glennes
5 Comments
 
LVL 85

Accepted Solution

by:
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




0
 
LVL 2

Assisted Solution

by:craigajhicks
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
0
 
LVL 10

Assisted Solution

by:SANTABABY
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

    Wend

    

    csResult = oExec.StdOut.ReadAll()

    csError = oExec.StdErr.ReadAll()

    Set oWsc = Nothing

    RunCmd = csResult

End Function

Open in new window

0
 

Author Comment

by:glennes
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!
0
 

Author Closing Comment

by:glennes
ID: 33645140
Thanks, guys!
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

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…
Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

762 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now