Excel 2007 macro gets workbook name and path from temp file

[BothFeet]

I have Office 2003 with compatibility pack installed. I have an
App_WorkbookOpen macro that gets the name and full path of any newly
opened workbook. This macro works correctly for any pre-2007
workbooks


When opening 2007 workbooks, the macro gets the name of the temp
files! So workbook.name, workbook.fullname and workbook.path all
point to the .tmp file.

Is there a way around this?

 

[Jim Rech]

Better post some code that duplicates the problem.

--
Jim
|I have Office 2003 with compatibility pack installed. I have an
| App_WorkbookOpen macro that gets the name and full path of any newly
| opened workbook. This macro works correctly for any pre-2007
| workbooks
|
|
| When opening 2007 workbooks, the macro gets the name of the temp
| files! So workbook.name, workbook.fullname and workbook.path all
| point to the .tmp file.
|
| Is there a way around this?

 

[BothFeet]

Code in ThisWorkbook:

Option Explicit

Private WithEvents App As Application
Private Sub Workbook_Open()
Set App = Application
End Sub

Private Sub App_WorkbookOpen(ByVal Wb As Workbook)
MsgBox "workbook was opened: " & Wb.Name & vbCr & vbLf & _
", full name: " & Wb.FullName & vbCr & vbLf & _
" path: " & Wb.Path & vbCr & vbLf
End Sub


wb.fullname will return corrent name and path of workbook for Excel
2003 notebooks
returns .tmp file and path to temp directory for Excel 2007 notebooks.

 

[BothFeet]

....... should also add, this code is in a Excel 2003 workbook that I
use for my add-ins. I first open this workbook with the code, then
any other workbooks I open get caught by the workbook open event .....

 

[Jim Rech]

Interesting. This is apparently a side-effect of the conversion process
that occurs when a 2007 workbook is opened in 2003. You could deal with it
by delaying getting the workbook name just a microsecond like this:

Private Sub App_WorkbookOpen(ByVal Wb As Workbook)
Application.OnTime Now, "Thisworkbook.ShowWBName"
End Sub

Sub ShowWBName()
MsgBox "workbook was opened: " & ActiveWorkbook.Name & vbCr & vbLf & _
", full name: " & ActiveWorkbook.FullName & vbCr & vbLf & _
" path: " & ActiveWorkbook.Path & vbCr & vbLf
End Sub

This assumes the opened workbook is the active workbook, which it would be
unless it was saved hidden like Personal.xls is.