Activesheet.pagesetup

Hi,

Have an Excel spreadsheet that contains a macro that creates an new
worksheet and then fixes the pagesetup.

All the activesheet.pagesetup commands are painfully slow, but occasionally
they complete in the blink of an eye.

Can anyone help me to speed them up, or explain the inconsistent
performance?

Thanks,
Joe

Hi Spenceley

You can save the Excel file as a webpage
It will create a htm file and a folder with all pictures for you


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Spenceley" <[email protected]> wrote in message news:[email protected]...
> Hi,
>
> Have an Excel spreadsheet that contains a macro that creates an new worksheet and then fixes the pagesetup.
>
> All the activesheet.pagesetup commands are painfully slow, but occasionally they complete in the blink of an eye.
>
> Can anyone help me to speed them up, or explain the inconsistent performance?
>
> Thanks,
> Joe
>

Sorry, wrong thread

--
Regards Ron de Bruin
http://www.rondebruin.nl



"Ron de Bruin" <[email protected]> wrote in message news:[email protected]...
> Hi Spenceley
>
> You can save the Excel file as a webpage
> It will create a htm file and a folder with all pictures for you
>
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
>
> "Spenceley" <[email protected]> wrote in message news:[email protected]...
>> Hi,
>>
>> Have an Excel spreadsheet that contains a macro that creates an new worksheet and then fixes the pagesetup.
>>
>> All the activesheet.pagesetup commands are painfully slow, but occasionally they complete in the blink of an eye.
>>
>> Can anyone help me to speed them up, or explain the inconsistent performance?
>>
>> Thanks,
>> Joe
>>
>
>

Read this posting from Tom
************************

To do multiple pages


grouping (which is what you would use manually) is largely not supported in
VBA, however.


If you have one sheet set up, then KeepItCool has suggested this as a way to
format multiple sheets:


sheets(array("sheet2","sheet3","sheet4")).select
sheets("sheet3").activate
SendKeys "{enter}"
Application.Dialogs(xlDialogPageSetup).Show


keepITcool
-------------------------


So you would set up one sheet using the xl4 approach below, then use
KeepItCool's technique for the other sheets.


Other than that you would need to loop through the sheets. You are probably
already aware that pagesetup is extremely slow. So you should only set
those attributes that you need to do because each setting is an individual
call to the slow pagesetup object.


somewhat faster is to use the xl4 macro approach posted here by John Green:


From: John Green ([email protected])
Subject: Re: Pagesetup code takes too long
Newsgroups: microsoft.public.excel.programming
View complete thread (5 articles)
Date: 1999/03/29


Macro =
"Page.Setup(,,.25,.25,.5,.25,,False,True,True,2,1,{1,1},,,,,.25,.25)"
ExecuteExcel4Macro Macro


HTH,


John Green - Excel MVP
Sydney
Australia


=================================
From: John Green ([email protected])
Subject: Re: About PageSetup..
Newsgroups: microsoft.public.excel.programming
View complete thread (10 articles)
Date: 2001-01-22 12:57:23 PST


PageSetup in VBA has always been a painfully slow process.
If you can't avoid having to set these parameters,
you can use the Excel 4 macro function, PAGE.SETUP
to carry out most of the PageSetup operations much
more quickly. The following two macros are almost
equivalent, and should give you the clues you need
to start using PAGE.SETUP. You can download a full
description of all the Excel 4 macro functions from
Microsoft's web site:


Sub PS()
ActiveSheet.DisplayPageBreaks = False
With ActiveSheet.PageSetup
.LeftHeader = "My Company"
.CenterHeader = ""
.RightHeader = "&D / &T"
.LeftFooter = "Highly Confidential and Proprietary"
.CenterFooter = ""
.RightFooter = "Finance"
.LeftMargin = Application.InchesToPoints(0.54)
.RightMargin = Application.InchesToPoints(0.3)
.TopMargin = Application.InchesToPoints(0.4)
.BottomMargin = Application.InchesToPoints(0.36)
.HeaderMargin = Application.InchesToPoints(0.22)
.FooterMargin = Application.InchesToPoints(0.17)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
' .PrintQuality = 600 ' does not work with all the printers
.CenterHorizontally = True
.CenterVertically = True
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
End With
End Sub


Sub PS4()


