1

I've been following articles and questions about converting VBA to VBScript but I'm now stuck. The following code still works in VBA (if I remove the Sub routine call) but it won't run as a script.

The code opens a connection to SQL Server to check a table to see if the process has already run today and loads the result into a Recordset. If the field is set to No then it opens up an Excel workbook and runs a macro. It works in VBA but when I run the same code as a script nothing happens (no errors either).

Can you see what the problem is? Thanks very much.

NB. There are two lines for cmd.CommandText. The commented out line is designed to always return No for testing purposes only. 

' Author Steve Wolstencroft
' Inititates the Automated Excel Refresh Procedure
Option Explicit

Pivot_Refresh

Public Function ConnectToSQLDwarfP()
    On Error Resume Next
    ConnectToSQLDwarfP = "Driver={SQL Server Native Client 10.0};Server=DwarfP;Database=DwarfPortable;Trusted_Connection=yes;"
End Function

Public Sub Pivot_Refresh()
    On Error Resume Next

    Dim cnx
    Dim Rst

    Set cnx = New ADODB.Connection
        cnx.ConnectionString = ConnectToSQLDwarfP
        cnx.Open

    Dim cmd

    Set cmd = New ADODB.Command
        cmd.ActiveConnection = cnx
        cmd.CommandType = adCmdText
        cmd.CommandText = "Select Case When max(DwarfPortable.dbo.fn_GetJustDate(pl.StartDateTime)) = DwarfPortable.dbo.fn_GetJustDate(getDate()) Then 'Y'  Else 'N' End as RunToday From ProcessControl.dbo.ProcessLog pl Where pl.ProcessName = 'Excel_Auto_Refresh'"
        'cmd.CommandText = "Select Case When max(pl.StartDateTime) = DwarfPortable.dbo.fn_GetJustDate(getDate()) Then 'Y' Else 'N' End as RunToday From ProcessControl.dbo.ProcessLog pl Where pl.ProcessName = 'Excel_Auto_Refresh'"

    Set Rst = cmd.Execute

    Dim objXL, objBook
    Set objXL = CreateObject("Excel.Application")

    If Rst.Fields("RunToday") = "N" Then
        Set objBook = objXL.Workbooks.Open("\\nch\dfs\SharedArea\HI\Clinical-Informatics\InfoRequestOutputs\Regular-Jobs\Pivot-Refresh\Pivot-Refresh-Control.xls", 0, True)
        objXL.Application.Visible = True

        objXL.Application.Run "'Pivot-Refresh-Control.xls'!Auto_Refresh"

        objXL.ActiveWindow.Close
        objXL.Quit

        Set objBook = Nothing
        Set objXL = Nothing
    End If

End Sub
GSerg
  • 76,472
  • 17
  • 159
  • 346
SliderSteve
  • 165
  • 1
  • 1
  • 8
  • Thanks GSerg. That's great. It's now working, I didn't realise CreateObject should also be used with ADODb - makes sense. – SliderSteve Sep 01 '16 at 18:11

1 Answers1

5

You can't instantiate external objects in VBScript with e.g. New ADODB.Connection because there are no references to external libraries.

You can't use constants like adCmdText either. They will be treated as undefined empty variables.

You don't get any errors because you shut them up with On Error Resume Next. Remove that and you will get your errors.

Make sure all external object instantiation is done with CreateObject like you are doing for Excel, and replace all external constants with their literal values.

Community
  • 1
  • 1
GSerg
  • 76,472
  • 17
  • 159
  • 346