Rx_
Nothing In Moderation
- Local time
- Today, 01:10
- Joined
- Oct 22, 2009
- Messages
- 2,803
The work site recentlly updated to a new Xerox printer/copier/scanner. The existing automation code for Excel reports generated with VBA stopped working correctly for the Zoom to All Columns Fit on Page.
This is a new and evidently very popular Xerox printer. Those of you who are new to Excel programming from MSAccess might find this useful.
For all applications, I never use the Access Reports. All reports are customized Excel reports.
Printer Information for future searches:
Xerox Color 560 XC PCL6
Data Format RAW Driver Name: UNIDRV.DLL
Data File: xgchnxhg.gpd Config File UNIDRVVI.DLL Driver Version 6.00 for Windows NT x86
' after setting up the spread sheet - use the Page Setup
The .FitToPagesTall = False ' this use to be 0 on previous printers but False works on the new Xerox
The old .FitToPagesTall = 0 that use to work on the old Xerox just really doesn't work on the new xerox printer.
This is a new and evidently very popular Xerox printer. Those of you who are new to Excel programming from MSAccess might find this useful.
For all applications, I never use the Access Reports. All reports are customized Excel reports.
Printer Information for future searches:
Xerox Color 560 XC PCL6
Data Format RAW Driver Name: UNIDRV.DLL
Data File: xgchnxhg.gpd Config File UNIDRVVI.DLL Driver Version 6.00 for Windows NT x86
Code:
This first code is just an example - for no real reason except to show how to transfer a SQL string into Excel.
' Just some standard code set up where some SQL code is custom generated
Set strDataDirHz = CurrentDb.OpenRecordset(strSQLDirHz, dbOpenSnapshot, dbReadOnly) ' suggestion was this could be faster but it is not
'Set strDataDirHz = CurrentDb.OpenRecordset(strSQLDirHz, dbOpenSnapshot)
If ObjXL Is Nothing Then
Set ObjXL = New Excel.Application
ObjXL.EnableEvents = False
Else
Excel.Application.Quit
DoEvents
Set ObjXL = New Excel.Application
ObjXL.EnableEvents = False
End If
On Error GoTo PROC_Error
ObjXL.Visible = False ' set ObjXL.Visible = True ' for debug
ObjXL.Workbooks.Add
'objXL.Worksheets.Add
intWorksheetNum = 1
'ObjXL.Visible = False ' set for Production copy
intRowPos = 1
ObjXL.Worksheets(intWorksheetNum).Name = ReportPathNameThe .FitToPagesTall = False ' this use to be 0 on previous printers but False works on the new Xerox
The old .FitToPagesTall = 0 that use to work on the old Xerox just really doesn't work on the new xerox printer.
Code:
With ObjXL.ActiveSheet.PageSetup
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape 'xlPortrait as other choice
.Draft = False
.PaperSize = xlPaper11x17 ' paper size
.FirstPageNumber = xlAutomatic
.Order = xlOverThenDown
.BlackAndWhite = False
'.Zoom = 80 ' below is print all columns on one page
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = False ' this use to be 0 on previous printers but False works on the new Xerox
'.PrintErrors = xlPrintErrorsDisplayed
.OddAndEvenPagesHeaderFooter = False
.DifferentFirstPageHeaderFooter = False
.ScaleWithDocHeaderFooter = True
.AlignMarginsHeaderFooter = True
.PrintTitleRows = "$1:$" & (intRowPos - 1) ' repeats header row 1 to 5
.LeftFooter = "Page &P of &N"
.RightFooter = "&D"
.LeftMargin = ObjXL.InchesToPoints(0.25)
.RightMargin = ObjXL.InchesToPoints(0.25)
.TopMargin = ObjXL.InchesToPoints(0.5)
.BottomMargin = ObjXL.InchesToPoints(0.5)
.HeaderMargin = ObjXL.InchesToPoints(0.3)
.FooterMargin = ObjXL.InchesToPoints(0.3)
.PrintHeadings = False
End With