Page Setup in VBA taking too long.

[MikeCDPQ]

Part of a more elaborate VB script is to setup the page.

This part is taken from a macro recording where I remove any unnecessary lines.

With ActiveSheet.PageSetup
.PrintTitleRows = "$4:$4"
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.LeftFooter = "&D"
.CenterFooter = "&F"
.RightFooter = "Page &P of &N"
.LeftMargin = Application.InchesToPoints(0.5)
.RightMargin = Application.InchesToPoints(0.5)
.TopMargin = Application.InchesToPoints(0.5)
.BottomMargin = Application.InchesToPoints(1.5)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.Orientation = xlLandscape
.PaperSize = xlPaperLegal
.FitToPagesWide = 1
.FitToPagesTall = 500
.PrintErrors = xlPrintErrorsDisplayed
End With

When I run the macro, the screen flickers and this simple step takes 4 - 5 seconds. This may not seem like much but my script produces 800 to 1000 new spreadsheets so, do the math

Is this normal or is there any way to speed it up ??


Thanks for any suggestion !

 

[VBAjedi]

My experience has been that changing page setup settings via VBA takes a while. Several seconds is normal for me, even when I only need to change 2-3 settings.

Commenting out specific settings and re-running your code may help you identify any particularly slow settings (I bet the FitToPage settings are slow)

Alternately, consider having your code create the first spreadsheet, then use that spreadsheet as an ad-hoc template, copying it and only changing the PageSetup settings that differ (like the footers). Having to make fewer changes to each one may be faster. . .

VBAjedi

 

[MikeCDPQ]

Thanks VBAjedi,

I took out some setting and there isn't much difference. I guess I'll have to live with the time it take because I cannot use a template. The files being generated are send outside the company by email so a share template is out of the question :-(.

Thanks for your help. Basically I guess what I wanted to know was if it was normal for page setup to be slow via VBA. You say it is so I will live with it.

Thanks again.

 

[SkipVought]

ONE THOUSAND sheets?

What are you trying to do?

You're not gonna SAVE a workbook with 1000 sheets, are you? How could you find anything?

Maybe, if we knew what your business objective was, another approch might meet the requirement.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[MikeCDPQ]

SkipVought

Sorry for my misuse of the word sheet, I meant workbook or excel file.

My script takes +- 25000 excel lines, splits them according to different criteria, creates new workbooks and performs a few operations and then saves them and send them by email to the vendors.

Everything works beautifully, thanks to you in great part since you answered my posts on more than one occasion.

I just wanted to format the page setup of the newly created workbook (8½ x 14 landscape etc.) However, as I said before, if I am to add 4 - 5 secs to every workbook created (over 700) it won't be worth it . The whole progam already takes about an hour to run.

Thanks again.

 

[SkipVought]

Why not rethink the process.

1. format one workbook as a template

2. in a loop,
populate the template
save the workbook

Might that work?

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[VBAjedi]

Mike,

Skip's suggestion is basically what I was trying to say. Sorry I wasn't more clear. My thinking simply was that it doesn't really take longer to create a copy of an open workbook than it takes to just open a new workbook. So, you open a new workbook just like you are currently doing, change the necessary page attributes next, and then, for the next 700 repetitions, create a copy of that workbook (which inherits all the page attributes of the first workbook you made), paste your data into the copy, and save/send it.

You don't actually have to have a "template.xlt" file saved anywhere - you just kind of define your "template" on the fly.

VBAjedi

 

[MikeCDPQ]

Great Idea.


I used to add a new workbook on which I would paste some info from Master Sheet

Now, I open a template, paste the info onto it and save it as ...

Thanks again.

Mike

 

[rdodge]

One other thing to think about, which is something that I have ran into. Not really sure why it's the case, but it's something that I have found.

If you are attempting to print to a small local printer such as an inkjet printer, all of the file printer settings seems to take a lot longer to process than using a network server printer. Yes, color may take longer than black and white, but even a color network printer doesn't take near as much time to adjust as a local inkjet color printer.

Now the one big issue that I have had with Excel, it doesn't seem to remember the page setup nor does it remember the printer settings. At least not for Excel 2002, so I have had to code my Production Reports to set the settings as needed for each production report that gets printed out as the reporting code takes place.

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000