Setting Excel Worksheet Print options in Access VBA code

[FMoody]

Greetings, all. I have a sticky problem that I am at wit's end over. Since this is both an Excel and an Access problem, I am cross-posting it in both Excel and Access forums.

Situation:
An Access application that generates a report whose output is an Excel workbook file. The report details Production and Downtime data for an industrial processing plant.

Problem:
The Excel workbook consists of N+1 worksheets: a Chart worksheet and N data pages. The number of data worksheets is selected by the user before the report is run. The Chart worksheet has two charts on it for each data worksheet.

After extracting and aggregating the required data, creating data worksheets and writing outputs to same, charts are created on the Chart worksheet. The Chart sheet ends up with contents that, if printed with no "fit to page" option, takes four pages to print. They want it all on one page.

The users when printing the output report workbook sheet can of course manually set "fit to page" before printing, but they would prefer that this option already be set.

I am trying to do that in the VBA code, and hitting a wall.

I took an output worksheet, recorded a macro (that works) inside the Excel file, and extracted the code to use in the Access VBA routine that generates the workbook file. That approach is failing.

here is the macro code:

    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .PrintTitleRows = ""
        .PrintTitleColumns = ""
    End With
    Application.PrintCommunication = True
    ActiveSheet.PageSetup.PrintArea = ""
    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = ""
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = ""
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0.7)
        .RightMargin = Application.InchesToPoints(0.7)
        .TopMargin = Application.InchesToPoints(0.75)
        .BottomMargin = Application.InchesToPoints(0.75)
        .HeaderMargin = Application.InchesToPoints(0.3)
        .FooterMargin = Application.InchesToPoints(0.3)
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = xlPrintNoComments
        .PrintQuality = 600
        .CenterHorizontally = False
        .CenterVertically = False
        .Orientation = xlPortrait
        .Draft = False
        .PaperSize = xlPaperLetter
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = 1
        .PrintErrors = xlPrintErrorsDisplayed
        .OddAndEvenPagesHeaderFooter = False
        .DifferentFirstPageHeaderFooter = False
        .ScaleWithDocHeaderFooter = True
        .AlignMarginsHeaderFooter = True
        .EvenPage.LeftHeader.Text = ""
        .EvenPage.CenterHeader.Text = ""
        .EvenPage.RightHeader.Text = ""
        .EvenPage.LeftFooter.Text = ""
        .EvenPage.CenterFooter.Text = ""
        .EvenPage.RightFooter.Text = ""
        .FirstPage.LeftHeader.Text = ""
        .FirstPage.CenterHeader.Text = ""
        .FirstPage.RightHeader.Text = ""
        .FirstPage.LeftFooter.Text = ""
        .FirstPage.CenterFooter.Text = ""
        .FirstPage.RightFooter.Text = ""
    End With
    Application.PrintCommunication = True
    ActiveWorkbook.Save

here is the altered code that is failing in the VBA routine:

    oChartWorksheet.Activate
 
 
    oExcel.Application.PrintCommunication = False
    With oWorkbook.ActiveSheet.PageSetup
        .PrintTitleRows = ""
        .PrintTitleColumns = ""
    End With
    oExcel.Application.PrintCommunication = True
 
    oWorkbook.ActiveSheet.PageSetup.PrintArea = ""
 
    oExcel.Application.PrintCommunication = False
    With oWorkbook.ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = ""
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = ""
        .RightFooter = ""
        .LeftMargin = oExcel.Application.InchesToPoints(0.7)
        .RightMargin = oExcel.Application.InchesToPoints(0.7)
        .TopMargin = oExcel.Application.InchesToPoints(0.75)
        .BottomMargin = oExcel.Application.InchesToPoints(0.75)
        .HeaderMargin = oExcel.Application.InchesToPoints(0.3)
        .FooterMargin = oExcel.Application.InchesToPoints(0.3)
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = xlPrintNoComments
        .PrintQuality = 600
        .CenterHorizontally = False
        .CenterVertically = False
        .Orientation = xlPortrait
        .Draft = False
        .PaperSize = xlPaperLetter
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = False
[B]       .FitToPagesWide = 1[/B]
[B]       .FitToPagesTall = 1[/B]
        .PrintErrors = xlPrintErrorsDisplayed
        .OddAndEvenPagesHeaderFooter = False
        .DifferentFirstPageHeaderFooter = False
        .ScaleWithDocHeaderFooter = True
        .AlignMarginsHeaderFooter = True
        .EvenPage.LeftHeader.Text = ""
        .EvenPage.CenterHeader.Text = ""
        .EvenPage.RightHeader.Text = ""
        .EvenPage.LeftFooter.Text = ""
        .EvenPage.CenterFooter.Text = ""
        .EvenPage.RightFooter.Text = ""
        .FirstPage.LeftHeader.Text = ""
        .FirstPage.CenterHeader.Text = ""
        .FirstPage.RightHeader.Text = ""
        .FirstPage.LeftFooter.Text = ""
        .FirstPage.CenterFooter.Text = ""
        .FirstPage.RightFooter.Text = ""
    End With
