excel - Excel VBA 类型不匹配 13 使用 ActiveWorkbook.Close

Question

我在打开文件的主工作簿中运行代码；执行数据刷新；然后我想保存文件。一切正常，直到我尝试使用ActiveWorkbook.Close True. 我收到“运行时错误 13 类型不匹配错误”。在调试消息上单击“结束”后...文件按我想要的方式关闭。我On Error Resume Next在前面使用了也没有解决错误的行。

我正在打开的工作簿是活动工作簿，并且仍然处于打开状态。我也试过使用workbooks("").close true没有运气。

以下是我的代码：

Option Explicit
Option Private Module


'CONSTANTS
Const sTEMP_PATH_NAME As Variant = "C:\Users\ta7464\Vivial\Budgets - Documents\2018 Forecast F8\AdminOnly\"
Const sTEMPLATE_WKBK As Variant = "BlankTemplate_V2.xlsm"



'VARIABLES


Public Sub DeployNewTemplates()

'1.  Turn off features
    'TurnOffFunctionality

'2.  Open the spreadsheet and activate Sheet2
    DeployTemplates

'3. Turn on features
    'TurnOnFunctionality

End Sub


Private Sub DeployTemplates()
    Dim sMainWkbk As Variant, sBlnkTemp As Variant, rDeptCds As Variant, DeptArr As Variant
    Dim i As Long, sDeptNo As String, sDeptNm As String, sWBName As Variant, sWBFullName As Variant
    Dim wbTemplate As Workbook, wbTarget As Workbook


    'Load array with list of valid department codes
    Set rDeptCds = shDepts.Range("A1").CurrentRegion
    DeptArr = rDeptCds.Value

    'Assign variable for the TemplateMgmtTool workbook
    sMainWkbk = Left(ThisWorkbook.Name, (InStrRev(ThisWorkbook.Name, ".", -1, vbTextCompare) - 1))
    sBlnkTemp = Left(sTEMPLATE_WKBK, (InStrRev(sTEMPLATE_WKBK, ".", -1, vbTextCompare) - 1))

    'Opens the blank template for population and saving

    Set wbTemplate = Workbooks.Open(Filename:=sTEMP_PATH_NAME & sTEMPLATE_WKBK)

    'Loop through all department numbers with actuals in the current year to customize and save a template for each
    For i = LBound(DeptArr) + 1 To UBound(DeptArr)
        sDeptNo = DeptArr(i, 1)
        sDeptNm = DeptArr(i, 2)
        sWBFullName = sDeptNo & "-" & sDeptNm & "_BT.xlsm"
        sWBName = sDeptNo & "-" & sDeptNm & "_BT"

        '1.  Save a copy of the blank template with proper naming convention
         Workbooks(sTEMPLATE_WKBK).Activate
         wbTemplate.SaveCopyAs PathName & sWBFullName
         Set wbTarget = Workbooks.Open(Filename:=PathName & sWBFullName)

        '2. Populate the shPL tab with the active department number

        Workbooks(sWBFullName).Worksheets("P&L").Range("C1").Value = DeptArr(i, 1)

        '3 Refresh the template connections and wait until complete before saving and closing
         wbTarget.Connections("Query - DeptNo").Refresh
         wbTarget.Connections("Query - CoRollUp").Refresh
         wbTarget.Connections("Query - Current Year Actuals").Refresh
         wbTarget.Connections("Query - DeptCode").Refresh
         wbTarget.Connections("Query - Oracle Headcount").Refresh
         wbTarget.Connections("Query - ButtsInSeats").Refresh


        '4 Save and close the newly created template
        On Error Resume Next
        ActiveWorkbook.Close True


    Next i

End Sub

标签： excelvbatype-mismatch