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

In article <du0il1hu28s7g60gq40dcjjnr6ip3i7dnu@4ax.com>,
PeteCresswell <x@y.z.Invalid> wrote:

> 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".

[PeteCresswell]

On Fri, 21 Oct 2005 10:02:18 -0600, in microsoft.public.excel you
wrote:

> 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"



In article <dsiil1tad4qi4mepguhdfplpntm70mqkge@4ax.com>,
PeteCresswell <x@y.z.Invalid> wrote:

> On Fri, 21 Oct 2005 10:02:18 -0600, in microsoft.public.excel you
> wrote:
>
> > 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?