Change margins in Excel using VBScript Code

4
votes

I have a script that currently takes in an Excel document and turns it into a report (still in the Excel format), broken up in to two sheets: UPLOAD and PRINTOUT.

The Upload sheet shows all of the information that was taken in from the original Excel doc and filters it into the right columns/rows for the report. The Printout sheet takes the Upload sheet and formats it further to be ready for an actual printout.

The problem is, I'm trying to apply Orientation and Margin PageSetup parameters, but it is just ignoring them. No matter what I do, the page format won't change.

Mainly, I need these parameters changed (margins are in inches):

  • Landscape Orientation
  • Left Margin (0.36)
  • Right Margin (0.25)
  • Top and Bottom Margins at (0.5)
  • Header and Footer Margins at (0.25)

Here's the Code where I think there's an issue:

'Start printout sheet

Set objLastSheet = objWorkbook.Worksheets("Upload")
Set objWorksheet = objWorkbook.Worksheets("Upload")
objWorksheet.Copy, objLastSheet

Set objWorksheet2 = objWorkbook.Worksheets("Upload (2)")
objWorksheet2.Name = "Printout"

Set objRange = objWorksheet2.Pagesetup
Set objRange.PageSetup.Orientation= xlLandscape
Set objRange.PageSetup.LeftMargin = Application.InchesToPoints(0.36) 
Set objRange.PageSetup.RightMargin = Application.InchesToPoints(0.25)
Set objRange.PageSetup.TopMargin = Application.InchesToPoints(0.5)  
Set objRange.PageSetup.BottomMargin = Application.InchesToPoints(0.5) 
Set objRange.PageSetup.HeaderMargin = Application.InchesToPoints(0.25) 
Set objRange.PageSetup.FooterMargin = Application.InchesToPoints(0.25)

I didn't write the rest of the code for the upload, I was just asked to make the margins work so I've been trying to add whatever need be to the existing code. Not sure if it's syntax or if I'm just missing something crucial. Thank you in advance!

EDIT: Current code. Orientation is Landscape and is fitting to one page wide now, but the Margin parameters are still not taking (When I go to print preview, it still says "Normal Margins"). 

Const xlLandscape = 2

With objWorksheet2.Pagesetup
.Orientation = xlLandscape
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = False
.LeftMargin = Application.InchesToPoints(0.5) 
.RightMargin = Application.InchesToPoints(0.5) 
.TopMargin = Application.InchesToPoints(0.5) 
.BottomMargin = Application.InchesToPoints(0.5) 
.HeaderMargin = Application.InchesToPoints(0.5) 
.FooterMargin = Application.InchesToPoints(0.5)
End With

FINAL EDIT

For some reason, my Application.InchesToPoints() function wasn't working. Maybe I didn't have a library included or something, but nonetheless, when I manually converted the values and entered their point values, it worked.

Final Solution:

Const xlLandscape = 2

With objWorksheet2.Pagesetup
.Orientation = xlLandscape
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = False
.LeftMargin =  26
.RightMargin =  18
.TopMargin =  36
.BottomMargin = 36
.HeaderMargin = 18
.FooterMargin = 18
End With
3
excelvbscriptvba

3 Answers

6
votes

ObjRange = objWorksheet2.Pagesetup so you should be using:

Set objRange.Orientation= xlLandscape

etc.

or rather:

 With objWorksheet2.Pagesetup
   .Orientation= xlLandscape
   .LeftMargin = Application.InchesToPoints(0.36) 
   .RightMargin = Application.InchesToPoints(0.25)
   .TopMargin = Application.InchesToPoints(0.5)  
   .BottomMargin = Application.InchesToPoints(0.5) 
   .HeaderMargin = Application.InchesToPoints(0.25) 
   .FooterMargin = Application.InchesToPoints(0.25) 
End With
1
votes

VBScript dosen't know the value of xl contants. You can either replace the xl constants with there value or you can set the value of the xl constant yourself. I prefer used the a const

Const xlLandscape = 2

It makes this make easier to research

.Orientation = xlLandscape

This should do the trick for you

Sub setWorksheetPageSetup(xlWS)
    Const xlLandscape = 2

    With xlWS.PageSetup
        .Orientation = xlLandscape
        .LeftMargin = Application.InchesToPoints(0.36)
        .RightMargin = Application.InchesToPoints(0.25)
        .TopMargin = Application.InchesToPoints(0.5)
        .BottomMargin = Application.InchesToPoints(0.5)
        .HeaderMargin = Application.InchesToPoints(0.25)
        .FooterMargin = Application.InchesToPoints(0.25)
    End With
End Sub

Usage:

setWorksheetPageSetup objWorksheet2

0
votes

You may need to verify the unit measurement of your values.

If you're already using cm or centimeters, you do not need to use the Application.InchesToPoints(...) function, which receives as input, values in inches.

The following sets the active sheet's page Margins to "Narrow" using values that are in cm.

Note that the page orientation is set to "Landscape".

Set myOutput = Sheets(ActiveSheet.Name)

myOutput.PageSetup.Orientation = xlLandscape
myOutput.PageSetup.TopMargin = 1.91
myOutput.PageSetup.BottomMargin = 1.91
myOutput.PageSetup.LeftMargin = 0.64
myOutput.PageSetup.RightMargin = 0.64
myOutput.PageSetup.HeaderMargin = 0.76
myOutput.PageSetup.FooterMargin = 0.76

I hope this helps you. Thank you. Peace.