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


Excel VBA - Get User Input and Save As Variable for Use in Shell cmdLine = Statement

Posted on 2010-09-13
Medium Priority
Last Modified: 2012-05-10
I have an open spreadsheet with a click control that executes this macro:

Private Sub CommandButton1_Click()
cmdLine = """c:\Program Files\ProgramName.exe"" -a""C:\script.SQL""
Shell cmdLine, 1
ThisWorkbook.Close False
End Sub

I want to pop up a user input box that asks for a variable named end_date,  - something like "Please enter the ending date:" - then use that variable in the shell command in the above script - something like this:

Private Sub CommandButton1_Click()
cmdLine = """c:\Program Files\ProgramName.exe"" -a""C:\script.SQL" & end_date & """
Shell cmdLine, 1
This Workbook.Close False
End Sub

The variable end_date is passed to the SQL script "C:\script.SQL" (an Oracle PL/SQL script) when Shell cmdLine,1 runs and the Oracle SQL script will use it as a position variable '&1' when the SQL script runs.

(For those familiar with Oracle SQL, this approach is needed to replace an ACCEPT/PROMPT command in SQL which does the same thing - only I need to do it in the Excel VBA script instead of in the Oracle SQL script.)

 I need to know how the VBA code would look to make it ask the user for the variable end_date, then save the input as a variable named end_date so that the shell command will capture end_date as entered by the user, and save it long enough for the Shell command to pass that user input as a variable named end_date to the SQL script as part of the syntax of the cmdLine statement.

How would I modify my VBA script to do this?
Question by:Glenn Stearns
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
  • 4
  • 2
  • +1
LVL 65

Expert Comment

ID: 33665331
you could either have a textbox on your form or prompt using inputbox

if a textbox on the form then just use that (say it was called txtEndDate)

cmdLine = """c:\Program Files\ProgramName.exe"" -a""C:\script.SQL" & Me.txtEndDate & """

LVL 65

Expert Comment

ID: 33665347
obviously you need to add validation and ensure the date entry is in the format the program expects

the other way

dim sEndDate

sEndDate = inputbox("Enter a date")

cmdLine = """c:\Program Files\ProgramName.exe"" -a""C:\script.SQL" & sEndDate & """

what format date are you expecting? thats the next bit, the validation - ensure entry added and in the right format
LVL 13

Expert Comment

ID: 33665422
adding to rockiroads post, you could use the format function to make sure the user input is correct:
Private Sub CommandButton1_Click()
dim sEndDate
sEndDate = format(inputbox("Enter a date"),"mm/dd/yyyy")
cmdLine = """c:\Program Files\ProgramName.exe"" -a""C:\script.SQL" & sEndDate & """
Shell cmdLine, 1
This Workbook.Close False
End Sub
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

by:Glenn Stearns
ID: 33672392
VBA accepts the MWGaineJR script as syntactically correct.

However, when it runs, and after the user enters the date in the input box (in this case as 2010/09/12) and control passes back to the shelled-out program that runs the SQL script, I get a message from that program that says:

Cannot open file "G:\Golden32401kScriptAE.sql2010\09\12". The system cannot find the file specified.

It looks like the VBA syntax is appending the date the user input into the filename of the .sql script file rather than using it as input to the SQL file's date variable, (which is '&1' in the SQL script) and then trying to find a SQL script file named G:\Golden32401kScriptAE.sql2010\09\12, which, of course, does not exist. Notice, too, that the date in the error message is using a '\' between the year, month, and day instead of a '/', which is how the date was entered in the input box.

This is how the command looks in my VBA script incrporating the '& sEndDate &' variable name:

Private Sub CommandButton1_Click()
Dim sEndDate
sEndDate = Format(InputBox("Enter Payroll Week Ending Date"), "yyyy/mm/dd")
cmdLine = """E:\Program Files\Benthic\Golden6.exe"" -a""G:\Golden32401kScriptAE.sql" & sEndDate & """ -x -s -m -unameduser@PROD -puserpassword "
Shell cmdLine, 1
ThisWorkbook.Close False
End Sub

