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

Dan,

Wow, thanks for your fulsome reply.  I shall look at each of these in
turn.  May take a couple of days.  I use CCleaner regularly, other than
that, a lot of things to try.  Will report in a few days.

Stephen

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

Hi Stephen,

I'm surprised that formatting a single sheet in Excel takes so long.
>From your data it's not a single method, but all of them.  This makes me
think that the first thing to look at is your PC.

768 Mb of Ram seems a little low.  1529 MHz seems fine.

I would look next at your computer's performance settings.  Go to My
Computer Properties, Advanced tab, then push Settings under Performance.


	1) Under Visual Effects, uncheck everything except 'Smooth
edges...'
and 'Use visual styles ...'.
	2) Under Advanced, check Programs for best performance.
	3) Under Advanced, push Change under Virtual memory.  Then
select System Managed Size and push Set.  Or you can select your own
numbers which are higher than what is displayed as the recommended
(shown at the bottom).
	4) Under Data Execution Prevention, select 'Turn on DEP for
essential...', assuming you have a firewall installed.

Next, I would use a Registry Cleaner and Disk Cleaner.  If you haven't
done this for a while, you'll see a noticeable performance increase.
I've been using Wise Disk Cleaner and Wise Registry Cleaner, both free.

With Excel:
	- Maybe there is a way to change formatting for the entire
workbook at once instead of looping through 200 sheets.  
	- Perhaps you could format one sheet in code and then copy it
200 more times - which might be faster.
	- Perhaps you could format the sheet(s) first, and then create
the embedded chart.

Without Excel:
	In the code you displayed what you're doing is essentially page
setup for the header and footer.  I've been use MSGraph charts for quite
a while now.  An MSGraph chart is an object on an Access Report.  You
can put the chart on the page wherever you want and what size you want.
You can put more than one chart on a report, or you can mix a chart
object with normal report content.  This is a pretty nice way to go
because once you design the chart & report, you can loop through a
dataset with different parameters very quickly, and print the reports or
export them as snapshots and programmatically email them out.

HTH!
Dan

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