0

I'm writing a macro that needs to:

  1. Start .bat file
  2. Wait until .bat file is finished (data is downloaded from Oracle to .csv via SQLcl)
  3. Continue executing rest of VBA

I found two solutions for that problem:

First one - via Shell:

Dim shell_command As String
shell_command = ".bat_file_location"
Call Shell(shell_command, vbNormalFocus)

which opens the .bat file but the window immediately closes and nothing is executed.

Second one - via Windows Script Host Object:

Dim strCommand As String
Dim lngErrorCode As Long
Dim wsh As WshShell
Set wsh = New WshShell

strCommand = Chr(34) & _
             ".bat_file_location" & _
             Chr(34)
lngErrorCode = wsh.Run(strCommand, _
                       WindowStyle:=0, _
                       waitonreturn:=True)
If lngErrorCode <> 0 Then
    MsgBox "Uh oh! Something went wrong with .bat file!"
    Exit Sub
End If
Set wsh = Nothing

this option does not work (lngErrorCode=0) and I don't quite know why. I use the same method for other .bat files which download data via BTEQ from Teradata database, so I'm assuming that SQLcl/Oracle database doesn't want to work with that method.

Do you have any ideas how to overcome this?

.bat file code:

sql user/password@//hostname:port/servicename @"path\code.sql"

Any advices will be much appreciated. Regards.

thatjeffsmith
  • 20,522
  • 6
  • 37
  • 120
Krzysztof Zurek
  • 1
  • 2
  • 1
    first thing to check, does sqlcl run interactively? i'm wondering if it can't fine a java home – thatjeffsmith Mar 13 '20 at 14:49
  • This has a `batch-file` tag, but there doesn't seem to be any cmd script to go with it. From the description, it sounds like the script or the interpreter might be starting, but there's no available diagnostics. Perhaps you should place some strategic `pause` commands in the script? See `help pause`. – jwdonahue Mar 14 '20 at 05:36

0 Answers0