head = """&LMy Company&R&D / &T"""
foot = """&LHighly Confidential and Proprietary&RFinance"""
pLeft = 0.54
pRight = 0.3
Top = 0.4
bot = 0.36
head_margin = 0.22
foot_margin = 0.17
hdng = False
grid = False
notes = False
quality = ""
h_cntr = False
v_cntr = False
orient = 2
Draft = False
paper_size = 1
pg_num = """Auto"""
pg_order = 1
bw_cells = False
pscale = True


pSetUp = "PAGE.SETUP(" & head & "," & foot & "," & _
pLeft & "," & pRight & ","
pSetUp = pSetUp & Top & "," & bot & "," & hdng & _
"," & grid & "," & h_cntr & ","
pSetUp = pSetUp & v_cntr & "," & orient & "," _
& paper_size & "," & pscale & ","
pSetUp = pSetUp & pg_num & "," & pg_order _
& "," & bw_cells & "," & quality & ","
pSetUp = pSetUp & head_margin & "," & foot_margin _
& "," & notes & "," & Draft & ")"
Application.ExecuteExcel4Macro pSetUp
End Sub


John Green (Excel MVP)
Sydney
Australia


--
Regards,
Tom Ogilvy





--
Regards Ron de Bruin
http://www.rondebruin.nl



"Spenceley" <[email protected]> wrote in message news:[email protected]...
> Hi,
>
> Have an Excel spreadsheet that contains a macro that creates an new worksheet and then fixes the pagesetup.
>
> All the activesheet.pagesetup commands are painfully slow, but occasionally they complete in the blink of an eye.
>
> Can anyone help me to speed them up, or explain the inconsistent performance?
>
> Thanks,
> Joe
>

Ron,

I've started using Excel4 Macros but I don't have Excel2K on my PC and
therefore can't open the help file.

Can you point me towards a good online reference guide?

Thanks,
Joe


"Ron de Bruin" <[email protected]> wrote in message
news:[email protected]...
> Read this posting from Tom
> ************************
>
> To do multiple pages
>
>
> grouping (which is what you would use manually) is largely not supported
> in
> VBA, however.
>
>
> If you have one sheet set up, then KeepItCool has suggested this as a way
> to
> format multiple sheets:
>
>
> sheets(array("sheet2","sheet3","sheet4")).select
> sheets("sheet3").activate
> SendKeys "{enter}"
> Application.Dialogs(xlDialogPageSetup).Show
>
>
> keepITcool
> -------------------------
>
>
> So you would set up one sheet using the xl4 approach below, then use
> KeepItCool's technique for the other sheets.
>
>
> Other than that you would need to loop through the sheets. You are
> probably
> already aware that pagesetup is extremely slow. So you should only set
> those attributes that you need to do because each setting is an individual
> call to the slow pagesetup object.
>
>
> somewhat faster is to use the xl4 macro approach posted here by John
> Green:
>
>
> From: John Green ([email protected])
> Subject: Re: Pagesetup code takes too long
> Newsgroups: microsoft.public.excel.programming
> View complete thread (5 articles)
> Date: 1999/03/29
>
>
> Macro =
> "Page.Setup(,,.25,.25,.5,.25,,False,True,True,2,1,{1,1},,,,,.25,.25)"
> ExecuteExcel4Macro Macro
>
>
> HTH,
>
>
> John Green - Excel MVP
> Sydney
> Australia
>
>
> =================================
> From: John Green ([email protected])
> Subject: Re: About PageSetup..
> Newsgroups: microsoft.public.excel.programming
> View complete thread (10 articles)
> Date: 2001-01-22 12:57:23 PST
>
>
> PageSetup in VBA has always been a painfully slow process.
> If you can't avoid having to set these parameters,
> you can use the Excel 4 macro function, PAGE.SETUP
> to carry out most of the PageSetup operations much
> more quickly. The following two macros are almost
> equivalent, and should give you the clues you need
> to start using PAGE.SETUP. You can download a full
> description of all the Excel 4 macro functions from
> Microsoft's web site:
>
>
> Sub PS()
> ActiveSheet.DisplayPageBreaks = False
> With ActiveSheet.PageSetup
> .LeftHeader = "My Company"
> .CenterHeader = ""
> .RightHeader = "&D / &T"
> .LeftFooter = "Highly Confidential and Proprietary"
> .CenterFooter = ""
> .RightFooter = "Finance"
> .LeftMargin = Application.InchesToPoints(0.54)
> .RightMargin = Application.InchesToPoints(0.3)
> .TopMargin = Application.InchesToPoints(0.4)
> .BottomMargin = Application.InchesToPoints(0.36)
> .HeaderMargin = Application.InchesToPoints(0.22)
> .FooterMargin = Application.InchesToPoints(0.17)
> .PrintHeadings = False
> .PrintGridlines = False
> .PrintComments = xlPrintNoComments
> ' .PrintQuality = 600 ' does not work with all the printers
> .CenterHorizontally = True
> .CenterVertically = True
> .Orientation = xlLandscape
> .Draft = False
> .PaperSize = xlPaperLetter
> .FirstPageNumber = xlAutomatic
> .Order = xlDownThenOver
> .BlackAndWhite = False
> .Zoom = False
> .FitToPagesWide = 1
> .FitToPagesTall = 1
> End With
> End Sub
>
>
> Sub PS4()
>
>
> head = """&LMy Company&R&D / &T"""
> foot = """&LHighly Confidential and Proprietary&RFinance"""
> pLeft = 0.54
> pRight = 0.3
> Top = 0.4
> bot = 0.36
> head_margin = 0.22
> foot_margin = 0.17
> hdng = False
> grid = False
> notes = False
> quality = ""
> h_cntr = False
> v_cntr = False
> orient = 2
> Draft = False
> paper_size = 1
> pg_num = """Auto"""
> pg_order = 1
> bw_cells = False
> pscale = True
>
>
> pSetUp = "PAGE.SETUP(" & head & "," & foot & "," & _
> pLeft & "," & pRight & ","
> pSetUp = pSetUp & Top & "," & bot & "," & hdng & _
> "," & grid & "," & h_cntr & ","
> pSetUp = pSetUp & v_cntr & "," & orient & "," _
> & paper_size & "," & pscale & ","
> pSetUp = pSetUp & pg_num & "," & pg_order _
> & "," & bw_cells & "," & quality & ","
> pSetUp = pSetUp & head_margin & "," & foot_margin _
> & "," & notes & "," & Draft & ")"
> Application.ExecuteExcel4Macro pSetUp
> End Sub
>
>
> John Green (Excel MVP)
> Sydney
> Australia
>
>
> --
> Regards,
> Tom Ogilvy
>
>
>
>
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
>
> "Spenceley" <[email protected]> wrote in message
> news:[email protected]...
>> Hi,
>>
>> Have an Excel spreadsheet that contains a macro that creates an new
>> worksheet and then fixes the pagesetup.
>>
>> All the activesheet.pagesetup commands are painfully slow, but
>> occasionally they complete in the blink of an eye.
>>
>> Can anyone help me to speed them up, or explain the inconsistent
>> performance?
>>
>> Thanks,
>> Joe
>>
>
>