[B][I][COLOR=red]   oExcel.Application.PrintCommunication = True[/COLOR][/I][/B]
    oExcel.ActiveWorkbook.Save

objects:
oExcel is DIMmed as excel.application
oWorkbook is DIMmed as excel.workbook and set to refer to the workbook file
oChartWorksheet is DIMmed as excel.worksheet and set to refer to the chart worksheet
the objects are correctly set, as they are used successfully in creating and populating the output file.

The lines in BOLD font are the ones that set "fit to page" (I believe)
The line in RED is where the code throws an error
(run time error 1004
Method "PrintCommunication" of object "_Application" failed)

Any help would be greatly appreciated.

 

[Trevor G]

Please don't duplicate your thread as it is against the Forum rules.

 

[fivearrows]

The lines in BOLD font are the ones that set "fit to page" (I believe)
The line in RED is where the code throws an error
(run time error 1004
Method "PrintCommunication" of object "_Application" failed)

Any help would be greatly appreciated.

I had a similar problem. A macro that I use daily just suddenly stopped working with this error. The code below is clearly a kludge and should not be necessary but it does seem to work so far. It does incur some performance penalty. Waiting a few extra seconds, though, may be preferable to doing the steps by hand every time.

To use, replace "Application.PrintCommunication = True" with "WorkAroundMicrosoftFailings". Please pardon the function name ... I was very frustrated when I wrote the routine. You may want to rename it for better Karma.

Sub WorkAroundMicrosoftFailings()
   On Error GoTo WAMFretry

WAMFturnOn:
   Application.PrintCommunication = True
   On Error GoTo 0
   GoTo WAMFdone
   
WAMFretry:
   Application.Wait DateAdd("s", 2, Now)
   GoTo WAMFturnOn
   
WAMFdone:
   
End Sub

It should be fairly self explanatory. Turn on error redirection and try to enable print communication. If it passes, great, turn off error redirection and return. If it fails, wait a couple seconds for Excel to do whatever it needs to do and retry. The 1004 error in this case seems to be very timing dependent.

In another language, this could be handled more elegantly (without goto's). This is as clean as I can figure out how to make it within the VBA syntax. Suggestions welcomed.

YMMV, but it lets me get my work done.

 

[frummiewc]

I had a similar problem. A macro that I use daily just suddenly stopped working with this error. The code below is clearly a kludge and should not be necessary but it does seem to work so far. It does incur some performance penalty. Waiting a few extra seconds, though, may be preferable to doing the steps by hand every time.

To use, replace "Application.PrintCommunication = True" with "WorkAroundMicrosoftFailings". Please pardon the function name ... I was very frustrated when I wrote the routine. You may want to rename it for better Karma.

Sub WorkAroundMicrosoftFailings()
   On Error GoTo WAMFretry

WAMFturnOn:
   Application.PrintCommunication = True
   On Error GoTo 0
   GoTo WAMFdone
   
WAMFretry:
   Application.Wait DateAdd("s", 2, Now)
   GoTo WAMFturnOn
   
WAMFdone:
   
End Sub

It should be fairly self explanatory. Turn on error redirection and try to enable print communication. If it passes, great, turn off error redirection and return. If it fails, wait a couple seconds for Excel to do whatever it needs to do and retry. The 1004 error in this case seems to be very timing dependent.

In another language, this could be handled more elegantly (without goto's). This is as clean as I can figure out how to make it within the VBA syntax. Suggestions welcomed.

YMMV, but it lets me get my work done.

This worked perfectly. Thanks! I can now stop banging my head.