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

我用 Access 2010 VBA 创建床单 Excel 从 cols A-M 和一个未知的行数。 我的格式简单：更改字体，添加边界，列宽度，打印参数等。它首次开始第一次，但如果我再次尝试，它将返回以下错误： "Err = 91 Object variable or With block variable not set". 我的混乱是，在他弄错的地方，第一次工作，但后来他不再工作。 这是我的代码：

`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 = "$1:$1"

 .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

当它执行此代码行时，我的代码会创建一个错误：

'select first row and apply formating

objXLSheet.Activate

objXLSheet.Range/"A1:M1"/.Select

With Selection.Interior

我打开了这个酒店 visible, 看它真正选择的东西 A1:M1, 但我无法理解为什么下一行只能运作一次。 这是我第一次尝试格式化 Excel 的 Access. 任何帮助都会非常感激。