Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Oct 2003
    Location
    Birmingham, England
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    simplify and generalise macro (97sr2(n))

    <P ID="edit" class=small>(Edited by HansV on 17-Nov-03 01:05. Moved long macro code to attachment)</P>The following macro derives from the 'point and shoot' method, using the recorder, the only way I really know.
    Its function is to copy a range as a picture to a new workbook, give it a white background, and size it to fit on one portrait A4 page, with fairly generous margins.
    [The purpose of the 'as picture' bit is to make it hard to edit (it's about other people's money), and to reduce the file size, as the original file includes a quite big lookup table.]
    The macro is triggered by a button.
    It works.
    But very slowly (several seconds, on a P3 866.)
    My questions -
    1. What safe simplifications can I make to the code? I've already rubbed out quite a lot, which I recognise to be redundant. Plus the defeat screenupdating trick.
    2. Can the macro be generalised to be proof against the original range's being increased, by adding one or more rows before hitting the macro button?
    3. Can the new workbook, the one with the sized, whitened picture, automatically be named with the contents of cell K18, a unique reference, and saved to the location
    c:Credit Unioncollectionsheets
    Thank you very much!
    Makes a change from railroading through the Rockies, Arizona, or wherever!
    The bloated code: see attachment.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: simplify and generalise macro (97sr2(n))

    I think that the main culprit is changing the page setup, this is slow in Excel 97.

    No line is default, and transparency for the fill color is 0 by default. So you can omit the code for those.

    If your printer(s) are set to A4 and portrait orientation, you can leave out the commands for setting those.
    The values you set for page margins are almost the default settings (on my system, top and bottom margins are 2.5 cm = 1 inch, left and right margins 2.0 cm = 0.8 inch and header and footer margins are 1.25 cm = 0.5 inch), so you might as well omit the code to set the margins.
    And headers and footers are blank by default, so you might omit the code to set them.
    You can probably also omit the settings for comments and print quality.
    This only leaves the "fit to page" settings.

    This could be the modified code. It acts on the selected range, whatever that is, and saves the new workbook:

    Sub copy_picture_to_new_file()
    Dim strFilename As String
    strFilename = Range("K18")

    Application.ScreenUpdating = False

    Selection.Copy
    Workbooks.Add

    With ActiveSheet.Pictures.Paste.ShapeRange.Fill
    .Solid
    .ForeColor.SchemeColor = 9
    End With

    With ActiveSheet.PageSetup
    .BlackAndWhite = True
    .Zoom = False
    .FitToPagesWide = 1
    .FitToPagesTall = 1
    End With

    ActiveWorkbook.SaveAs Filename:="C:Credit Unioncollectionsheets" & strFilename
    Application.ScreenUpdating = True
    ActiveWindow.SelectedSheets.PrintPreview
    End Sub

  3. #3
    Lounger
    Join Date
    Oct 2003
    Location
    Birmingham, England
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: simplify and generalise macro (97sr2(n))

    That's quite a lot quicker, Hans - as well as doing the 'save as' bit - but regretfully gives the wrong result, namely an undersized grey blob.
    See attached. (And a picture of the original, with bogus data in it.)
    I've seen this 'Dim' business lots of times on the examples here - what's it all about, 10 words or less?

  4. #4
    Lounger
    Join Date
    Oct 2003
    Location
    Birmingham, England
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: simplify and generalise macro (97sr2(n))

    I see we can only add one file. So here are both of them zipped together (to fool the internet into thinking that it's only one!)

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: simplify and generalise macro (97sr2(n))

    1. I have no idea why you get an undersized gray blob, the code works OK for me. I'm using Excel 2002, but I didn't add anything that is specific to that version.

    2. Dim declares a variable, in this case a string (text) variable. I use it to pick up the file name in cell K18; it's best to do this in the beginning because the source workbook is still the active workbook then. If you have ticked "Require variable declaration" in Tools | Options... in the Visual Basic Editor, you *must* declare all variables you use. This avoids problems with undefined variables.

  6. #6
    2 Star Lounger
    Join Date
    Sep 2002
    Location
    Birmingham, England
    Posts
    123
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: simplify and generalise macro (97sr2(n))

    Thanks for the Dim bit, Hans.
    Will re-paste in the code and try again and let you know.

Posting Permissions

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