PDA

View Full Version : Thisworkbook.path



SJM
10-20-2010, 10:40 PM
I have Thisworkbook.path statement in BeforeClose event in the workbook. This seems to work fine in Excel 2002. However, in Excel 2010 it returns blank if BeforeClose is used immediatley after swithcing worksheets. Please see attached sheet that walks thru the steps. Any help will be appriciated.

Simon Lloyd
10-20-2010, 11:27 PM
In the Beforeclose event >xl2007 use ActiveWorkbook.Path

Jan Karel Pieterse
10-20-2010, 11:55 PM
Works fine for me, always shows the correct path!

Bob Phillips
10-21-2010, 12:15 AM
Works fine for me too!

Simon Lloyd
10-21-2010, 12:56 AM
xl2010 on closing via x msgbox showed no path for me, all the others showed the workbook path, but using Activeworkbook cured that for me.

Bob Phillips
10-21-2010, 01:25 AM
Very odd, it showed the path for me.

Simon Lloyd
10-21-2010, 06:57 AM
I find it hard to believe it would be hit and miss!, i opened and closed the workbook via the x 30 times 23 times it gave me the path the others it gave an empty path (xl2010), i wonder whether the focus is being shifted at some point?

As a point of interest or maybe a fluke using ActiveWorkbook always gave the path!

SJM
10-21-2010, 08:54 AM
Changing Activeworkbook.path did not solve the problem. Any suggestion for workaround. It took a long time to nerrow down the propblem. I am checking if the file is new based on a template before save:


With ThisWorkbook
If Not .Saved Then
Select Case MsgBox("Do you want to save the changes you made to '" & .Name & "'?", _
vbYesNoCancel + vbExclamation)
Case Is = vbYes
'Call customized save routine
If Me.Path = "" Then 'if this is a new file based on template
Call CustomSave(True, Cancel) 'this will prompt for new file name
Else
Call CustomSave
End If
Case Is = vbNo
'Do not save
Case Is = vbCancel
'Set up procedure to cancel close
Cancel = True
End Select
End If


Thank you everyone for looking into this.
SJM

Jan Karel Pieterse
10-21-2010, 11:36 AM
A file created from a template that has not been saved yet never has a Path.

Simon Lloyd
10-21-2010, 11:56 AM
A file created from a template that has not been saved yet never has a Path.Agreed, but in my test i had opened the workbook and saved it to my test folder but the close by x in xl2010 still showed the anomaly, however using activeworkbook did cure it for me, so i cant understand the OP's continuing issue.

SJM
10-21-2010, 12:36 PM
A file created from a template that has not been saved yet never has a Path.

Exactly. That is what I am trying to find. But, if a file is saved it also shows no path. Others on this thread have confirmed the behavior and have suggested using Activeworkbook as solution. However, I am not able to solve the problem using Activeworkbook. I am hoping someone will provide a workaround and suggest alternate solution.

SJM

Simon Lloyd
10-21-2010, 10:48 PM
I would use a public variable and capture the workbook path on open then refer to that variable in the workbook close.

Jan Karel Pieterse
10-22-2010, 04:42 AM
What does Me.Path give you (works in ThisWorkbook module only of course)?

SJM
10-22-2010, 06:45 AM
What does Me.Path give you (works in ThisWorkbook module only of course)?

It is blank. I have tried to change that to Activeworkbook but did not make any difference.

As a side note, the Saveas command also returns blank file name even if the file name is entered. So if I close the worksheet immediately after switching sheets and use Saveas command, the Saveas file will not have any name. I have attached the original file with Saveas added to show the problem.

Bob Phillips
10-22-2010, 07:14 AM
Again,. not here SJM. The MsgBox tells us the path, and the SaveAs dialog is populated, albeit with quotes.