Sub Auto_open()
MsgBox "The name of the active workbook is " & ActiveWorkbook.Name
MsgBox "The name of the this workbook is " & ThisWorkbook.Name
End Sub

The name of the active workbook is PERSONAL.XLS
The name of the this workbook is PERSONAL.XLS

Sub workbook_open()
MsgBox "The name of the active workbook is " & ActiveWorkbook.Name
MsgBox "The name of the this workbook is " & ThisWorkbook.Name
End Sub

.Workbooks(.Workbooks.Count).Name

Workbooks( 1 ).Name

Personal.xls лежит тут C:\Documents and Settings\***\Application Data\Microsoft\Excel\XLSTART
а файл  1 .xls например тут C:\

Т.е. макрос находится в PERSONAL.XLS->Microsoft excel objects->Эта книга:

Sub workbook_open()
    With Application
        MsgBox "The name of the workbook I need is " & .Workbooks(.Workbooks.Count).Name
    End With
End Sub

Option Explicit

Public WithEvents App As Application

Private Function App_WorkbookOpen(ByVal Wb As Excel.Workbook) As String

Dim SaveAsName As String
Dim strDocName As String
Dim intPos As Integer

If Wb.Name <> "PERSONAL.XLS" Then MsgBox "The name of the workbook opened is " & Wb.Name: strDocName = Wb.Name

intPos = InStrRev(strDocName, ".")
strDocName = Left(strDocName, intPos -  1 )
strDocName = strDocName & ".txt"

App_WorkbookOpen = strDocName

MsgBox "Transparent variable:" & strDocName
End Function

Option Explicit

Dim AppClass As New EventClass

Private Sub Workbook_open()
Set AppClass.App = Application

End Sub