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

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 & """

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

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

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?

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

Select allOpen in new window

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?

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?

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!

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.

Thanks!