Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    Birmingham, West Midlands, United Kingdom
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    macro problem, XL2007 (2007)

    I have a remote user who's 'blessed' with Excel 2007.
    He needs to read and edit a workbook, produced in xl2003, containing a simple macro.
    2 issues:
    1. how does he adjust the security level to something sensible ie the 'Medium' setting in xl2003 and make this setting stick?
    2. Having eventualy persuaded xl2007 to accept the macro, it fails with this cheerful message:
    'Runtime error 1004. Unable to set paper size property of the page setup class.'
    All the macro does is copy a range as a picture, pastes it into a new workbook, and sets it up so that it prints on one landscape A4 page.
    The lump of code concerned is this:
    .PrintHeadings = False
    .PrintGridlines = False
    .PrintComments = xlPrintNoComments
    .PrintQuality = 600
    .CenterHorizontally = False
    .CenterVertically = False
    .Orientation = xlLandscape
    .Draft = False
    .PaperSize = xlPaperA4
    .FirstPageNumber = xlAutomatic
    .Order = xlDownThenOver
    .BlackAndWhite = False
    .Zoom = False
    .FitToPagesWide = 1
    .FitToPagesTall = 1
    .PrintErrors = xlPrintErrorsDisplayed

    I am unfamilar with Office 2007, but would be surprised to think that it was being asked to do something it can't do.
    Comments, please?

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: macro problem, XL2007 (2007)

    1: Macro security has changed with 2007. Tell him he can assign "trusted folders". Files stored in those folders will have macros enabled by default:

    Office button, Excel options, Trust center tab, Trust center settings button, Trusted locations tab, add new location button.

    2. The xlPaperA4 error isn't tied to Excelo 2007, but rather to the installed default printer. On my system for example, the printQuality line errored out on Excel 2003 and on Excel 2007 (on virtual machine) it worked perfectly, just as the Papersize line.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    Birmingham, West Midlands, United Kingdom
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: macro problem, XL2007 (2007)

    Thank you very much, Jan Karel.

Posting Permissions

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