Perhaps there's something wrong with where the ' " ' marks are, or how many there are, that's causing this, but I really don't know since the VBA compiler accepts the syntax as written.
For what it's worth, the -a, -x, -s, -u, and -p are switches that are recognized by Golden6.exe.
-a means open and run a SQL script, putting a " before and after the path/filename
-x means exit the script after it runs
-s means start a new instance of Golden6.exe
-m means run Golden6 minimized
-u means username
-p means user password

Any idea why this is happening?
LVL 13

Accepted Solution

MWGainesJR earned 1000 total points
ID: 33672527
How should the command look?  Where you put the variable is up to you.....
do you need to add a space between ".sql" and the date?
cmdLine = """E:\Program Files\Benthic\Golden6.exe"" -a""G:\Golden32401kScriptAE.sql " & sEndDate & " "" -x -s -m -unameduser@PROD -puserpassword "
The easier way to troubleshoot is to place all the text into a an msgbox command and make sure it look right before running the command.
As for the date having backslashes instead of forward....I have no clue....It works fine when I test it....must be something beyond the format function changing it....the shell maybe
LVL 65

Expert Comment

ID: 33673554
ideally you should give your button a meaningful name instead of command1

anyways, what I was talking about in terms of validation. are you expecting a valid value to be entered all the time? you should not think that

simple validation entered here. Reason why I enter as a date is to verify it is a valid date entered

Now when you buld the string you can use """ or you can use chr$(34). I prefer the latter as it makes it more readable for me

You wrap the filenames in quotes because of the possiblity of spaces
Any text argyments also wrapped and you can see I use chr$(34) for this

to confirm the command line generated look in the immediate window

I also used variables to hold your userid/password and environment - sample code uses fred/flintstone

Private Sub CommandButton1_Click()

    Dim sEndDate As String
    Dim sCmdLine As String
    Dim sUser As String
    Dim sPassword As String
    Dim sEnv As String
    sEndDate = InputBox("Enter Payroll Week Ending Date")
    If IsNull(sEndDate) Or Trim$(sEndDate) = "" Then
        MsgBox "No Date Entered"
        Exit Sub
    End If
    If Not IsDate(sEndDate) Then
        MsgBox "Valid Date Not Entered"
        Exit Sub
    End If
    sUser = "fred"
    sPassword = "flintstone"
    sEnv = "PROD"
    sCmdLine = Chr$(34) & "E:\Program Files\Benthic\Golden6.exe" & Chr$(34) & " -a " & Chr$(34) & "G:\Golden32401kScriptAE.sql" & Chr$(34) & " " & _
            Chr$(34) & Format(sEndDate, "YYYY/MM/DD") & Chr$(34) & _
            " -x -s -m -u" & sUser & "@" & sEnv & " -p" & sPassword
    Debug.Print sCmdLine
    Shell cmdLine, vbMaximizedFocus
End Sub

Open in new window

LVL 35

Expert Comment

ID: 33674072
Why do you need to get the input in Excel when it appears the other application/database is asking for it?

Have you checked what the syntax is for using parameters in the database?

eg what would the SQL code look like that calls the script?

Author Comment

by:Glenn Stearns
ID: 33675384
That works, rockiroads...

I still cannot get the VBA cmdLine statement to pass the date variable to the sql script being executed by the program being shelled out to. That program (the 'Golden6' in the cmdLine) runs SQL*PLUS commands just fine, but it doesn't seem to know what to do with the date variable - still wants to append it to the filename.sql (like filename.sql2010/09/12). It could be that Golden6 is not seeing the variable as a substitution variable in the same way it does the same variable if I create it inside the sql script with the ACCEPT command. Could be that there's another -switch that needs to go in front of the variable 'Format(sEndDate, "YYYY/MM/DD")'. I've sent a support message to them to see if that's what it is.  If it's not that, can you think of anything else it might be?
LVL 65

Assisted Solution

rockiroads earned 1000 total points
ID: 33675505
what does Golden6 do
does -a produce an output or expect input

if you want it as part of a filename then you cannot use /

