1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Excel Macro help needed, please!

Discussion in 'Business Applications' started by davidgn, Feb 11, 2009.

Thread Status:
Not open for further replies.
Advertisement
  1. davidgn

    davidgn Thread Starter

    Joined:
    Feb 11, 2009
    Messages:
    13
    Hi,
    I am in some major need of help. :confused:

    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!
     
  2. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    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.
     
  3. davidgn

    davidgn Thread Starter

    Joined:
    Feb 11, 2009
    Messages:
    13
    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.
     
  4. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    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?
     
  5. davidgn

    davidgn Thread Starter

    Joined:
    Feb 11, 2009
    Messages:
    13
    Diff. users on diff PCs also - good question! Thanks again...
     
  6. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
  7. davidgn

    davidgn Thread Starter

    Joined:
    Feb 11, 2009
    Messages:
    13
    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.
     
  8. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    That macro should work on every PC - just a matter of installing it....but let's see what anyone else has to say here.
     
  9. computerman29642

    computerman29642

    Joined:
    Dec 4, 2007
    Messages:
    2,895
    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).
     
  10. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    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.
     
  11. davidgn

    davidgn Thread Starter

    Joined:
    Feb 11, 2009
    Messages:
    13
    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-
     
  12. computerman29642

    computerman29642

    Joined:
    Dec 4, 2007
    Messages:
    2,895
    What version of Excel are you running? Do all users run the same version?
     
  13. computerman29642

    computerman29642

    Joined:
    Dec 4, 2007
    Messages:
    2,895
    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.
     
  14. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    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.
     
  15. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    CMan, could you alter the macro so that it ran upon opening of Excel automatically?
     
  16. Sponsor

As Seen On
As Seen On...

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.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/799892