Modifying Header (&[file]) format

  • Thread starter Thread starter candyman200
  • Start date Start date
C

candyman200

I'm trying to make a header for a "low-tech" user that uses part of th
file name. For example - January-2004.xls woud be in the header a
January-2004. Is there any way to exclude the suffix?

Thanks in advance
 
Hi
one way would be to use VBA and insert this name in the header. Put the
following code in your workbook module:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim wkSht As Worksheet
For Each wkSht In Worksheets
With wkSht.PageSetup
.CenterHeader =
left(activeworkbook.Name,len(activeworkbook.Name)-4)
End With
Next wkSht
End Sub
 
I'm a novice using VB in Excel ... I can add the code in the Module, bu
I need more help in "calling" the script from the custom header. I'
like to email off line on this if possible. Thank
 
Try the SUBSTITUTE function on the cells and change .xls
to "" (which is nothing) that might do it
 
Hi
you can mail me your sheet (frank[dot]kabel[at]freenet[dot]de). Though
it is quite easy to do it by yourself:
- righclick on the Excel icon left to the menu entry 'File'
- choose 'Code' in the appearing context menu
- paste the code in the appearing VBA module (this is the workbook
module)
- close the editor and save your workbook

That's it, you don't have to do anything els in the pagesetup dialog
(the macro is invoked automatically)
 
Back
Top