Excel 2007 : File names--entering into a cell

**623bookends** · 02-12-2012, 12:53 AM

My goal is to automate the entry of the spreadsheet file name into a cell at the bottom of the spreadsheet. The first step is to find out how to automatically obtain the file name without typing it in. I assume I start by going to the cell into which the name is to be entered. Then what???

Thanks.

623bookends

**dilipandey** · 02-12-2012, 01:36 AM

Hi 623bookends,

Welcome to the forum.

By saying "automate the entry of the spreadsheet file name into a cell", do you mean to extract the file name of current file which you have opened on your system... ?
i.e., if I open a workbook "sales.xls" and I want the name "sales" to be automatically extracted in a cell ?
thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

**protonLeah** · 02-12-2012, 07:21 PM

=CELL("filename")

**623bookends** · 02-13-2012, 12:44 AM

Yes!! It works beautifully. Thanks.

Now: can you tell me how to get to the bottom left corner of the first page of the spreadsheet so I can put that into a macro? (I could use a footnote but would prefer to have it on the sheet itself so it can be seen when working on the sheet).

623bookends

**protonLeah** · 02-13-2012, 01:52 AM

There are too many variables: margins, monitor, fonts, window size, print area, etc.
If you have designed your sheet with specific print area you could use that with something like:

Option Explicit
Sub test()
    Dim AreaAddress As String, Bottom As Long
    
    AreaAddress = Worksheets("Sheet1").PageSetup.PrintArea
    Bottom = Range(AreaAddress).Rows.Count - 1
    Cells(Bottom, 1).Value = ThisWorkbook.Name
End Sub