inserting the name of the current workbook into a cell

[Paul James]

If I wanted to use VBA to insert the name of the current workbook into
sheets(1).range("a1"), is there a way to do this?

Thanks in advance.

 

[Dave Peterson]

You could do in VBA or a worksheet formula:
http://www.contextures.com/xlfaqFun.html#SheetName

One way in VBA:

sheets(1).range("a1").value = activeworkbook.fullname
'or activeworkbook.name

 

[Bob Kilmer]

Yes.

;^>

 

[Bob Kilmer]

Sheets(1).Range("A1").Value = ThisWorkbook.Name

or

Sheets(1).Range("A1").Value = ThisWorkbook.FullName

 

[Ron Rosenfeld]

If I wanted to use VBA to insert the name of the current workbook into
sheets(1).range("a1"), is there a way to do this?

Thanks in advance.

You could do something like this:

Private Sub Workbook_Open()
Worksheets(1).Range("A1").Value = ThisWorkbook.Name
End Sub

This is an event macro and will do the insertion when you open the workbook.
To enter it, <alt><F11> opens the VB Editor. Double click on ThisWorkbook on
the pertinent VBA project, and paste the code into the window that opens.

Note that the file has to be saved in order for the Workbook to have an
accessible name. This will be true whether you use this VBA method (which you
requested) or use a formula string manipulation of the CELL worksheet function.


--ron

 

[Paul James]

Works great. Thanks, guys.

Thanks also for that link to the Excel faq, Dave.

 

[Paul James]

Ron - Thank you for the additional information about the Workbook_Open
event. That's something I can use for a number of purposes.

And thanks also for the step by step explanation. I handn't appreciated the
benefit of double clicking on ThisWorkbook - that's also something I will
use.