• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2650
  • Last Modified:

Excel VBA How to Run a Command Line with VBA

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
Glenn Stearns
Asked:
Glenn Stearns
3 Solutions
 
Rory ArchibaldCommented:
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
 
craigajhicksCommented:
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
 
SANTABABYCommented:
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
 
Glenn StearnsAnalystAuthor Commented:
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
 
Glenn StearnsAnalystAuthor Commented:
Thanks, guys!
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Tackle projects and never again get stuck behind a technical roadblock.
Join Now