Workbook Path Property returning NullString

[Galaxiom]

Suddenly, sometime between 5 Oct and yesterday, the Workbook path property is returning NullString.

Application.Workbooks(ThisWorkbook.Name).Path

Also happens on a new workbook with the expression run from Immediate Window.
No updates to Windows or Office 365.

What else could cause it?

 

[June7]

Is this a workbook that has been saved?

 

[The_Doc_Man]

Debug the code and set a breakpoint on the line that contains this reference. When the debug screen steps up, you can use DEBUG.PRINT or hover the cursor over things to examine their contents. I'm thinking that "ThisWorkbook.Name" might be wrong in one of two ways - either .Name is null or it points to a non-existent file.

You can also iteratively do a Debug.Print on Application.Workbooks(0).Path, ... (1).Path, ...(2).Path etc. to see where these items are located. You can tell which ones are empty because Debug.Print would tell you "null" for any that are not defined.

The idea is to see what has become dereferenced. Then perhaps a little more detective work might follow.

I know you're no novice in this. You've been on the forum for a LONG time. You knew to check for Windows and Office updates and say that none have occurred. But you and I both know that computers don't go wacky without cause, so if you can figure out the nature of what changed, you might be able to figure out WHY it changed.

 

[Galaxiom]

Ye

Is this a workbook that has been saved?

Good lead. The Master workbook with all the macros is derived from a template. The macros generate a number of new workbooks from the data loaded automatically into the derived Master document using database connection when it opens. The path for these derived documents is created as a subfolder of the folder holding the Master template which is where it uses the Workbook.Path property. (The dynamic path is because multiple independent users run their own copy of the master template.)

The path property used to return the location of the template. That is the behaviour that has changed. Master was never meant to be saved. It was done this way so I could edit the template and code would know it was working on the template and save it as xltm after twice warning the user that it was the template.

I have now saved the derived Master as xlsm and that is working.

Thanks for your suggestion. Still a mystery why the behaviour suddenly changed though.

 

[Galaxiom]

I'm assuming that the derived Master is no longer running de facto in the same folder as its template and the actual temporary location where it exists until saved is exempted from being returned by Workbook.Path.

We have Office Administrative Templates distributed by Group Policy that control settings in Office Applications. I know there is one for default save location. I will check if they have been altered recently.

 

[Galaxiom]

Debug the code and set a breakpoint on the line that contains this reference. When the debug screen steps up, you can use DEBUG.PRINT or hover the cursor over things to examine their contents. I'm thinking that "ThisWorkbook.Name" might be wrong in one of two ways - either .Name is null or it points to a non-existent file.

I immediately did exactly that when the error popped up. The Name was fine but the Path was NullString.

 

[arnelgp]

can you check if it is null also on ThisWorkbook.Path

 

[Galaxiom]

can you check if it is null also on ThisWorkbook.Path

Yes same.

 

[Isaac]

It's always a zero length string for me until it's saved
Just tested it
That's even on my home PC, with no IT group or policy
Just checked, also the same on my work computer, with full IT group and policies galore.

Depends on how it's generated, but until it's saved. Even
set wb = application.workbooks.add
msgbox wb.path

..... is a ZLS.

Not actually null,
?isnull(thisworkbook.path)
False

 

[Galaxiom]

It's always a zero length string for me until it's saved

Yes. Now that this has been considered, the surprise is that it worked before. I developed the macros on an xlsx file then routinely converted it to a template, as one does with such things, without considering this factor in the code. It had been working from the template for over eighteen months, until this week when it suddenly failed.

 

[ebs17]

the surprise is that it worked before

It's just a feeling on my part, but I believe, even with Access, that a higher level of error tolerance has been built into the application programming. You notice this with many internal data type conversions.
As applications continue to develop, this higher error tolerance seems to be reduced again and again, possibly time-driven by the high internal complexity.
The only safe way is to do your own highly precise and error-free programming.