Displaying File Path

[Amr Tabbarah]

In the Tip of the Hour entitled:

"Inserting the path where the Workbook is saved to the title bar or the
status bar using VBA in Microsoft Excel",

the indicated Macro works well. However, it is required to "Insert the
macro into every workbook that you need to view the path."

Anybody knows a way to have this Macro activated for any workbook that
is opened? If this is possible through the Personal.xls, how to insert
this Macro?

Thanks

 

[Bob Phillips]

Amr,

You don't show the macro, but I assume it is a BeforePrint event macro. If
so, then no, putting it in the Personal.xls file will not help, as that
event will not be invoked when another workbook is printed.

The best I can think of is to create a new workbook, add the macro, and
save it as Book.xlt in the XLStart directory. Every new workbook will then
have that macro.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Amr Tabbarah]

Thanks Bob,

The Macro is as follows:

Private Sub Workbook_Open()
Application.Caption = ThisWorkbook.Path
Application.StatusBar = ThisWorkbook.Path
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.Caption = ""
Application.StatusBar = ""
End Sub


I have tried the xlt suggestion but it did not work.

Do you have another suggestion?

Thanks,


Am

 

[Bob Phillips]

Amr,

It won't show for the new file, but once it is saved it will apply. Save one
and see.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Bob Phillips]

Amr,

You should also include this code in ThisWorkbok, in case you have multiple
files open

Private Sub Workbook_Activate()
Application.Caption = ThisWorkbook.Path
Application.StatusBar = ThisWorkbook.Path
End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Amr,

It won't show for the new file, but once it is saved it will apply. Save one
and see.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Amr Tabbarah]

Bob,

I have tried and it did not work.

I have used ALt+F11 and selecting "This Workbook" before writing the
Macro. Did I do something wrong?

I am enclosing the file in xls format since xlt is not accepted by the
Excel Forum, if this could be of any help in defining the problem.

Thanks,

Amr

Attachment filename: display_file_path1.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=407111

 

[Bob Phillips]

Amr,

That doesn't help as it opens the file in IE, not Excel.

It does work, I tried it. Try this

New a file - does it contain the code
Save the file
Close it
Open it again
Caption changed?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Bob Phillips]

Amr,

Just looked through some old files, and I have some code that will put the
full path and workbook name in the window caption, not Excel caption. This
can be included in Personal.xls and will apply to all open (but previously
saved) workbooks.

Do you want to see this?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Dave Peterson]

One way is to use an application level event:
http://groups.google.com/[email protected]
may help.

And stolen from that same thread:

But you may want to read about event programming at Chip Pearson's site:
http://www.cpearson.com/excel/AppEvent.htm

 

[Amr Tabbarah]

Bob,

I tried it again without success. Would appreciate seeing the code yo
have indicated. Thanks.


Dave,

I will be reviewing the sites that you have indicated. Thanks.




Am

 

[Bob Phillips]

Amr,

My solution is using application events as suggested by Dave, so it all
links.As suggested, put this code in Personal.xls

Put this code into a class module, mine is named clsAppEvents

'--------------------------------------------------------------
Option Explicit

Public WithEvents App As Application

Private Sub App_WindowActivate(ByVal Wb As Workbook, ByVal Wn As Window)
App.Caption = Wb.Path
End Sub
'--------------------------------------------------------------

Add this code to ThisWorkbook code module

'--------------------------------------------------------------
Option Explicit

Dim AppClass As New clsAppEvents

Private Sub Workbook_Open()
Set AppClass.App = Application
End Sub
'--------------------------------------------------------------

That's all there is to it.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)