macro problem, XL2007 (2007)

Topic

New Reply

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?

Reply | Quote

Replies

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.

Reply | Quote

Thank you very much, Jan Karel.

Reply | Quote