Results 1 to 13 of 13
  1. #1
    Lounger
    Join Date
    Nov 2001
    Location
    MI, USA
    Posts
    33
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default page setup (Excel 2000)

    This is a two part question.

    1) I want the default spreadsheet to include formatted header and footer content, i.e. File Name, Page X of Y, Tab Name, Author. In Word, this isn't a problem since you only have to edit the normal.dot template as applicable.

    The kicker is that I need the *Create Date*, not TODAY's date value which is what you would get using Excel's page setup. I would imagine that this would require some scripting.

    2) Since we get/send a lot of excel attachments, how about a macro or two to;
    A) force Excel to open in the page view rather than normal--I hate printing and discovering that print area wasn't set and Excel wants to print more pages than necessary, and;
    [img]/forums/images/smilies/cool.gif[/img] insert a preformatted header & footer to files, so that printouts include filename, author, Page X of Y etc. FWIW, I tried macro-recording and have a functional but bloated macro that is activated by a custom icon in the toolbar.

    In this case, I think I would want this to be a function of print, so that even from Outlook, if I right-click the attachment and select print, all of my required info (see Item 1) would be printed without changing or resaving the original file.

    Would these solutions best be attached to the PERSONAL.XLS, even though the file is not present in the default Excel 2000 setup? I want to be able to install them painlessly to everyone's computer throughout our company. THX in advance.

    AJF

  2. #2
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Default page setup (Excel 2000)

    AJF,

    This is more than a two part question. This will maybe not be a complete answer to your multiple questions. I think you should create a new Excel template, with some macros attached. To count the total number of pages to be printed, you should use an old Excel4 macro (). To put the files name in header or footer, see <A target="_blank" HREF=http://www.wopr.com/cgi-bin/w3t/showthreaded.pl?Cat=&Board=xl&Number=52035&Search= true&Forum=xl&Words=fullname&Match=Entire Phrase&Searchpage=0&Limit=25&Old=allposts&Main=520 29>this thread.

  3. #3
    Lounger
    Join Date
    Nov 2001
    Location
    MI, USA
    Posts
    33
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Default page setup (Excel 2000)

    Hans,

    Maybe I better clarify the two problems. We have about 80 people in our company and frankly a lot of them haven't gone beyond simple formatting such as font style, color etc. That is true of most of our customers that we deal with as well. I've inherited an office with a lot of paper based files that I can't even match with existing digital files. I blame Micro$oft, in part, for not making this a default feature in that all printouts include in the header/footer area, such details as filename, tabname, author and Page X of Y.

    Creating a template is fine for forms etc, but I want the default or normal template to include this information, so that anytime anyone creates a new spreadsheet, this stuff is already there. This wasn't a problem with Excel 97, but in Excel 2000, apparently there is no default template. If I look at the properties in FILE | NEW, the default workbook is *0 Kb* and has no properties, so where is the actual template for that? If I can find that, then I have no problems setting it up and then getting that version on everyone's computer. So that from now on, anything we print will always have what I consider to be essential file information.

    Aside from that internal implementation, the second problem concerns the attachments we receive. Currently, I have to open each file, look at it, run my clunker macro (see below which is attached to PERSONAL.XLS and run by a custom icon--but not everyone has Personal.xls) to automatically populate the footer and headers with the essentials before I print them out. I want a macro that will run even if I right-click from explorer or Outlook and select PRINT. This way, anytime anyone in our company prints a file, regardless of how, it will automatically print the essentials.

    THX again
    AJF

    MY CODE:
    Sub CompanyPageSetupStyle()
    '
    ' PageSetup Macro
    ' Macro recorded 9/14/2001 by AJF
    '
    ActiveWindow.View = xlPageBreakPreview
    With Application
    ' In the event that this folder is moved, is it possible to have it find this folder regardless of Drive?
    .DefaultFilePath = "T:Company Documents"
    End With
    With ActiveSheet.PageSetup
    .CenterHeader = "&""StarTrek Film BT,Bold Italic""&12&A"
    .LeftFooter = "&""StarTrek Film BT,Bold""&9Company Confidential"
    ' How would I insert the original Author's name in the LeftFooter after a line break and change the font to size 8, Italics and Not Bold?
    .CenterFooter = "&""StarTrek Film BT,Regular""&8&F"
    .RightFooter = "&""StarTrek Film BT,Regular""&9Page &P of &N"
    .LeftMargin = Application.InchesToPoints(0.4)
    .RightMargin = Application.InchesToPoints(0.4)
    .TopMargin = Application.InchesToPoints(0.7)
    .BottomMargin = Application.InchesToPoints(0.7)
    .HeaderMargin = Application.InchesToPoints(0.3)
    .FooterMargin = Application.InchesToPoints(0.3)
    .PrintQuality = 600
    .CenterHorizontally = True
    .CenterVertically = False
    .PaperSize = xlPaperLetter
    .FirstPageNumber = xlAutomatic
    .Order = xlOverThenDown
    End With
    End Sub

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Default page setup (Excel 2000)

    Set up a workbook with all of the defaults that you want, and then store this workbook in your XLStart directory with the name Book.xls. Whenever you open a new workbook, it will have all of the defaults and formatting in this workbook. If you want default formatting for worksheets added to a workbook, create another workbook with one worksheet in it formatted like you want and store it in XLStart with the name Sheet.xls.
    Legare Coleman

  5. #5
    Lounger
    Join Date
    Nov 2001
    Location
    MI, USA
    Posts
    33
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Default page setup (Excel 2000)

    I don't understand. Why would Excel format a sheet based on Sheet.xls or a workbook on book.xls?

    Also which XLStart folder? I have 2, one in Program FilesMicrosoft OfficeOffice and one in WindowsApplication DataMicrosoftExcel (where my personal.xls resides).

    I tried your suggestion in both folders and still have nothing when reopening Excel, yet the original files retained the format page view and header/footer formats. Both in closing or hiding the new files. The only thing that happens is that when I close these files, they reopen as themselves.

    THX
    AJF

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Default page setup (Excel 2000)

    Book.xls and Sheet.xls are Excel's version of Normal.dot. That is what it uses to base the default workbook settings on.

    Where XLStart is depends on the combination of XL version and Windows version. XLStart for my XL 2000 on Win 2000 is in:

    Cocuments and SettingsLegare ColemanApplication DataMicrosoftExcelXLStart

    If you have two of them, then you must have had multiple versions of Excel and/or Windows installed on the machine. You Can also have a secondary startup directory defined on the General Tab under Tools/Options. Any workbook put into XLStart should be opened automatically when you start Excel. So, if you put a workbook (any workbook) into your XLStart directories, it should open. Try moving one around until you find the directory where it will automatically open.
    Legare Coleman

  7. #7
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Default page setup (Excel 2000)

    Isn't it book.xlt?

    Cheers
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  8. #8
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Default page setup (Excel 2000)

    It appears to be either, and if you have a preferred layout and style, you can use personal.xls to set them instead, which is what I do.
    -John ... I float in liquid gardens
    UTC -7ąDS

  9. #9
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Default page setup (Excel 2000)

    I think that either one works.
    Legare Coleman

  10. #10
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Default page setup (Excel 2000)

    <hr>If you have two of them, then you must have had multiple versions of Excel and/or Windows<hr>
    Not neccesarily. My machine is a fresh rebuild (i.e. reformatted hard drive) of 2k/2k and book.xls/book.xlt works from either location.

    They do, as noted, do the same job. However, I believe the difference of the four options currently on offer here is that only C:Program FilesMicrosoft OfficeOfficeXlstartbook.xlt will display the Save As dialog and default to your default directory. The other three options will just save over themselves - leastways, that's how it works for me. This difference between xls/xlt certainly holds in 97 but I can't offer any advice on XP.

    There is also another place I've just found whilst doublechecking the above - if you open a new book and Save As, and then select file type template, Excel automatically kicks you to Cocuments and SettingsYour NameApplication DataMicrosoftTemplates - but I don't know what saving in this folder does as I haven't played around with it. There is already a book.xlt here on my system, and I assume that this is the default that the system uses if you haven't specified your own.

  11. #11
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Default page setup (Excel 2000)

    If you have multiple XLStart directories, and have only installed Windows once and Excel once, then the only other thing that I can think of is that you have installed patches and/or service packs that moved XLStart. As far as I know, Excel will use only one XLStart directory, unless you have a second one assigned as the Alternate Startup directory in Tools/Options.

    I just happened to think, the best way to figure out which XLStart directory Excel thinks you should use is to find out where your Personal.xls file is located. Excel will put that file into what it thinks should be the XLStart directory. If you don't have a Personal.xl file, then record a macro and tell Excel to store it in your Personal file, and Excel will create one for you.

    The Templates directory is something totally different. That is where the templates that are available to you when you select New from the File menu are stored. The files in this directory are not loaded automatically when you start Excel, and Book.xls and Sheet.xls will not work as described previously if stored there.
    Legare Coleman

  12. #12
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Default page setup (Excel 2000)

    <hr>The Templates directory is something totally different<hr>
    Yes. I just did a search on book.xl* and forgot to turn my brain on - not that that would have helped much!

    It's highly possible that the drive images applied to my machine come with service packs to go. I wouldn't have a clue about that!

    but the main point I was making, I'll stand by. Using .xlt means you don't save over the original file, whilst using .xls means you either have to save it as a read only file or remember to hit Save As.

  13. #13
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Default page setup (Excel 2000)

    You are correct, and I had not noticed that difference. For the reason you cited, it is much better to make it Book.xlt.
    Legare Coleman

Posting Permissions

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