Forum Discussion
Remove File Extension in Custom Header in an Excel Sheet
When I insert file name as using &[File] in the custom header, the file extension ".xlsx " also gets added & printed. How can I display & print an Excel sheet without file extension?
8 Replies
aredev I know I'm late to the party and you've already forgotten that you had this problem at one time, but I'm really posting for others who may be searching for this issue, as I was today. If you want the file name in the header, you could use the file name as the sheet name (without the extension) and then use the sheet name as your header. Much easier. The use of the marco is way above my pay grade. Ms. Hawighorst's answer is extremely knowledgeable, and I wish I could maneuver Excel like she clearly can. Hat's off to her. However, for the plebes who aren't at that level, this will work wonders.
Hello,
what you describe can only be done with VBA. You need to add this code to the ThisWorkbook module:
Private Sub Workbook_BeforePrint(Cancel As Boolean) ActiveSheet.PageSetup.CenterHeader = UCase(Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 5)) End SubHello Ingeborg, Thanks
Can I use this code for all types of workbooks, be it .xlsx or .xltx? Do I need to save the workbook as .xlsm, i.e as macro-enabled workbook? If so, how can use .xlsm workbook as a template?
Hello,
if you use this approach, the workbook must be saved as a macro-enabled workbook, i.e. with either the .xlsm or .xlsb file extension.
You can create a template that contains this macro by saving the file as a macro-enabled template with the .xltm extension.
When I insert file name as using &[File] in the custom header, the file extension ".xlsx " also gets added & printed. How can I display & print an Excel sheet without file extension?
