[AccessD] Building a spreadsheet from Access ... slow segment ofcode

NAILED IT !!!!

It's the printer spool service (spoolsv.exe) that's the problem.  The
default printer on my PC is a network printer and the slow VB code is
where I set up: page margins, orientation, 3 footers, 3 headers,
PaperSize, CenterVertically, CenterHorizontally, etc etc  (see below). 

I changed the default printer to a local printer (Win2PDF in this case),
re-ran the routine, and the time elapsed went from 40+ minutes to 4
minutes.

Thanks to the head techie from my local supplier, who was a fresh-faced
IT student of mine 20 years ago at the local Community College.  He was
round for a quiet one after work tonight and recognised the situation as
one he'd dealt with previously in a CAD environment.  What goes around
... 

Thanks to all for your input. 


Stephen Bond




 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Stephen
Sent: Sunday, 7 September 2008 2:15 p.m.
To: Stephen
Subject: Re: [AccessD] Building a spreadsheet from Access ... slow
segment ofcode

Darryl, 

A couple of things

1...yes, I always set ScreenUpdating to False 2...will check the
defaults now, but they may be diferent 
    on target machine(s).  This is why I left them in 
    there.  Comments?
3...the code is embedded in an Access system.  Will see if 
    I can extract the relevant bits.  Are you still 
    interested if I can do this?

Stephen Bond  



-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Darryl
Collins
Sent: Sunday, 7 September 2008 1:16 p.m.
To: Stephen
Subject: Re: [AccessD] Building a spreadsheet from Access ... slow
segment ofcode



Stephen,

Excel has always had an issue that setting up print ranges in
 Code takes F   O    R    E    V    E     R!!!

I am not sure it is a 'bug' as such, but it is well known and long
standingn issue.

I suggest you take out absolutely everything except what you need to
change.  Check what Excel's Defaults are and DO NOT set them again in
code, only put in there the stuff that changes the default.

Add in BEFORE the code starts

Application.calculation = xlcalculationmanual Application.screenupdating
= False


If it is still super slow, email me the file offlist and I will clean up
your Excel VBA code so it all runs faster.

Cheers
Darryl.




-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Stephen
Sent: Saturday, 6 September 2008 5:45 PM
To: Access Developers discussion and problem solving
Subject: [AccessD] Building a spreadsheet from Access ... slow segment
of code


The code segment below takes around 10 seconds to run.  The worksheet
has been just created in code, an embedded chart has been built on the
worksheet (again, code), and the code below is the 'prettying up' bit.
Yep, to get it, I recorded a macro in Excel and then commented out the
bits I didn't want/didn't work.

I put some rudimentary time checks at random places in this segment.
Embedded in the sample, I have put the elapsed time it took to get to
the indicated line.

Any ideas?  The rest of it runs really fast on a 6-year-old PC (768MB,
WinXP SP2, Processor x86 Family 6 Model 8 Stepping 0 AuthenticAMD ~1529
Mhz) but I can't see why this is happening.  I am generating a couple of
hundred sheets at a time, so the slowness is not trivial.

TIA

Stephen Bond


' finally build print specs

    xlWs.Range("A1").Select



    With xlWb.ActiveSheet.PageSetup

        .PrintArea = ""

        .PrintTitleRows = ""

        .PrintTitleColumns = ""

        .LeftHeader = "&6&Z&F!&A"

  ' 0 seconds

        .CenterHeader = "&""Arial,Bold""&16&A"

        .RightHeader = "&6Printed &D &T"

        .LeftFooter = "&6Prepared by Stephen Bond for " & conUser1

        .CenterFooter = "&""Arial,Bold""&16&A"

        .RightFooter = "&6based on an idea by J McKinlay, Southland
Boys' High School"

  ' 3 seconds

    '    .LeftMargin = Application.InchesToPoints(0.75)

    '    .RightMargin = Application.InchesToPoints(0.75)

    '    .TopMargin = Application.InchesToPoints(0.75)

    '    .BottomMargin = Application.InchesToPoints(0.75)

    '    .HeaderMargin = Application.InchesToPoints(0.5)

    '    .FooterMargin = Application.InchesToPoints(0.5)

        .PrintHeadings = False

        .PrintGridlines = True

        .PrintComments = xlPrintNoComments

  ' 5 seconds

 '''''       .PrintQuality = -4

        .CenterHorizontally = True

        .CenterVertically = False

        .Orientation = xlLandscape

        .Draft = False

  ' 7 seconds

        .PaperSize = xlPaperA4

        .FirstPageNumber = xlAutomatic

        .Order = xlDownThenOver

        .BlackAndWhite = False

        .Zoom = False

  ' 10 seconds

        .FitToPagesWide = 1

        .FitToPagesTall = 1

        .PrintErrors = xlPrintErrorsDisplayed

  ' 10 seconds

    End With





--