As far as I know it is working in Excel 97 also

What do you use

--
Regards Ron de Bruin
http://www.rondebruin.nl



"Spenceley" <[email protected]> wrote in message news:%[email protected]...
> Ron,
>
> I've started using Excel4 Macros but I don't have Excel2K on my PC and therefore can't open the help file.
>
> Can you point me towards a good online reference guide?
>
> Thanks,
> Joe
>
>
> "Ron de Bruin" <[email protected]> wrote in message news:[email protected]...
>> Read this posting from Tom
>> ************************
>>
>> To do multiple pages
>>
>>
>> grouping (which is what you would use manually) is largely not supported in
>> VBA, however.
>>
>>
>> If you have one sheet set up, then KeepItCool has suggested this as a way to
>> format multiple sheets:
>>
>>
>> sheets(array("sheet2","sheet3","sheet4")).select
>> sheets("sheet3").activate
>> SendKeys "{enter}"
>> Application.Dialogs(xlDialogPageSetup).Show
>>
>>
>> keepITcool
>> -------------------------
>>
>>
>> So you would set up one sheet using the xl4 approach below, then use
>> KeepItCool's technique for the other sheets.
>>
>>
>> Other than that you would need to loop through the sheets. You are probably
>> already aware that pagesetup is extremely slow. So you should only set
>> those attributes that you need to do because each setting is an individual
>> call to the slow pagesetup object.
>>
>>
>> somewhat faster is to use the xl4 macro approach posted here by John Green:
>>
>>
>> From: John Green ([email protected])
>> Subject: Re: Pagesetup code takes too long
>> Newsgroups: microsoft.public.excel.programming
>> View complete thread (5 articles)
>> Date: 1999/03/29
>>
>>
>> Macro =
>> "Page.Setup(,,.25,.25,.5,.25,,False,True,True,2,1,{1,1},,,,,.25,.25)"
>> ExecuteExcel4Macro Macro
>>
>>
>> HTH,
>>
>>
>> John Green - Excel MVP
>> Sydney
>> Australia
>>
>>
>> =================================
>> From: John Green ([email protected])
>> Subject: Re: About PageSetup..
>> Newsgroups: microsoft.public.excel.programming
>> View complete thread (10 articles)
>> Date: 2001-01-22 12:57:23 PST
>>
>>
>> PageSetup in VBA has always been a painfully slow process.
>> If you can't avoid having to set these parameters,
>> you can use the Excel 4 macro function, PAGE.SETUP
>> to carry out most of the PageSetup operations much
>> more quickly. The following two macros are almost
>> equivalent, and should give you the clues you need
>> to start using PAGE.SETUP. You can download a full
>> description of all the Excel 4 macro functions from
>> Microsoft's web site:
>>
>>
>> Sub PS()
>> ActiveSheet.DisplayPageBreaks = False
>> With ActiveSheet.PageSetup
>> .LeftHeader = "My Company"
>> .CenterHeader = ""
>> .RightHeader = "&D / &T"
>> .LeftFooter = "Highly Confidential and Proprietary"
>> .CenterFooter = ""
>> .RightFooter = "Finance"
>> .LeftMargin = Application.InchesToPoints(0.54)
>> .RightMargin = Application.InchesToPoints(0.3)
>> .TopMargin = Application.InchesToPoints(0.4)
>> .BottomMargin = Application.InchesToPoints(0.36)
>> .HeaderMargin = Application.InchesToPoints(0.22)
>> .FooterMargin = Application.InchesToPoints(0.17)
>> .PrintHeadings = False
>> .PrintGridlines = False
>> .PrintComments = xlPrintNoComments
>> ' .PrintQuality = 600 ' does not work with all the printers
>> .CenterHorizontally = True
>> .CenterVertically = True
>> .Orientation = xlLandscape
>> .Draft = False
>> .PaperSize = xlPaperLetter
>> .FirstPageNumber = xlAutomatic
>> .Order = xlDownThenOver
>> .BlackAndWhite = False
>> .Zoom = False
>> .FitToPagesWide = 1
>> .FitToPagesTall = 1
>> End With
>> End Sub
>>
>>
>> Sub PS4()
>>
>>
>> head = """&LMy Company&R&D / &T"""
>> foot = """&LHighly Confidential and Proprietary&RFinance"""
>> pLeft = 0.54
>> pRight = 0.3
>> Top = 0.4
>> bot = 0.36
>> head_margin = 0.22
>> foot_margin = 0.17
>> hdng = False
>> grid = False
>> notes = False
>> quality = ""
>> h_cntr = False
>> v_cntr = False
>> orient = 2
>> Draft = False
>> paper_size = 1
>> pg_num = """Auto"""
>> pg_order = 1
>> bw_cells = False
>> pscale = True
>>
>>
>> pSetUp = "PAGE.SETUP(" & head & "," & foot & "," & _
>> pLeft & "," & pRight & ","
>> pSetUp = pSetUp & Top & "," & bot & "," & hdng & _
>> "," & grid & "," & h_cntr & ","
>> pSetUp = pSetUp & v_cntr & "," & orient & "," _
>> & paper_size & "," & pscale & ","
>> pSetUp = pSetUp & pg_num & "," & pg_order _
>> & "," & bw_cells & "," & quality & ","
>> pSetUp = pSetUp & head_margin & "," & foot_margin _
>> & "," & notes & "," & Draft & ")"
>> Application.ExecuteExcel4Macro pSetUp
>> End Sub
>>
>>
>> John Green (Excel MVP)
>> Sydney
>> Australia
>>
>>
>> --
>> Regards,
>> Tom Ogilvy
>>
>>
>>
>>
>>
>> --
>> Regards Ron de Bruin
>> http://www.rondebruin.nl
>>
>>
>>
>> "Spenceley" <[email protected]> wrote in message news:[email protected]...
>>> Hi,
>>>
>>> Have an Excel spreadsheet that contains a macro that creates an new worksheet and then fixes the pagesetup.
>>>
>>> All the activesheet.pagesetup commands are painfully slow, but occasionally they complete in the blink of an eye.
>>>
>>> Can anyone help me to speed them up, or explain the inconsistent performance?
>>>
>>> Thanks,
>>> Joe
>>>
>>
>>
>
>