G
Gina
This is from Charles (Co-Worker)
I am creating an instance of excel after exporting a report from Access and
formatting it. The code runs through fine with the first report but errors
out on the next report. The problem is with the line
Rows("2:2").Select
With ActiveWorkbook
xlApp.ActiveWindow.FreezePanes = True
End With
I get an “Object variable or with block variable not set error.â€
Excel:
Set xlApp = CreateObject("Excel.Application") 'Create an instance of Excel
xlApp.Visible = False ' Make it invisible to the user
With xlApp
Workbooks.Open FileName:=strFileName, ReadOnly:=False 'Open the workbook
previously exported from Access
Rows("1:1").Select ' Select the header row and format
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
.Font.Bold = True
End With
Rows("2:2").Select
With ActiveWorkbook
xlApp.ActiveWindow.FreezePanes = True ' Freeze the row for scrolling
End With
Columns("D
").Select
Selection.NumberFormat = "m/d/yy;@" ' Format cloumn as date
Columns("E:E").Select
Selection.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($*
""-""??_);_(@_)" ' Format the column as Accounting with $ sign
Cells.Select
Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(5), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True 'Sub total
the entire worksheet by the change in customer number
Cells.EntireColumn.AutoFit ' Auto the entire sheet
Range("A1").Select
ActiveWorkbook.Save 'Save the workbook
End With
xlApp.Quit 'Close Excel
Set xlApp = Nothing 'Release the instance of Excel
I am creating an instance of excel after exporting a report from Access and
formatting it. The code runs through fine with the first report but errors
out on the next report. The problem is with the line
Rows("2:2").Select
With ActiveWorkbook
xlApp.ActiveWindow.FreezePanes = True
End With
I get an “Object variable or with block variable not set error.â€
Excel:
Set xlApp = CreateObject("Excel.Application") 'Create an instance of Excel
xlApp.Visible = False ' Make it invisible to the user
With xlApp
Workbooks.Open FileName:=strFileName, ReadOnly:=False 'Open the workbook
previously exported from Access
Rows("1:1").Select ' Select the header row and format
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
.Font.Bold = True
End With
Rows("2:2").Select
With ActiveWorkbook
xlApp.ActiveWindow.FreezePanes = True ' Freeze the row for scrolling
End With
Columns("D
Selection.NumberFormat = "m/d/yy;@" ' Format cloumn as date
Columns("E:E").Select
Selection.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($*
""-""??_);_(@_)" ' Format the column as Accounting with $ sign
Cells.Select
Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(5), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True 'Sub total
the entire worksheet by the change in customer number
Cells.EntireColumn.AutoFit ' Auto the entire sheet
Range("A1").Select
ActiveWorkbook.Save 'Save the workbook
End With
xlApp.Quit 'Close Excel
Set xlApp = Nothing 'Release the instance of Excel