Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Business Applications Business Applications
Search Search
Search for:
Tech Support Guy > > >

Solved: Excel Macro help needed, please!


(!)

davidgn's Avatar
davidgn davidgn is offline
Junior Member with 13 posts.
THREAD STARTER
 
Join Date: Feb 2009
11-Feb-2009, 09:02 PM #1
Solved: Excel Macro help needed, please!
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's Avatar
Computer Specs
Member with 7,837 posts.
 
Join Date: Oct 2004
Location: Southwest Iowa....
Experience: Currently stupid...
12-Feb-2009, 11:05 AM #2
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's Avatar
davidgn davidgn is offline
Junior Member with 13 posts.
THREAD STARTER
 
Join Date: Feb 2009
12-Feb-2009, 11:21 AM #3
Thanks & Follow up
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's Avatar
Computer Specs
Member with 7,837 posts.
 
Join Date: Oct 2004
Location: Southwest Iowa....
Experience: Currently stupid...
12-Feb-2009, 11:27 AM #4
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's Avatar
davidgn davidgn is offline
Junior Member with 13 posts.
THREAD STARTER
 
Join Date: Feb 2009
12-Feb-2009, 11:28 AM #5
Yes...
Diff. users on diff PCs also - good question! Thanks again...
slurpee55's Avatar
Computer Specs
Member with 7,837 posts.
 
Join Date: Oct 2004
Location: Southwest Iowa....
Experience: Currently stupid...
12-Feb-2009, 11:29 AM #6
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's Avatar
davidgn davidgn is offline
Junior Member with 13 posts.
THREAD STARTER
 
Join Date: Feb 2009
12-Feb-2009, 11:33 AM #7
Not quite perfect
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's Avatar
Computer Specs
Member with 7,837 posts.
 
Join Date: Oct 2004
Location: Southwest Iowa....
Experience: Currently stupid...
12-Feb-2009, 11:49 AM #8
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's Avatar
Computer Specs
Member with 2,888 posts.
 
Join Date: Dec 2007
Location: HERE OR THERE?!?!?!
Experience: Intermediate
12-Feb-2009, 05:34 PM #9
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's Avatar
Computer Specs
Member with 7,837 posts.
 
Join Date: Oct 2004
Location: Southwest Iowa....
Experience: Currently stupid...
12-Feb-2009, 05:40 PM #10
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's Avatar
davidgn davidgn is offline
Junior Member with 13 posts.
THREAD STARTER
 
Join Date: Feb 2009
12-Feb-2009, 05:45 PM #11
Great
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's Avatar
Computer Specs
Member with 2,888 posts.
 
Join Date: Dec 2007
Location: HERE OR THERE?!?!?!
Experience: Intermediate
12-Feb-2009, 05:47 PM #12
What version of Excel are you running? Do all users run the same version?
computerman29642's Avatar
Computer Specs
Member with 2,888 posts.
 
Join Date: Dec 2007
Location: HERE OR THERE?!?!?!
Experience: Intermediate
12-Feb-2009, 05:51 PM #13
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's Avatar
Computer Specs
Member with 7,837 posts.
 
Join Date: Oct 2004
Location: Southwest Iowa....
Experience: Currently stupid...
12-Feb-2009, 05:57 PM #14
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's Avatar
Computer Specs
Member with 7,837 posts.
 
Join Date: Oct 2004
Location: Southwest Iowa....
Experience: Currently stupid...
12-Feb-2009, 05:58 PM #15
CMan, could you alter the macro so that it ran upon opening of Excel automatically?
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
WELCOME TO TECH SUPPORT GUY!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.


Tags
add-in, add-ins, excel, macro

(clock)
THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools


WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