Solved

Excel VBA How to Run a Command Line with VBA

Posted on 2010-09-09
5
1,928 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 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: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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

728 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