Results 1 to 9 of 9
  1. #1
    New Lounger
    Join Date
    Mar 2001
    Location
    Chicago, Illinois, USA
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel PageFormatting Macro

    I have a macro that does page formatting for some reports in Excel.

    For some reason it runs extremely slow on some machines, and fine on others.

    I have tested it on Win 98 and 2K. All machines involved have the same office setup.

    4 machines tested: 1 Win 98 & 1 Win 2K run fine, 1 Win 98 & 1 Win 2K run slow. ??

    Any help/suggestion will be greatly appreciated.

    Thanks,
    RL

  2. #2
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel PageFormatting Macro

    An observation (not necessarily your solution):

    If you have anti-virus software running, you can expect inordinate delays whenever VBA macros start. I have not migrated to Word 2000 and signed projects yet, so don't know if that bypasses the virus check. Disabling "AutoProtect" (Norton's) does not help with their NAV 2000 version. Maybe NAV 2001 is better at disabling the macro virus checking. But of course, you must heed the warnings about disabling anti-virus software.
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

  3. #3
    2 Star Lounger
    Join Date
    Dec 2000
    Location
    eastern Connecticut, Connecticut, USA
    Posts
    113
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel PageFormatting Macro

    Another suggestion...

    The Print drivers being used may make a big difference. Are the slow machines using a different printer?

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel PageFormatting Macro

    When you say "page formatting" do you mean that is is using the PageSetup property to set page formats? If so, using that property on some Operating Systems is VERY slow. I don't know why, and I unfortunately don't know any way around it. One thing that does seem to help a little (but not a lot) is to use With/End With to define the PageSetup object like this:

    <pre> With ActiveSheet.PageSetup
    .CenterHeader = "My Report"
    .CenterFooter = Format(Date(), "dd mmm yyyy")
    End With
    </pre>


    How slow this is seems to be dependant on which OS you are running on, and what the printer is.
    Legare Coleman

  5. #5
    New Lounger
    Join Date
    Mar 2001
    Location
    Chicago, Illinois, USA
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel PageFormatting Macro

    here's a sample of the Code being used:
    Private Sub myPageSetup()
    With ActiveSheet.PageSetup
    .CenterHeader = ""
    .RightHeader = "&D &T"
    .LeftFooter = ""
    .CenterFooter = ""
    .RightFooter = "Page &P of &N"
    .LeftMargin = Application.InchesToPoints(0.5)
    .RightMargin = Application.InchesToPoints(0.5)
    .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
    ' .PrintQuality = 600
    .CenterHorizontally = True
    .CenterVertically = False
    .Orientation = xlPortrait
    .Draft = False
    .PaperSize = xlPaperLetter
    .FirstPageNumber = xlAutomatic
    .Order = xlDownThenOver
    .BlackAndWhite = False
    .Zoom = False
    .FitToPagesWide = 1
    .FitToPagesTall = False
    End With
    End Sub

    I have 2 Dell OptiPlex (1Ghz, 256MB ram) machines using the same printer.. one is fine the other is slow.

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel PageFormatting Macro

    Yes, that is exactly the kind of code that is VERY slow. If those two computers are running the same OS, I don't know why one would be slow and not the other.
    Legare Coleman

  7. #7
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel PageFormatting Macro

    I don't know if you can do this. I've had that situation, where it is extremely slow; so, instead of formatting individual sheets, I copied an existing formatted (hidden) sheet onto a new worksheet. That turned out heaps faster.

    Differences in speed may be to do with printer drivers?
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  8. #8
    New Lounger
    Join Date
    Mar 2001
    Location
    Chicago, Illinois, USA
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel PageFormatting Macro

    I'll give that a try.
    Also, I found that doing the page formatting with an excel 4 macro command and using the ExecuteExcel4Macro is much faster. I almost hate to do it that way but if it works....

  9. #9
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel PageFormatting Macro

    I found an old message on CompuServe from Bill Manville on this subject, and the workaround that he recommended was to use an Excel 4 macro. No one seemed to know why this is so slow.

    I did think of one thing that could cause one of your machines to be slow and not the other. Check the CMOS setup settings for the LPT port (is that how the printers are attached?) both machines, or the network settings if the printers are attached through a network. I had a situation a while back where a user of one of my applications reported that his printing was very slow. Changing his network from Full Duplex to Half Duplex solved his problem.
    Legare Coleman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •