Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
PATH Update; VBA problems
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
PATH Update; VBA problems
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 "RU_Powers" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
PATH Update; VBA problems FOLLOW UP
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 On Mon, 18 Aug 2003 19:34:39 -0400, "John Wilson" wrote: 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 "RU_Powers" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
PATH Update; VBA problems FOLLOW UP
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 "RU_Powers" wrote in message ... 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 On Mon, 18 Aug 2003 19:34:39 -0400, "John Wilson" wrote: 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 "RU_Powers" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup - Update Path | Excel Worksheet Functions | |||
Excel problems after winXP sp3 update | Excel Discussion (Misc queries) | |||
auto file path update when excel sheet moved to another directory. | Excel Discussion (Misc queries) | |||
Recurring Problems with Links with Full Path Names | Excel Discussion (Misc queries) | |||
More UDF problems; some won't update, some won't stop running! | Excel Programming |