Drive and full path in name at top of screen

M

Mervyn Thomas

The default seems to be to show the filename on the top border in Excel. Is
there any way to include the full path including the drive letter as well?
I have an operator who gets confused as to where she is and this would be
really nice.
 
B

Bob Phillips

Public WithEvents App As Application

Private Sub Workbook_Open()
Set App = Application
End Sub

Private Sub App_WindowActivate(ByVal Wb As Workbook, ByVal Wn As Window)
Wn.Caption = Wb.FullName
End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code
 
D

Dennis

Mervyn,

Something that I have used to document the location of W/S is:

In any cell, enter the formula =Cell("filename")
(Enter it exactly as above , in short, do not enter
the actual filename for "filename")

The above will display and print the drive; full path; and the Tab. Be
cautious though about the Tab, in most versions of XL the Pathe does not
update until a calculation is made.

Thus, if you open a W/B then a W/S and do not hit F9, the Tab displayed may
be incorrect.


HTH Dennis
 
M

Mervyn Thomas

Thanks that works very well, but how would you refresh the caption without
reopening the file - sometimes we do a <File Save as> to a different drive
and that is where the confusion usually starts?
 
M

Mervyn Thomas

You're solution works well and updates as you do <File Save as> which is
what I really need
Mervyn
 
B

Bob Phillips

add this event procedure to ThisWorkbook

Private Sub App_WorkbookBeforeSave(ByVal Wb As Workbook, ByVal SaveAsUI As
Boolean, Cancel As Boolean)
App_WindowActivate Wb, Windows(Wb.Name)
End Sub
 
D

Dennis

Bob,

I really like your idea. One can see the information no matter where they
are on the worksheet.

Thanks so much for your time and knowledge.

Dennis
 
J

Jim May

The sample I just tried **the full path is so long** the Windows title bar
shows just so much and truncates with the "..." indicating there is more...
what to do?
The Web Toolbar has an address box that seems to furnish the same info,
which can be accessed in full.(start to end)
Can you comment on this?
TIA,
Jim
 
B

Bob Phillips

Hi Jim,

A glib response is that you could just widen the workbook window :).

I also see that problem with files I open from emails, as they load into a
temporary directory. One way would be to check the name and the window
width, and if they don't fit, to truncate the name at the start, something
like 'C:\...\subdir\subdir2\filename', but it is still truncated, and seems
to defeat the purpose of the original request.

I can't think of a good soloution, other than to use the Excel caption bar.
 
B

Bob Phillips

Dave,

That is similar to what I was suggesting, but my way was manual.Suffers the
same problem as I alluded to, but it is so neat, and I wish I had known
about this on something else I had done earlier.

As the demo shows though, you still need to know how much room the receiving
object has otherwise it truncates it

Jim,

Do you want a version that uses this?
 
J

Jim May

"A glib response is that you could just widen the workbook window :)."
Mine (my window) was already maximized.
tks,
Jim
 
M

Mervyn Thomas

Bob - I couln't get that bit of code to work. These lines paste in red
colour in ThisWorkBook

Private Sub App_WorkbookBeforeSave(ByVal Wb As Workbook, ByVal SaveAsUI As
Boolean, Cancel As Boolean)

and then the whole thing bugs when the file is opened with the yellow on the
previous "Private Sub... that works OK on its own.
Full code that doesn't work now is:

Public WithEvents App As Application

Private Sub Workbook_Open()
Set App = Application
End Sub

Private Sub App_WindowActivate(ByVal Wb As Workbook, ByVal Wn As Window)
Wn.Caption = Wb.FullName
End Sub

Private Sub App_WorkbookBeforeSave(ByVal Wb As Workbook, ByVal SaveAsUI As
Boolean, Cancel As Boolean)
App_WindowActivate Wb, Windows(Wb.Name)
End Sub
 
B

Bob Phillips

Mervyn, are we are victims of the notorious NG wrap-around.

Here is the code again with continuations

Public WithEvents App As Application

Private Sub Workbook_Open()
Set App = Application
End Sub

Private Sub App_WindowActivate(ByVal Wb As Workbook, _
ByVal Wn As Window)
Wn.Caption = Wb.FullName
End Sub

Private Sub App_WorkbookBeforeSave(ByVal Wb As Workbook, _
ByVal SaveAsUI As Boolean, _
Cancel As Boolean)
App_WindowActivate Wb, Windows(Wb.Name)
End Sub


anyone interested in an ellipsed version as highlighted by Dave?
 
Top