Formatting Page Layout using VBA

[ErinL]

Hi everyone -

I have the following code in a module to set certain formatting properties when exporting an Access 2010 query to Excel:

With xlApp
.Application.Sheets("Spoilage").Select
.Application.Cells.Select
.Application.Selection.ClearFormats
.Application.Range("A1:K1").Select
.Application.Selection.Font.Bold = True
.Application.Selection.Font.Italic = True
.Application.Selection.Interior.ColorIndex = 33
.Application.Cells.Select
.Application.Selection.RowHeight = 15.75
.Application.Selection.Columns.AutoFit
.Application.Range("A2").Select
.Application.ActiveWindow.FreezePanes = True
.Application.Range("A1").Select
.Application.Range("A2:A65000").Select
.Application.Selection.Columns("A").NumberFormat = "m/d/yyyy"
.Application.Range("I2:I65000").Select
.Application.Selection.NumberFormat = "$#,##0.00"
.Application.Range("A1:K65000").Select
.Application.Selection.Columns("A:K").HorizontalAlignment = -4108
.Application.Selection.Font.Italic = True
.Application.Range("A2").Select
.Application.Activeworkbook.Save
.Application.Activeworkbook.Close
.Quit
End With

This works perfect but now I need to set some of the page layout properties (margins, orientation, etc.)

I have tried adding this:

With ActiveSheet.PageSetup
.LeftMargin = Application.InchesToPoints(0.5)
.RightMargin = Application.InchesToPoints(0.5)
.TopMargin = Application.InchesToPoints(0.75)
.BottomMargin = Application.InchesToPoints(0.75)
.Orientation = xlPortrait
.PaperSize = xlPaperA4
End With

But I get a complie error (Method or data member not found) on the .InchesToPoints part of the line.

Would someone please help me out with the correct syntax?

Thank you in advance.

 

[JHB]

Maybe you are missing the references to the Excel object, then you are running the code from MS-Access, (not tested):

[COLOR=Red][B]xlApp.[/B][/COLOR]Application.InchesToPoints(0.5)

I assume that the code runs in the same procedure as the other code, else you need to declare and set the Excel object, (xlApp).

 

[ErinL]

This works. Thought I'd post in case it could help someone else.

With xlApp
.Sheets("Spoilage").PageSetup.Orientation = 2
.Sheets("Spoilage").PageSetup.LeftMargin = (9)
.Sheets("Spoilage").PageSetup.RightMargin = (9)
.Sheets("Spoilage").PageSetup.TopMargin = (9)
.Sheets("Spoilage").PageSetup.BottomMargin = (9)
.Sheets("Spoilage").PageSetup.PaperSize = acPRPSLegal
End With