change to like this if you want the date

    sCmdLine = Chr$(34) & "E:\Program Files\Benthic\Golden6.exe" & Chr$(34) & " -a " & _
            Chr$(34) & "G:\Golden32401kScriptAE" & Format(sEndDate, "YYYY-MM-DD") & ".sql" & Chr$(34) & " " & _
            " -x -s -m -u" & sUser & "@" & sEnv & " -p" & sPassword

this should produce

Golden6.exe -a G:\Golden23401kScriptAE2010-09-14.sql -x -s m .... etc

if you do not want a dash then change the format date mask (remove dashes)


Author Comment

by:Glenn Stearns
ID: 33681032
rockiroads (and other EE experts)...

Golden6 is a program that connects to and logs in to the Oracle server. The first part of the Shell command line is the path to the Golden6 executable file. The second part calls the named .sql file containing the SQL script. It is followed by the -u and -p switches precede the Oracle username and password so that, when Golden 6 launches, it can automatically connect to and log in to the Oracle server. (The other -switches affect how Golden6 behaves while it is running the script.)  When Golden6 connects to the Oracle server, it then will execute a SQL script containing both SQL*PLUS-only commands (such as ACCEPT) and PL/SQL commands all within the same SQL script, in this case, the script is Golden32401kScriptAE.sql, and then run the entire SQL script. When execution completes, Golden6 closes, returns control back to the VBA script,  and returns the SQL result set to Excel formatted as an Excel worksheet. The date '2010-09-14' is not part of the name of the Golden32401kScriptAE.sql file and should not be appended onto the SQL script filename the way the VBA shell command is doing it., e.g.,  'Golden32401kScriptAE.sql2010-09-14'.  Too, the default format in the Oracle database for a date is YYYY/MM/DD. It does not know what to do with a date when it is formatted YYYY-MM-DD, though, if VBA cannot handle the '/' separator, I'm sure I could find a way within the SQL script to change the ' - ' in the VBA script to a ' / ' in the SQL script.

In the SQL script as modified to use the variable from the Excel input box, the ACCEPT...PROMPT line is deleted from the script, since user input is not being captured within the SQL script, but is being passed to the SQL script in the Excel Shell command. The '2010-09-14' part is the date variable I want passed to Golden6 (as '&1') as part of the Shell command after it is captured by the Excel input box, so that the script can then use that variable in SQL command lines that need it. In this example, '&1' is used in a line from the SQL script where '&1' should be replaced with the date captured by the Excel input box:

to_char(to_date('&1','YYYY/MM/DD) -7, 'YYYY/MM/DD'))

After doing some more research yesterday, both on EE and on other Web sites, it seems like this date variable known in the SQL script as '&1' should be declared in the VBA script and equated with '&1'. It also seems like the '&1' variable name should be declared in the SQL script since, somehow, the SQL script has got to know what '&1' is in order  to treat it as a variable that gets replaced with the date as input in the VBA script input box and passed to the SQL script in the same way that the SQL script would do it if the ACCEPT statement were being used in the SQL script to capture user input. I do not know enough about Oracle SQL to determine if this is some kind of bind variable method or some other kind of variable-identification method within the Oracle SQL command syntax, nor do I know how, exactly, how to declare it in VBA or in SQL so that when it is captured in VBA, it gets passed to the Oracle SQL script in such a way that the SQL script knows that it should replace every occurrence of '&1' in the SQL script with the date captured in the VBA script just as the SQL script would do if user input were being captured from the SQL script with the ACCEPT command.

Much of what I read indicated this can be done, but was less than clear on how to do it in the specific way I need it done; however, I hope that my explanation above is clear enough for you to understand what I'm trying to do.  Any suggestions you and the other experts might have on how, exactly, I can accomplish this will be most appreciated!


Author Comment

by:Glenn Stearns
ID: 33841170
Thanks, all...
I got the problem fixed.
The problem had to do with Golden6 instead of Excel.
Golden6 does not support passing the variable in the cmdLine command in Excel.
I used SQL*XL instead and it does what I needed to do.

Author Closing Comment

by:Glenn Stearns
ID: 33841232

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone 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

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 a serious pitfall that can happen when deleting shapes using VBA.
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 how to use longer labels with horizontal bar charts instead of the vertical column chart.

597 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