PATH Update; VBA problems

  • Thread starter Thread starter RU_Powers
  • Start date Start date
R

RU_Powers

I have a cell "F10" on sheet 1 that contains the PATH for the file. I
have the following code which is working fine:

Private Sub Workbook_Open()
Application.Caption = ThisWorkbook.Path
Sheet1.Range("F10").Value = ThisWorkbook.Path
End Sub

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

Upon opening the book, the Path is listed in the caption bar and
updates F10 with the location. Upon closing it removes it from the
caption bar.

What I want to do is have that cell updated anytime the file is saved.
I tried the following code. It did not give me an error but it also
did not give me my desired result.

Private Sub Workbook_AfterSave()
Sheet1.Range("F10").Value = ThisWorkbook.Path
End Sub

What am I missing?

Thank you for your help.

RU
 
RU,

There isn't an After_Save Event.
There is a Before_Save which should do the trick for you.

For a lit of Workbook events, from the VBA Editor, Double
click on THisWorkbook in the "Projects", select "Workbook"
from the left dropdown in the panel to the right and then check
the right dropdown. There you'll have a list of all of the valid
workbook events.

John
 
Thanks for the pointer on the pull down menu.

I modified the code:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Sheet1.Range("F10").Value = ThisWorkbook.Path
End Sub

It works - sort of. When I save, the function does occur but does not
display immediately. This is what I mean.

File is in Folder A
I 'Save As' into Folder B
(When I look at the cell F10, it still says Folder A)
As soon as I click on Save As again then the cell updates it's
reference. (I don't even have to actually save, just clicking in save
in the menu does it.)

Is there a way to make it update/refresh? I don't think so but I
thought I would ask those who know more then me.

I have added code for BeforePrint and if a person saves the file to a
location and then chooses to print, the correct location will appear.
That may be my only solution.

SIDE QUESTION. Is there a way to get the file name with the path?
That is not as important to me as the Path, but if it could be done
easily that would be a bonus.

Thanks again John (and any others who reply)

RU
 
RU,

Here's a workaround.....
I never could figure out exactly why it works but it does.
The premise is to use the Application.OnTime method
to create a one second delay before running the code.
I'm guessing that this gives Excel an opportunity to save
the file prior to running the code.

Anyway......
In the Workbook.Before_Close Event:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Application.OnTime Now + TimeValue("00:00:01"), "UpdatePath"
End Sub

And now in a regular module:

Sub UpdatePath()
Sheet1.Range("F10").Value = ThisWorkbook.Path & "\" & ThisWorkbook.Name
End Sub

You'll note that I added "ThisWorkbook.Name" which should answer
your other question.

John
 
Back
Top