Darryl Collins
Darryl.Collins at coles.com.au
Sun Sep 7 06:01:39 CDT 2008
Yep - that is definately a good option, although it does depend on the end user having access to the template - which is not always possible. I have having a look at the code now and it creates a new sheet for each output, so a single sheet template that is replicated could work, early days looking at this so....
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Gustav Brock
Sent: Sunday, 7 September 2008 8:53 PM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] Building a spreadsheet from Access ... slow
segment ofcode
Hi Stephen and Darryl
Another option is to use a template - with all printer settings and formatting ready - for the new worksheet
>>> Darryl.Collins at coles.com.au 07-09-2008 06:52 >>>
Hi stephen,
You can just send me the code module if you like and I can have a look at it from from there. If I understand this correctly you are calling a new Excel workbook and populating and setting it up from Access (all the work is being from Access, rather than running an Excel code module from Access(?).
Yep, still interested in having a look. Just send the code rather than your whole database and I will see if I can help.
cheers
Darryl.
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Stephen
Sent: Sunday, 7 September 2008 12:12 PM
To: Access Developers discussion and problem solving
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
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
This email and any attachments may contain privileged and confidential information
and are intended for the named addressee only. If you have received this e-mail in
error, please notify the sender and delete this e-mail immediately. Any
confidentiality, privilege or copyright is not waived or lost because this e-mail
has been sent to you in error. It is your responsibility to check this e-mail and
any attachments for viruses. No warranty is made that this material is free from
computer virus or any other defect or error. Any loss/damage incurred by using this
material is not the sender's responsibility. The sender's entire liability will be
limited to resupplying the material.