[davidgn]

Hi,
I am in some major need of help.

I'd like a macro -- actually a plug in/add-in if that can be set up -- that finds the maximum DPI print quality setting of a worksheet within an Excel workbook (this is the setting under File | Page Setup | Page tab | Print Quality), and will take that max value and make it the default DPI setting for all of the pages in that same workbook.

At the same time, I'd like to have a custom footer placed on all of the pages of the workbook that reads something along the lines of "My footer text - Page x of y".

If possible, I'd like this plug-in to run upon saving of the Excel file.

Thanks very much!

[slurpee55]

The maximum print quality is not a function of Excel, rather, it is tied specifically to the printer you have listed as your default printer (or, if you only have one printer, well, that is it.)
Excel merely draws that information from your printer driver. If you always want to print at the highest dpi, go into your printer driver and set it at that.
As for a universal footer, the easiest way is really to click on the first worksheet, hold down shift, click on the last worksheet. That will select all the sheets. Then go to View, Header and Footer, either select the dropdown that reads "Page 1 of ?" and then enter your footer text in front of that in the custom footer, or enter "My footer text - Page &[Page] of &[Pages]" in the empty box of the footer.

[davidgn]

Thanks very much for your time & reply. A couple of follow-up notes that I didn't clarify in my first post...

I realize that the DPI setting is pulled from my printer. But I'm looking to apply this type of macro to several different users, each of whom may have a different printer with a different print setting. Instead of having everyone set their settings exactly the same, it would be easier to have a macro do this for us.

Same holds true of the footer - it will be used by many, so having everyone do it manually isn't the best option (though it certainly is a fall-back option, if need be).

Thanks again.

[slurpee55]

Are you talking about different users on different PCs also? Or different users logging in to the same PC? Or different users logged in the same on the same PC?

[davidgn]

Diff. users on diff PCs also - good question! Thanks again...

[slurpee55]

Okay, this is a crosspost (Exactly the same wording):
http://answers.yahoo.com/question/in...1175532AAmjOKj
and you got a response and replied
Awesome - fast response, worked perfectly!
So why are you wasting my time?

[davidgn]

Sorry - I'm not wasting anyone's time, I hope - I posted that "Awesome..." response before I fully tested, and couldn't undo my answer.

That macro worked, but only for one workbook. I would like one that can be used as a plug-in or add-in, applied to multiple users on multiple PCs.

[slurpee55]

That macro should work on every PC - just a matter of installing it....but let's see what anyone else has to say here.

[computerman29642]

If I am understanding clearly, the macro you have works just like you want. Is this correct?

If so, the easiest thing to do is take a blank Excel file with the macro loaded, and create a Personal.xls file. This will enable any user that uses any PC to run the macro (once added to the PC).

NOTE: That the Personal.xls will have to be added to each PC (only once though).

[slurpee55]

I suppose
a) if the computers are all networked and
b) you are the administrator of the network
it might be possible for someone to write a batch file that would load the macro onto all machines - but that is just a hunch.

[davidgn]

Thanks, guys. If I create a personal.xls file, I can ask that all users install that, that's not a problem. No need to push it out to all users via the network. But can you explain how the personal.xls file works? Will they need to physically run the macro, or will it run automatically on closing the file (as it does now)? Where should the personal.xls file be stored?
Thanks very much-

[computerman29642]

What version of Excel are you running? Do all users run the same version?

[computerman29642]

You do need to keep in mind that the Personal.xls applies to all Excel workbooks. So, if you have it run when the workbook closes, it will run when all workbooks are closed.

Will this present a problem?

EDIT: If so, you could add a toolbar button that will run the macro.

[slurpee55]

If you are using 2003, you would create a file with the macro, then place it in
C:\Program Files\Microsoft Office\OFFICE11\XLSTART\Personal Macro
The workbook containing the macro will start up each time Excel is opened (and you will get an error message if you try to open Excel twice at the same time - not two or more workbooks, that is fine, just starting Excel and then starting it again with the other one running).
Personally, I have hidden the two I have open when I start Excel so that I don't accidentally change them - or worse, delete them. Just open the file, then go to Window, Hide, then close Excel.

[slurpee55]

CMan, could you alter the macro so that it ran upon opening of Excel automatically?