Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    May 2003
    Posts
    69
    Thanks
    1
    Thanked 0 Times in 0 Posts

    File name in header (2000)

    I want to put the filename of the spreadsheet in the header to print on every page, but without the ".xls" at the end. How can I get rid of this? (It used to leave it off, not it's there.)
    Also, is there a way to have the header (or footer) read from a certain cell in the spreadsheet and pull the date from that cell?

    Thanks.

    -Troy

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: File name in header (2000)

    You need a bit of VBA code for this:
    - Activate the Visual Basic Editor (Alt+F11)
    - Double click the ThisWorkbook node of your workbook in the Project Explorer (the Windows Explorer-like treeview on the left hand side)
    - Copy and paste the following code into the ThisWorkbook module:

    Private Sub Workbook_BeforePrint(Cancel As Boolean)
    Dim strName As String
    Dim wsh As Worksheet
    strName = Me.Name
    strName = Left(strName, Len(strName) - 4)
    For Each wsh In Me.Worksheets
    With wsh.PageSetup
    .LeftHeader = strName
    ' Modify cell reference as needed
    .RightHeader = wsh.Range("A3")
    End With
    Next wsh
    Set wsh = Nothing
    End Sub

    - Modify as needed. You can use .LeftHeader, .CenterHeader, .RightHeader, .LeftFooter, .CenterFooter and .RightFooter.

  3. #3
    Star Lounger
    Join Date
    May 2003
    Posts
    69
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: File name in header (2000)

    Thank you. This works, but I cannot format the header. It is printing in small text. The changes I make to the font in the header do not save. This string becomes the title of each page, so it should be big, bold font. Can this be formatted?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: File name in header (2000)

    To print the right header (for example) in 18 point size, use:

    .RightHeader = "&18 " & wsh.Range("A3")

    To make the font bold, add &B:

    .RightHeader = "&B&18 " & wsh.Range("A3")

Posting Permissions

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