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

Thanks Jim, yes, am using early binding.  At present, am following up
Darryl's earlier note on the known issue <<Excel has always had an issue
that setting up print ranges in Code takes F   O    R    E    V    E
R!!!>>, also some ideas from Dan on computer performance settings.  

Thanks to everybody so far ... we'll nail this one !

Stephen

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

Stephen,

  You using early or late binding?  While late binding does get you
around reference issues, it does carry a performance penalty.  If your
doing lots of operations with OLE Automation, it may be worth the
references hassle to use early binding.

Jim. 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Stephen
Sent: Saturday, September 06, 2008 3:45 AM
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

    

 
 
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com