Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Excel VBA How to Run a Command Line with VBA

Posted on 2010-09-09
5
Medium Priority
?
2,268 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:Glenn Stearns
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 800 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 800 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 400 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:Glenn Stearns
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:Glenn Stearns
ID: 33645140
Thanks, guys!
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

610 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