VBA: Get Active Workbook Name

[nsontag]

Hi All,

I need to return the name of an active workbook, but I cannot use ThisWorkbook because my code resides in an add-in.

I have managed to make a function to accomplish this, but it seems messier than it ought to be. Does any one know of a slicker way to do this?

I'm using Excel 2010 and windows 7

The code for my current function is below:

Function GetBook(myRange As Range) As String
' Returns name of the workbook in which a range is located as a String.

Dim address As String
Dim nameSplit As Variant
Dim bookName As String

address = myRange.address(external:=True)

nameSplit = Split(address, "]")

bookName = Replace(nameSplit(0), "'", "", 1)

GetBook = Replace(bookName, "[", "", 1)

End Function


Thanks,

Nolan

 

[JoeMo]

Try:

Function GetBook() As String
GetBook = ActiveWorkbook.Name
End Function

 

[AlphaFrog]

GetBook = MyRange.Parent.Parent.Name

The first .Parent is the worksheet of MyRange. The second .Parent is the workbook of the worksheet.

 

[nsontag]

Thanks guys.

JoeMo: your method works, but only if the book is active (I realize that is exactly what I asked for, but I also need it for instances in which I'm working in an inactive book...my bad!)

Alpha: Exactly what I was looking for. Thanks for reading beyond my poor communication skills!

 

[sika85]

Dir(ThisWorkbook.FullName)

Returns the filename