Modifying Header (&[file]) format

[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

 

[Frank Kabel]

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

 

[candyman200]

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

 

[ed griffis]

Try the SUBSTITUTE function on the cells and change .xls
to "" (which is nothing) that might do it

 

[Frank Kabel]

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)