Solved

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

Posted on 2010-09-13
12
1,712 Views
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?
0
Comment
Question by:glennes
  • 5
  • 4
  • 2
  • +1
12 Comments
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
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 & """

0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
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
0
 
LVL 13

Expert Comment

by:MWGainesJR
Comment Utility
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
0
 

Author Comment

by:glennes
Comment Utility
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?
0
 
LVL 13

Accepted Solution

by:
MWGainesJR earned 250 total points
Comment Utility
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
 
0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
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

0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 33

Expert Comment

by:Norie
Comment Utility
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?
0
 

Author Comment

by:glennes
Comment Utility
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?
0
 
LVL 65

Assisted Solution

by:rockiroads
rockiroads earned 250 total points
Comment Utility
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)

0
 

Author Comment

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




0
 

Author Comment

by:glennes
Comment Utility
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.
0
 

Author Closing Comment

by:glennes
Comment Utility
Thanks!
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I come…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

744 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

19 Experts available now in Live!

Get 1:1 Help Now