excel - Access VBA 与 Excel 交互，第二次得到错误 91

Question

我在第二次运行时遇到了这个脚本的问题。
第一次运行脚本我没有问题，如果我关闭 Access 并重新运行脚本我也没有问题，但是当在 10 分钟内运行时，我得到错误 91“对象变量或未设置 block 变量”

该脚本似乎卡在第二次运行时格式化电子表格的行上。 (With Selection .Font.Bold = True)

脚本是:

Option Compare Database
Option Explicit

Public Function LookInduct()

On Error GoTo Induct_err

Dim ExcelApp As New excel.Application
Dim WorkBook As excel.WorkBook
Dim Linecount As Integer
Dim SaveDetails As String

SaveDetails = "C:\LookToInductReport.xlsx"

If Len(Dir(SaveDetails)) > 0 Then
    Kill SaveDetails
  End If

DoCmd.TransferSpreadsheet acExport, 10, "LookToInductReport_04", SaveDetails, True, "LookToInduct"

Set ExcelApp = CreateObject("excel.application")

ExcelApp.Visible = False
'------------------------------------------------------------
' Format the Excel File
'------------------------------------------------------------
Linecount = DCount("[NSN]", "LookToInductReport_04") + 1

Set WorkBook = ExcelApp.Workbooks.Open(SaveDetails, , False)

    WorkBook.Sheets("LookToInduct").Select
    With Selection

    ExcelApp.Range("A1:M1").Select
        With Selection
            .Font.Bold = True '<- error #91 here
            .WrapText = True
            .Interior.Pattern = xlSolid
            .Interior.Color = 16764057
            .VerticalAlignment = xlTop 
        End With

    End With

    WorkBook.Save
    WorkBook.Close
    ExcelApp.Close
    ExcelApp.Quit

    Set WorkBook = Nothing
    Set ExcelApp = Nothing


    MsgBox "Report saved"

    Exit Function


Induct_err:

    WorkBook.Save
    WorkBook.Close
    ExcelApp.Quit
    MsgBox Err.Number & vbCr & Err.Description
    Exit Function

End Function

Answer 1

不要嵌套一个With Selection ... End With在另一个 With Selection ... End WithSelection是 Excel 应用程序对象的属性，所以使用 With ExcelApp.Selection而不仅仅是 With Selection

'With Selection

ExcelApp.Range("A1:M1").Select
    'With Selection
    With ExcelApp.Selection
        .Font.Bold = True
        .WrapText = True
        .Interior.Pattern = xlSolid
        .Interior.Color = 16764057
        .VerticalAlignment = xlTop 
    End With

'End With