Fit to 1 by 1 pages

[JeroenM]

In Access I wrote VBA programm to export a query to Excel and to change the
page layout.

DoCmd.OutputTo acOutputQuery, "Q_Totaal_Kruistabel", acFormatXLS,
Left(CurrentDb.Name, y - 2) & "\Status" & Koptekst & "_" & Wiltekst & ".xls",
True
Set xlApp = GetObject(, "Excel.Application")

For the Page Setup of the new Excel file, I would like to have the
following settings: "Fit to 1 by 1 pages"

What is the VBA code, so the result will be fitted on 1 by 1 pages?

Thanks in advance,

JeroenM.

 

[Douglas J Steele]

My general advice is to record a macro in Excel, and look at it. If you do
that, you'll see that the relevant statements are

With ActiveSheet.PageSetup
.FitToPagesWide = 1
.FitToPagesTall = 1
End With

Something like the following should work:

xlApp.Application.Workbooks.Open Left(CurrentDb.Name, y - 2) & "\Status"
& Koptekst & "_" & Wiltekst & ".xls"
With xlApp.Application.ActiveWorkbook.Worksheet(1).PageSetup
.FitToPagesWide = 1
.FitToPagesTall = 1
End With

 

[JeroenM]

Thanks, but I used the same code and it didn't work.

It looks like VBA in Access doesn't "recognize" the text as a valid VBA
statemement. Also normally when you type a statement it capatilizes some
characters (rowheight ==> RowHeight), but with the statements FitToPagesWide
and FitToPagesTall this doesn't happen.

Any advise?

JeroenM.

 

[Douglas J Steele]

Apologies, there was a typo in what I gave you. It should have been
WorksheetS(1), not Worksheet(1):

Additionally, I just found out from the Excel help file that you need to
ensure that PageSetup isn't set to Zoom, because it ignores FitToPages if it
is.

xlApp.Application.Workbooks.Open Left(CurrentDb.Name, y - 2) &
"\Status"
& Koptekst & "_" & Wiltekst & ".xls"
With xlApp.Application.ActiveWorkbook.Worksheets(1).PageSetup
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
End With

Note that you may have to explicitly set the PrintArea, as well as Borders:

.PrintTitleRows = "$2:$4"
.PrintTitleColumns = "$B:$E"
.PrintArea = "$F$3:$IT$64"

Why you're not getting capitalization is that you're using Late Binding, so
that the VB Editor doesn't know anything about what methods and properties
are associated with what objects. That's nothing to be concerned about.

 

[JeroenM]

Great, you found the solution. Works fine.

Thanks, JeroenM.