使用 Access 2010 VBA 格式化 Excel。我的代码第一次工作。

Format Excel using Access 2010 VBA. My code works the first time.

提问by DeeDee

I'm using Access 2010 VBA to create an Excel worksheet with cols A thru M and an unknown number of rows. My formatting is simple, changing font, adding borders, col widths, printing options, etc. It runs the first time but if I try it again it returns the following error: "Err = 91 Object variable or With block variable not set". My confusion is that where it errors works the first time it's run but then it won't work anymore. Here is my code:

我正在使用 Access 2010 VBA 创建一个 Excel 工作表，其中 cols A 到 M 和未知行数。我的格式很简单，更改字体、添加边框、列宽、打印选项等。它第一次运行，但如果我再试一次，它会返回以下错误：“Err = 91 对象变量或块变量未设置”。我的困惑是它第一次运行时出错的地方，但后来它不再起作用了。这是我的代码：

`Option Compare Database
'Option Explicit

'Dim objXLApp         As Excel.Application
'Dim objXLBook        As Excel.Workbook
'Dim objXLSheet       As Excel.Worksheet
'Dim objXLRange       As Object
'Dim xlLastRow        As Long
'Dim xlCell           As String

`Public Sub FormatTaskCalendar(fileIn As String, sheetIn As String)
 On Error GoTo Err_FormatTaskCalendar

        'open the Excel spreadsheet with the exported data
Set objXLApp = CreateObject("Excel.Application")
        'for testing, make the applicaiton visible
objXLApp.Visible = True
Set objXLBook = objXLApp.Workbooks.Open(fileIn)
        'make the "Orders" worksheet to be the active worksheet
Set objXLSheet = objXLBook.Sheets(sheetIn)
        'find the last used cell in Column "A" (Center)
xlLastRow = objXLSheet.Range("A65536").End(xlUp).Row

        'show the cell borders for entire sheet
With objXLSheet.Range("A1:M" & xlLastRow).Borders
    .LineStyle = xlContinuous
    .Weight = xlThin
End With

With objXLSheet.Range("B1:M" & xlLastRow).Font
    .Name = "Verdana"
    .Size = 11
End With

        'select first row and apply formating
objXLSheet.Activate
objXLSheet.Range("A1:M1").Select
With Selection.Interior
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
    .Color = 5287936
    .TintAndShade = 0
    .PatternTintAndShade = 0
End With
Selection.Font.Name = "Verdana"
Selection.Font.Size = 13
Selection.Font.Bold = True
Selection.HorizontalAlignment = xlCenter
With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlBottom
    .WrapText = True
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
End With

    'freeze the pane so the header row doesn't scroll
objXLSheet.Activate
objXLSheet.Range("A2", "A2").Select
objXLApp.ActiveWindow.FreezePanes = True

'autofit the columns
objXLSheet.Activate
objXLSheet.Range("A1", "M1").Select
objXLSheet.Cells.EntireColumn.AutoFit
Columns("B:B").ColumnWidth = 15.5           'Neovia Team Member
Columns("D:D").ColumnWidth = 26.5           'Client Name
Columns("E:E").ColumnWidth = 13             'Renewal Date
Columns("F:F").ColumnWidth = 14.44          'Stage
Columns("G:G").ColumnWidth = 12.7           'Channel
Columns("H:H").ColumnWidth = 36.5           'Task Name
Columns("I:I").ColumnWidth = 12.3           'Task Due Date
Columns("J:J").ColumnWidth = 16             'Task Completion Date
Columns("K:K").ColumnWidth = 14.11          'Days To Complete
Columns("L:L").ColumnWidth = 10.56          'Actual Work Hours
Columns("M:M").ColumnWidth = 15.3           'Comments

        ''do some settings for the page layout when printing
objXLApp.PrintCommunication = True
With objXLSheet.PageSetup
    .Orientation = xlLandscape
    .PrintTitleRows = ":"
    .PrintTitleColumns = ""
    .LeftHeader = "&""Verdana,Bold""&13page &P of &N"
    .CenterHeader = "&""Verdana,Bold""&13Task Calendar"
    .RightHeader = "&""Verdana,Bold""&13&D&T"
    .LeftFooter = ""
    .CenterFooter = ""
    .RightFooter = ""

    .LeftMargin = objXLApp.InchesToPoints(0.25)
    .RightMargin = objXLApp.InchesToPoints(0.25)
    .TopMargin = objXLApp.InchesToPoints(0.75)
    .BottomMargin = objXLApp.InchesToPoints(0.5)
    .HeaderMargin = objXLApp.InchesToPoints(0.5)
    .FooterMargin = objXLApp.InchesToPoints(0.5)
    .Zoom = False
    .FitToPagesWide = 1
    .FitToPagesTall = False

End With
objXLApp.PrintCommunication = False

'save the changes
objXLBook.Save
objXLApp.Quit
Set objXLApp = Nothing

My code creates and error when it performs this line of code:

我的代码在执行这行代码时创建并出错：

          'select first row and apply formating
objXLSheet.Activate
objXLSheet.Range("A1:M1").Select
With Selection.Interior

I turned the visible property on so I can see it does select A1:M1 but I can't figure out why the next line only works once. This is my first time trying to format Excel from Access. Any help will be greatly appreciated.

我打开了可见属性，所以我可以看到它确实选择了 A1:M1，但我不明白为什么下一行只能工作一次。这是我第一次尝试从 Access 格式化 Excel。任何帮助将不胜感激。

回答by Siddharth Rout

Since you have your code commented, I am not sure if you are using Early Binding or Late Binding.

由于您对代码进行了注释，因此我不确定您使用的是早期绑定还是后期绑定。

However, Try this. Avoid the use of .Select, Selectionor .Activate

但是，试试这个。避免使用.Select,Selection或.Activate

Change the lines

改变线条

objXLSheet.Activate
objXLSheet.Range("A1:M1").Select
With Selection.Interior

to

到

With objXLSheet.Range("A1:M1").Interior

You might also want to see THISlink.

您可能还想查看此链接。