通过Access VBA格式化Excel-范围应用程序/对象定义错误

Question

我的问题发生在以下行：

Worksheets(1).Range("A:A").Select

背景：我有一个过程，加载一个文本文件，将其拆分成许多其他表，然后将其中一些部分合并为新表，导出到excel，并进行一些格式化。

我使用"For Each"循环来遍历我的访问表。当识别出某些表时，会运行一些其他代码来创建新表（代码未显示）。一旦创建了一个新的表格，它就会被导出到excel并格式化。这就是错误发生的地方。第一个循环运行良好，在第二个循环中，代码在选择列范围时出错。

我已经对此错误进行了一些搜索，原因似乎通常是工作表的引用方式。我有很多工具使用这种过程，所以最终了解原因会很好。

代码：

Dim xl                           As Excel.Application
Dim xlBook                       As Excel.Workbook
Dim xlSheet1                     As Excel.Worksheet
outputFileName = "C:UsersUserIDDesktopReports" & tblData & ".xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "tbl_Report",     outputFileName, True
Set xl = New Excel.Application
Set xlBook = xl.Workbooks.Open(outputFileName)
xl.Visible = True
Set xlSheet1 = xlBook.Worksheets(1)
With xlSheet1
Worksheets(1).Range("A:A").Select
Selection.Delete Shift:=xlToLeft
Worksheets(1).Range("1:1").Select
Selection.Delete Shift:=xlUp
Worksheets(1).Range("A1:J1").Interior.Color = RGB(191, 191, 191)
Worksheets(1).Range("A1:J1").Borders.Weight = xlThin
Worksheets(1).Range("A1:J100").Font.Name = "Calibri (Body)"
Worksheets(1).Range("A1:J100").Font.Size = 11
Worksheets(1).Range("A1:J1").HorizontalAlignment = xlCenter
Worksheets(1).Range("A12:F12").Interior.Color = RGB(191, 191, 191)
Worksheets(1).Range("A12:F12").Borders.Weight = xlThin
Worksheets(1).Range("A12:F12").HorizontalAlignment = xlCenter
Worksheets(1).Range("A1:J100").Cells.Columns.AutoFit
xl.DisplayAlerts = False
xl.ActiveWorkbook.Save
xl.ActiveWorkbook.Close
xl.DisplayAlerts = True
Set xlSheet1 = Nothing
Set xlBook = Nothing
Set xl = Nothing
End With

Answer 1

EDIT-@A.S.H.击败我…

Dim xl As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet1 As Excel.Worksheet
outputFileName = "C:UsersUserIDDesktopReports" & tblData & ".xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
                          "tbl_Report", outputFileName, True
Set xl = New Excel.Application
Set xlBook = xl.Workbooks.Open(outputFileName)
xl.Visible = True
Set xlSheet1 = xlBook.Worksheets(1)
With xlSheet1
    .Range("A:A").Delete Shift:=xlToLeft
    .Range("1:1").Delete Shift:=xlUp
    With .Range("A1:J1")
        .Interior.Color = RGB(191, 191, 191)
        .Borders.Weight = xlThin
        .HorizontalAlignment = xlCenter
    End with
    With .Range("A1:J100")
        .Font.Name = "Calibri (Body)"
        .Font.Size = 11
        .Cells.Columns.AutoFit
    End with
    With .Range("A12:F12")
        .Interior.Color = RGB(191, 191, 191)
        .Borders.Weight = xlThin
        .HorizontalAlignment = xlCenter
    End with

End With    
xlBook.Close True 'save changes
Set xlSheet1 = Nothing
Set xlBook = Nothing
Set xl = Nothing

Answer 2

1）尽可能避免使用select。它经常失败，尤其是当工作表未处于活动状态时。。

2） 正确使用With语句：

With xlSheet1
   .Columns("A").Delete Shift:=xlToLeft ' <~~~ you dont need select
   .Rows(1).Delete Shift:=xlUp ' <~~~ you dont need select
   .Range("A1:J1").Interior.Color = RGB(191, 191, 191)
   .Range("A1:J1").Borders.Weight = xlThin
   .Range("A1:J100").Font.Name = "Calibri (Body)"
   .Range("A1:J100").Font.Size = 11
   .Range("A1:J1").HorizontalAlignment = xlCenter
   .Range("A12:F12").Interior.Color = RGB(191, 191, 191)
   .Range("A12:F12").Borders.Weight = xlThin
   .Range("A12:F12").HorizontalAlignment = xlCenter
   .Range("A1:J100").Cells.Columns.AutoFit
End With