=CELL("filename",A1) [doesn't make any difference what cell, as long as it is in the worksheet of interest]
Even though the syntax allows omitting a cellname reference, you must include it; otherwise, for instance, if you select multiple sheets, all of the grouped sheets will get the information of the activesheet or as updated during a recalculation instead of the sheet where the formula resides.D:\driveM\excel\TAXES\
The crippled form =cell("filename") and other CELL Worksheet Function without a cell reference such as =cell("address") will use the active cell found on the active sheet as the cell reference when entered or recalculated later, which can be useful if you want to see the sheetname and cell address that were last updated, though this is not reliable because a recalculation will change the value to the activesheet or the first sheet in a grouping at the time of recalculation. A recalculation occurs when the workbook is opened. See HELP, Cell worksheet function; for more information.
To see for yourself that the crippled form =cell("filename") is not the current sheet: Enter your formula on two different sheets, on the second optionally use Ctrl+Alt+F9 to do a full recalculation of everything then return to the first sheet and look at your use. If you have multiple workbooks open you could even be referring to another workbook, which you can also test yourself. To be applicable to the current page you must use something like =cell("filename",A1)
« The CELL formulas with "filename" will not work until the file has been saved (#VALUE! error).
If you rename sheet you will not see result until forced with a full recalculation Ctrl+Alt+F9. Reopening of workbook forces full recalculation.
=SUBSTITUTE(SUBSTITUTE(CELL("filename",A1),"[",""),"]"," [") & "]"
Formula will produce incorrect result if the sheetname contains "[" or "]" characters -- Harlan Grove, fortunately PC's (not Macs) do not allow the following characters in a filename: < > ? [ ] : | or *
=SUBSTITUTE(LEFT(LOWER(CELL("filename",A1)),FIND("]",CELL("filename",A1))-1),"[","") & " [" & MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,28) & "]"
« The CELL formulas with "filename" will not work until the file has been saved (#VALUE! error).
Function SheetName() As String
SheetName = Application.Caller.Parent.Name 'in XL97
MsgBox Application.StartupPath -- location of XLSTART, i.e. for personal.xls
Coding Example using a VBA macro: Leo Heuser =fname("filename")
The following is from a Nick Manton (1998-07-18) posting it applies to XL97 and above macro usage. Two additional items added from postings D.McRitchie & C.Pearson (1999-12-17)
Application.Caller.Address 'Cell address Application.Caller.Parent.Name 'Worksheet Name i.e. BigSheet Application.Caller.Parent.Codename 'Worksheet codename i.e. Sheet3 Application.Caller.Parent.Index 'Worksheet index number i.e. 1 as in Worksheets(1) Application.Caller.Parent.Parent.Name 'Workbook Name Application.Caller.Parent.Parent.Parent.Name 'Application Name
&[Path]&[File] inserts the path and file name. In VBA this is entered as &Z&F
This works in Excel 2000 only (uses Reverse String Search):
Function NameOnly(FullPath As String) As String
NameOnly = Mid(FullPath, InStrRev(FullPath, "\") + 1)
This works in previous versions too:
Function NameOnly97(FullPath As String) As String
NameOnly97 = FullPath
Do Until InStr(NameOnly97, "\") = 0
NameOnly97 = Mid(NameOnly97, 2)
Excel 2002 has added &[Path] for custom headers and footers, but for earlier versions of Excel you still must use a macro.
When using macros for headers and footers is easy to forget
about changing font attributes such as pointsize. I like to use
a smaller font in my footers, except possibly for page numbers.
Actually I prefer to use the Excel facilities for page numbers in footers,
but you could include that also in your macro as shown below.
F1 (VBE HELP) --> answer wizard -> Formatting Codes for Headers and Footers
Activesheet.PageSetup.LeftFooter = "&8" & _
LCase(ActiveWorkbook.FullName) & " &A "
Activesheet.PageSetup.CenterFooter = "Page &P of &N" 'like to use 2 spaces between
Following are three examples, install macro of your choice in a Module sheet.
Sub PrintWithFileNameInFooter() With ActiveSheet .PageSetup.LeftFooter = Application.ActiveWorkbook.FullName .PrintOut End With End Sub Sub insertFooter() ActiveSheet.PageSetup.LeftFooter = = Application.ActiveWorkbook.FullName End SubThe following is what I use, it prints the full pathname and sheetname less obtrusively with a smaller font. There are two spaces between each word/number on the page number. The left footer is placed regardless of current content. The right footer is placed only if there is no current right footer. The use of WITH to supply the prefixed wording is considered much more efficient in macros; and so the .LeftFooter is actually ActiveSheet.PageSetup.LeftFooter and the break character (underscore) is actually a blank and underscore to indicate continuation of a single line. I choose not to have it run automatically which I feel might inadvertently wipe out something important in someone else's sheet.
Sub PutFileNameInFooter() 'Documented in http://www.mvps.org/dmcritchie/excel/pathname.htm 'alternative -- lowercase for pathname Sheetname as is within square brackets 'also using an 8 point font, lettercase of Sheetname is unchanged in this 'example because you have control and can change it within the workbookbook 'Use of WITH provide a prefix for names that begin with a period With ActiveSheet.PageSetup .LeftFooter = "&8" & _ LCase(Application.ActiveWorkbook.FullName) & " &A " If .RightFooter = "" Then .RightFooter = "Page &P of &N" End With End SubAbove are three sample macros. I would suggest removing the PrintOut since it also prints out the spreadsheet and I like to make further changes in the footer.
One thing to be said about including .PRINTOUT is that if you move your workbook or worksheet there will be an automatic update of the footer. Hopefully, someday, Microsoft will fix up the headers and footers in Excel for more flexibility, exact margins, pathname, color logos, subtotals, and continued subheadings.
See VBE Help --> Index --> Find --> Formatting Codes for Headers and Footers
Sub allfooters() Dim s As Worksheet Application.ScreenUpdating = False For Each s In ActiveWorkbook.Worksheets With s.PageSetup .LeftFooter = "&8" & LCase(Application.ActiveWorkbook.FullName) _ & " [&A]" .CenterFooter = "Page &P of &N" .RightFooter = "&8 &D &T" End With Next s Application.ScreenUpdating = True End Sub
The following would only change the first sheet in the workbook:
If you want don't want to see an earlier date like 01-Jan-1998 in your footer you want to make sure you are invoking the VBA at print time.
You would install in ThisWorkbook instead of a standard code module.
ThisWorkbook is found at the end of all of your object modules and has this
specific name. To find use (Alt+F11, Ctrl+R) look at the project, look at
"Microsoft Excel Objects" (as opposed to "Modules" and "References"), expand
objects and look for ThisWorkbook at the end.
The quickest way to get to “This Workbook” is to right-click on the Excel icon at the left side of the menu bar, then use “View Code”.
Private Sub Workbook_BeforePrint(Cancel As Boolean) ActiveSheet.PageSetup.LeftFooter = Application.ActiveWorkbook.FullName & " &A" ActiveSheet.PageSetup.RightFooter = Format(now(),"dd-mmm-yyyy") 'UK dates End SubAn Improvement to modify the footers for Grouped Worksheets.
Private Sub Workbook_BeforePrint(Cancel As Boolean) Dim wkSht As Worksheet For Each wkSht In ActiveWindow.SelectedSheets wkSht.PageSetup.LeftFooter = _ "&8" & LCase(Application.ActiveWorkbook.FullName) & " &A " Next wkSht End Sub
Including the above and additional information: (#modify to suit your preferences)
ActiveWorkbook.Fullname does not work in Print Preview (Excel 2000), &F can be use to get the bookname (filename) instead. With Excel 2002 you can use &Z for the full pathname.Something like .RightFooter = "&8 &D &T" would print date (&D) with the user's own Regional settings. The prefix of &8 reduces the fontsize to 8 points, to make sure it doesn't blend in with data. The use of &A includes the sheetname. and will change even if you no longer had the macro, unlike the pathname. You also have .LeftHeader, .CenterHeader, and .RightHeader available. a format of "mmmm dd, yyyy" would print long dates (US format).
Option Explicit Private Sub Workbook_BeforePrint(Cancel As Boolean) 'Documented in http://www.mvps.org/dmcritchie/excel/pathname.htm 'Install in ThisWorkbook in the Project Library of your workbook 'Alt+F11 (switch to VBE), Ctrl+R (object explorer), 'find/install in "ThisWorkbook" under "Microsoft Excel Objects" Dim wkSht As Worksheet For Each wkSht In ActiveWindow.SelectedSheets With wkSht.PageSetup on error resume next 'for Excel 2000 print preview failure .LeftFooter = "&8" & LCase("&F ") & " &A " .LeftFooter = "&8" & LCase(Application.ActiveWorkbook.FullName) _ & " &A " .CenterFooter = "Page &P of &N" ' .RightFooter = "&8 &D &T" .RightFooter = "&8 " & _ Application.UserName & ", " _ & Format(Now(), "yyyy-mm-dd") & " &T" End With Next wkSht End Sub
Use &[Page]+X to adjust page numbers
With Excel 2000 the above no longer works, instead use Page Setup, Page tab, first page number: change the word Auto to the number for your first page. I presume this provides better reuse of your customized headers/footers.
Excel 2002 does provides &[Path] for use in headers and footers.
The situation is that there are some sheets with formulas in all of rows 2 through 3000, but the rows that should be included should only extend down to last entry in Column A. The PrintArea limits the print area it does not extend it so all 256 columns (columns.count) can be included.
If you think this situation actually applies, you should consider why there are formulas on rows that down't need them.
Private Sub Workbook_BeforePrint(Cancel As Boolean) With ActiveSheet on Error Resume Next .PageSetup.LeftFooter = " &F &A" 'works in print preview .PageSetup.LeftFooter = ThisWorkbook.FullName & " &A" .PageSetup.RightFooter = "&8 &D &T" If LCase(ActiveSheet.Name) = "sheet28" Then .PageSetup.PrintArea = .Cells(1, 1).Resize( _ .Range("A" & Rows.Count).End(xlUp).Row, Columns.count).Address End If End With
Desktop Start -- Settings -- Control Panel -- Regional Settings -- Date
I reset my own from mm/dd/yy to mm/dd/yyyy -- in the Short Date area
Sub PutFileNameInFooter() With ActiveSheet .PageSetup.LeftFooter = "&8" & LCase(Application.ActiveWorkbook.FullName) & " &A %quot; End With End SubInclude the desired code in a Module sheet, create one if you don't have one.
To invoke Tools --> Macro --> click on macro line --> Run
To save time you may install a custom button, to invoke the macro.
I have assigned one custom button to include a filename with full pathname in the left footer. Since the toolbars are in effect for all my Excel usage the macro is installed as follows: personal.xls!PutFileNameInFooter
For this macro I choose the yellow smilely face. Additional information on creating a custom button is available.
Bill Manville (Oxford England) offers a solution to automatically include changing the footer when printing is initiated using a Workbook_BeforePrint, event procedure e.g.
Private Sub Workbook_BeforePrint(Cancel As Boolean) ActiveSheet.PageSetup.LeftFooter = Application.ActiveWorkbook.FullName End SubA later XL97 version also from Bill Manville on Jul 31, 1998 lists
Private Sub Workbook_BeforePrint(Cancel As Boolean) Dim oSheet As Object For Each oSheet In ThisWorkbook.Windows(1).SelectedSheets oSheet.PageSetup.LeftHeader = ThisWorkbook.FullName Next End Subadditional lines you could add for a specially formatted date
ActiveSheet.PageSetup.RightFooter = Format(now(),"dd-mmm-yy")last saved date
ActiveSheet.PageSetup.RightHeader = "Saved date " _ & FileDateTime(ActiveWorkbook.FullName)In earlier versions you had to have a macro that sets up the footer and then does the printout. You would use this in place of File / Print:
Sub SetFooterAndPrint() ActiveSheet.PageSetup.LeftFooter = Application.ActiveWorkbook.FullName ActiveSheet.PrintOut End Sub
Private Sub Workbook_WindowActivate(ByVal Wn As Window) ActiveWindow.Caption = ActiveWorkbook.FullName End Sub
But I don't really need additional overhead the following will tell me where I am if I really want to know, as would file, properties.
Sub WhereAmI() MsgBox ActiveWorkbook.FullName End Sub
Display Sheet Statistics from Tom Ogilvy contains a lot more sheet information and could substituted for the above.
A special note on Auto_Open (from a note of Stephen Bullen in Excel-L). If you receive a spreadsheet of dubious origin that might have macro viruses open the spreadsheet with the Shift key engaged to override Auto_Open, continue to hold it down while selecting sheet or module this will ignore them for the time being while you examine content.
Ctrl+: (semicolon) places today's date permanently in a cell. Unchanged by recalculations.
Ctrl+Shift+: (colon) places current time permanently in a cel. Likewise, unchanged by recalculations.
Method to place the last saved date into a cell using the Workbook_BeforeSave event, suggested by Chip Pearson, Apr8, 1998.
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Sheets("Sheet1").Range("A1").Value = Now() End Sub
Insert --> Field --> Document Information --> filename
modify the insert field to appear as below:
Sub Macro12() [a1] = "Sheetname..." For q = 1 To Worksheets.Count [a1].Offset(q, 0).Value = "'" & Worksheets(q).Name Next q End SubAlso see Get All A1 Cells in buildtoc(#GetallA1Cells).
Sorting worksheets alphabetically (sorting the tab names) also see Build Table of Contents (ref SortAllSheets)
Sub SortSheets() 'Bill Manville 1997/09/19 -- http://groups.google.com/groups?oi=djq&ic=1&selm=an_AN=273647350 ' also see Q105069 XL: Macros to Sort Tabs in the Active Workbook Dim I As Long, J As Long For I = 1 To Sheets.Count For J = 1 To I - 1 If UCase(Sheets(I).Name) < UCase(Sheets(J).Name) Then Sheets(I).Move before:=Sheets(J) Exit For End If Next J Next I End Sub
ActiveWorkbook.SaveAs FileName:=ActiveCell.Value 'active cell, or ActiveWorkbook.SaveAs FileName:=Range("A1").value 'value in A1
expression.GetSaveAsFilename(InitialFilename, FileFilter, FilterIndex, Title, ButtonText)
The following example was posted by Harold Staff 2001-09-25.Sub SaveMe() Dim Savepath As Variant Savepath = Application.GetSaveAsFilename("C:\Code\A.xls", _ "Beckys Workbooks (*.xls), *.xls", , "Pick a nice place") If Savepath = False Then Exit Sub 'cancelled! ActiveWorkbook.SaveAs Savepath End SubJohn Walkenbach created a GetDirectory function, as might be used to select a directory for backups in his Tip 29, Selecting a Directory.
Making a directory, Patrick Molloy, programming, 2001-09-26Dim sPath As String sPath = "C:\Testing1" MkDir sPath ThisWorkbook.SaveAs sPath & "\" & "Myfile.xls"
Sub Macro43() 'Multiple paste of header information D.McRitchie 2001-03-01 programming Dim iMax As Long, i As Long 'Long would work now anyway iMax = Range("AX1").Column ' Ax1: COLUMN() shows 50 Range("A1:H9").Select Selection.Copy 'Range("A1").Offset(0, 9).Paste For i = 9 To iMax Step 8 Range("A1").Offset(0, i).PasteSpecial Next i End Sub
This page contains some VBA macros. If you need assistance to install or to use a macro please refer refer to my GetFormula page.
Since this page is primarily on Headers and Footings you might also want to look at
HELP (in VBE) --> headers --> Formatting Codes for Headers and Footers
- Build Table of Contents
- Sheets, working with
- Customizing the title/footer for each page by restarting the printer, so page headings can show content pertaining to the page as printed within a worksheet.
- Logo at top (logoshd) of first page only or entire report.
- Page Breaks and Page Breaks, and Inserting Blank Rows
- Pathname in Headers, Footers, and cells
- Printing with Macros
- Repeating column and row heading information (headcols) in printed report (page setup) or on screen (Window/Freeze Panes).
- Slow Response, particularly topic: Page Setup and speed.
- Q199505 macro may take several minutes if page breaks are visible.
- Change header or footer for each page: How to find the number of pages in a worksheet and how to change the header or footer from page to page, see posting, Myrna Larson, 1999-12-16; and also a bunch of related issues such as the page number of the active cell by various posters.
- Document Property, Username under Tools, General, username used in Cell Comments vs. Authorname as a Document Property under File, Properties, Summary, Author. David McRitchie 2000-01-29. Document properties apply to the entire workbook, not just one sheet.
MsgBox "Current user is " & Application.UserName
MsgBox "Author is " & ActiveWorkbook.BuiltinDocumentProperties("Author")
MsgBox "Last Author is " & ActiveWorkbook.BuiltinDocumentProperties("Last Author")
As a worksheet function: =property("Author")
- Filesize, Dave Peterson, 2002-08-31, (volatile UDF), FileSize = FileLen(FileName)
- Long File Names, How to obtain the long (complete) filename from the 8.3 DOS shorter filename.
- Massive changes to all footings in a workbook by replacement of character strings (2001-09-27), programming, mainly J.E. McGimpsey.
- Page number of the active cell, Find the page number of the activecell (Sub not formula). Laurent Longre 2000-04-21 programming.
- Some postings by Myrna Larson, that determine the page number for the activecell and think there is one somewhere that does it for a column of cells. (but could not find it) AN=626272822 2000/05/23, AN=561640908 1999/12/16
- Page Of Pages in a Cell, Dick's blog, pages in a cell on a worksheet (i.e. Page 3 of 5), without use of VBA.
- JWalk AddPath to Header or Footer requires XL97 (not 5, not 95).
- JWalk Printing a Workbook's Full Path in a Header or Footer
- Determining the Number of Printed Pages
How to determine how many pages will be printed.
- drive letters in use, see C:\Program Files\Microsoft Office\Office\Samples\samples.xls
Please send your comments concerning this web page to: David McRitchie send email comments
Copyright © 1997 - 2004, F. David McRitchie, All Rights Reserved