Modifying PageSetup Via VBA From Another App?

[PeteCresswell]

Right now, I'm in MS Access VBA and doing:

-------------------------------------------------
1070 With mySS.ActiveSheet.PageSetup
1071 .LeftHeader = ""
1072 .leftMargin = 0
1073 .RightMargin = 0
1074 .topMargin = 0
1075 .bottomMargin = 0
1079 .HeaderMargin = 0
1080 .FooterMargin = 0
1081 .Orientation = xlLandscape
1082 .FitToPagesWide = 1
1083 .FitToPagesTall = 1
1089 End With
-------------------------------------------------

This works, but takes a long time - it's like Excel is
re-drawing/reformatting the page for each property instead of
redrawing once at "End With".

I can live with that for one sheet, but this app is creating a
half-dozen sheets in one workbook and the wait time will be too long
if multiplied by six.

Seems like there sb some kind of default in MS Excel that I could set
once when opening up the workbook and then be inherited by each
worksheet I create..... or some way to create a worksheet using an
existing sheet as the template.

Anybody know?

How about the root performance issue? Am I doing something dumb in
the "With" clause?

 

[JE McGimpsey]

That's right. It is.

You should be able to modify this:

http://www.mcgimpsey.com/excel/udfs/pagesetup.html

 

[PeteCresswell]

http://www.mcgimpsey.com/excel/udfs/pagesetup.html

Thanks. That's just the ticket - runs in a flash.

Now, not to turn into a tarbaby on you.... but it looks like the parms
in PAGE.SETUP are positional, but I can't find anything on it.

I need to add FitToPagesWide and FitToPagesTall....

Gonna start just trying blind, but in the event that I can't stumble
into it, do you have any idea where to go for docs on that string?

 

[JE McGimpsey]

From the XL 4 Macro Reference:

Scale is a number representing the percentage to increase or decrease the
size of the sheet. All scaling retains the aspect ratio of the original.
- To specify a percentage of reduction or enlargement, set scale to
the percentage.
- For worksheets and macros, you can specify the number of pages
that the printout should be scaled to fit. Set scale to a two-item
horizontal array, with the first item equal to the width and the
second item equal to the height. If no constraint is necessary in
one direction, you can set the corresponding value to #N/A.
- Scale can also be a logical value. To fit the print area on a
single page, set scale to TRUE.

Note that in my macro, I've used "Zoom" instead of "Scale"