将VBA转换为VBScript-不起作用，但没有错误 [英] Converting VBA to VBScript - Not working but no error

问题描述

我一直在关注有关将VBA转换为VBScript的文章和问题，但现在陷入困境。以下代码在VBA中仍然有效（如果我删除了Sub例程调用），但它不会作为脚本运行。

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.

该代码打开了与SQL Server的连接检查表以查看该进程是否今天已经运行并将结果加载到Recordset中。如果该字段设置为否，则它将打开一个Excel工作簿并运行一个宏。它可以在VBA中运行，但是当我运行与脚本相同的代码时，什么也没发生（也没有错误）。

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。 cmd.CommandText 有两行。注释掉的行旨在始终返回否仅用于测试目的。

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

推荐答案

您不能使用例如VBScript实例化外部对象新的ADODB.Connection ，因为没有对外部库的引用。

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

您也不能使用诸如 adCmdText 之类的常量。它们将被视为未定义的空变量。

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

您不会得到任何错误，因为您使用 On Error Resume Next关闭了它们。删除它，您将得到错误。

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

请确保所有外部对象实例化都通过 CreateObject 完成为Excel做，并将所有外部常量替换为其文字值。

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.

这篇关于将VBA转换为VBScript-不起作用，但没有错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！