Search code examples
excelvbahost-object

Passing SQLPlus script location using Windows Script Host Object Model in VBA

Ok,

So I haz lots of scripts that are generated by a team in my organisation which need to then be processed via SQLPlus

Basically we/me get loads of emails with the script location @Z:/aFolder/aScript.sql;

We then have to process them individually by opening up SQLPlus and pasting the file location in there "@Z:/aFolder/aScript.sql;"

I have been looking into a way to automate this via either c# or VBA

I think that the Windows Script Host Object Model option via VBA is a good one

Example: 

Option Explicit 

Sub SQLPlus(strFilePath) 

    Dim WShell As New WshShell 
    WShell.Run "sqlplus username/[email protected] " & strFilePath 

End Sub 

Sub test() 

    Call SQLPlus("@Z:/aFolder/aScript.sql;") 

End Sub

Only issue is that I get an error from passing that last file location in there: "SP2-0310: unable to open file location "Z:/aFolder/aScript.sql;"

Am I missing a special character or something from this?

I pass the @ sign into the string but its not recognised on the command line??

Any input greatly appreciated, thanks.

Solution

  • Ok, this is now resolved [in a fashion]

    What I'm doing is passing the file location parameter to a .bat file

    So I have my .bat file OracleSQL.bat which contains this code: 

    @echo off 
sqlplus username/[email protected] @%1 
exit;

    I can now shell out to the batch file with the file parameter: 

    Dim strBatchName As String 

strBatchName = "C:\location\of\bat\OracleSQL.bat Z:/aFolder/aScript.sql" 
Shell strBatchName

    Done :)