Resize Reports from Letter to A4 with automation

DRHSupport used Ask the Experts™
I have a reporting database with about 50 reporting templates in Letter Size.  I need to resize all of these reports to A4.  Does anyone know of a way to accomplish this without a lot of pain.  Also, if the resize if possible is there also a way to adjust the report margins in the same manner?
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
For Access 2002+, I believe this code will work. Paste it into a general module and run it.

Sub ChangeAllReports()
Dim rpt As Object

For Each rpt In Application.CurrentProject.AllReports
    DoCmd.OpenReport rpt.Name, acViewDesign
    With rpt.Printer
        .PaperSize = acPRPSA4
        .TopMargin = 1440     'Margins are measured in twips
        .BottomMargin = 1440  '1 inch = 1440 twips
        .LeftMargin = 1440
        .RightMargin = 1440
    End With
    DoCmd.Close acReport, rpt.Name, acSaveYes
End Sub

Access 2002+, you can use printer objects:

Sub SetPrinter(strFormname As String)

    DoCmd.OpenForm FormName:=strFormname, view:=acDesign, _
                   datamode:=acFormEdit, windowmode:=acHidden
    With Forms(form1).Printer   'Within the form's code you can use With Me.Printer
        .DeviceName = **Name of printer as listed in Printers and Faxes**    'To change the printer  You also need to set UseDefaultPrinter = false

        .TopMargin = 1440
        .BottomMargin = 1440
        .LeftMargin = 1440
        .RightMargin = 1440
        .ColumnSpacing = 360
        .RowSpacing = 360
        .ColorMode = acPRCMColor
        .DataOnly = False
        .DefaultSize = False
        .ItemSizeHeight = 2880
        .ItemSizeWidth = 2880
        .ItemLayout = acPRVerticalColumnLayout
        .ItemsAcross = 6
        .Copies = 1
        .Orientation = acPRORLandscape
        .Duplex = acPRDPVertical
        .PaperBin = acPRBNAuto
        .PaperSize = acPRPSLetter
        .PrintQuality = acPRPQMedium
    End With
    DoCmd.Close objecttype:=acForm, objectname:=strFormname, _

End Sub

Before 2002 with an mda file you can use PrtDevMode, PrtDevNames, and PrtMip properties - very complicated - consult the Win32 Software Development Kit for complete documentation or or you can use 's an API workaround too:
Microsoft: Access Modules (VBA Coding) FAQ - Change the Default Printer

Before 2002 with an mde file, your only option is using RunCommand acCmdPageSetup with Sendkeys before each print. For example:

Sendkeys "{RIGHT}%zll~"
RunCommand acCmdPageSetup

will set the page setup to legal size paper for most printers.


I should have joined this website a long time ago!
Regarding the first code that you gave to change all reports - if 1/2 of the reports are portrait and the other 1/2 are landscape - I assume it will not make a difference as only the size and margins are being changed?
The code will change only the page size and margins. It should not change the orientation. It will change all reports. If you want to exclude some reports, we need to get more complicated. Backup the database before using it in case it doesn't do what you want.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial