Hi,
Does anyone know why updating the page setup for Excel worksheets via VBA from an Access module is so slow? As an example, all I'm setting is:
Yet this is averaging about 4-5 seconds per worksheet. This is part of an auomation routine that could be reformatting 10s or even 100s of sheets, so speed is an issue.
Any advice would be greatly appreciated.
Thanks, Iain
Does anyone know why updating the page setup for Excel worksheets via VBA from an Access module is so slow? As an example, all I'm setting is:
Code:
with appXL
...
'select the data range
.Cells(1 + frm.txtAddRows.Value, 1 + frm.txtAddCols.Value).Select
.Range(appXL.Selection, appXL.ActiveCell.SpecialCells(xlLastCell)).Select
'apply the print formatting
.ActiveSheet.PageSetup.PrintArea = appXL.Selection.Address
With .ActiveSheet.PageSetup
.LeftHeader = "Deloitte & Touche LLP"
.CenterHeader = lbx.ItemData(intListItems)
.RightHeader = Format(Int(Now()), "dd-mmm-yyyy")
.LeftFooter = "Confidential"
.CenterHorizontally = True
If appXL.Selection.Width > appXL.Selection.Height Then
.Orientation = xlLandscape
Else
.Orientation = xlPortrait
End If
.PaperSize = xlPaperA4
.Order = xlDownThenOver
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 100
End With
...
End With
Yet this is averaging about 4-5 seconds per worksheet. This is part of an auomation routine that could be reformatting 10s or even 100s of sheets, so speed is an issue.
Any advice would be greatly appreciated.
Thanks, Iain