Huge bug of Saved property in Excel2000

[LF]

Hello,

I am writing a COM add-in that should work in Excel2000/XP/2003. I need to
use the Saved property of the Workbook. However, it is not working, although
in the help is mentioned that it is read/write. In Excel2000 I can set it to
whatever value I want, the value I specify it is not retained. Please note
that this is not a problem with my (C++) code as it is not working from a
VBA macro either. In Excel2003 and ExcelXP it works from both C++ and VBA.
Help! I am desperate to make this work.

Thanks and regards,
Levente

 

[XL-Dennis]

Levente,

We need more info then 'it's not working'.

What are You trying to achieve and under which cirumstances does it not work?

Reading means that we can check if the workbook has been changed or not
since the last saving:

If Workbook.Saved Then
'Code here as the workbook has been changed.
Else
'Code here as the workbook has not been changed.
End if

Writing means that we can save the workbook:
Workbook.Save 'Save the workbook under the present name.
Workbook.SaveAs 'Save the workbook under a new name.

We can also close the workbook and control if any changes should be
considered or not:
Workbook.Close (SaveChanges:='Boolean value)

Which version does it not work with? In general it's recommended to always
make sure that You use the latest available Service Pack for the targeting
versions of the host application.

------------
With kind regards,
Dennis
Weekly Blog .NET & Excel: http://xldennis.wordpress.com/
My English site: http://www.excelkb.com/default.aspx
My Swedish site: http://www.xldennis.com/

 

[LF]

Hello,

I am storing some transient properties on the document (as custom document
properties) that are necessary for my processing of the document. However,
adding these will dirty the document and Excel will want to save it, even if
the interactive user did no changes. My understanding is that I can control
this "dirty" flag using the Saved property of the workbook (I find this
natural, it is present in all Office apps). However, setting the Saved
property to true and then reading the Saved property back immediately I
found that the value I set is not retained. I find this outrageous. A huge
bug. Need help!

One more thing. I am confident that Excel updates the Saved property to what
it thinks is correct and will set it to false as the document changes. This
is all good and fine, but __I__ want to be able to override this and control
when the document (workbook) should be considered dirty (thus needing a
save).

I would like to underline it again (although I believe I was clear enough in
my initial post): it does not work only in Excel2000. In XP and 2003 it
works OK. My Office 2000 is at SP3 already and with all possible fixes
installed.

Regards,
Levente

 

[XL-Dennis]

Levente,

Thanks for the information. I wrote 'version' but I had 'sp' in mind...
Anyway, I've done some basic tests and can't reproduce it. Is it possible
for You to provide us with some code?

---------------
With kind regards,
Dennis
Weekly Blog .NET & Excel: http://xldennis.wordpress.com/
My English site: http://www.excelkb.com/default.aspx
My Swedish site: http://www.xldennis.com/

 

[LF]

Dennis,

First, are you trying with Office 2000?!? Then you can reproduce it in two ways:

Create a new macro (Tools\Macro\Macros... enter a name, click Create), and make it look like this:

Sub test()
ActiveWorkbook.Saved = True
ActiveWorkbook.Saved = False
End Sub

Put a breakpoint on the first assignement line and then run the macro in the VBA debugger. You'll see that at least one of the assignement is a no-effect, the assigned value is not retained. Why, oh why is Excel so full of bugs?!?

Or from some C++ code, in a COM add-in, do this:

void Test()
{
CComQIPtr<_Workbook> ptrDoc = m_ptrApplication->ActiveWorkbook;
if(!ptrDoc)
return;

bool bSaved = true;
ptrDoc->Saved[0] = bSaved ? VARIANT_TRUE : VARIANT_FALSE;
bool bReallySaved = (VARIANT_FALSE != ptrDoc->Saved[0]);
ASSERT(bSaved == bReallySaved);
}

Any ideas why this is not working?

Best regards,
Levente

 

[XL-Dennis]

Levente,

I'm running Swedish version of Excel 2000 with SP-3 installed on a Windows
XP platform (Unfortunately I have only access to English version of 2002,
2003, 2007 but not for 2000).

I have done the following two tests:

Option Explicit

Sub test()
Dim wbBook As Excel.Workbook
Set wbBook = ActiveWorkbook
wbBook.Saved = True
'The following line gives 'True'
MsgBox wbBook.Saved, vbOKOnly, "Status 1"
wbBook.Saved = False
'The following line gives 'False'
MsgBox wbBook.Saved, vbOKOnly, "Status 2" '
End Sub

Sub Test2()
Dim wbBook As Excel.Workbook
Set wbBook = ActiveWorkbook
wbBook.Saved = False
'This gives 'False'
MsgBox wbBook.Saved, vbOKOnly, "Status 1"
'This gives also 'False'
'The same output is achieved when setting it to True, i e it gives the same
msg.
MsgBox wbBook.Saved, vbOKOnly, "Status 2"
End Sub

In general I would advise to avoid the use of 'ActiveWorkbook' as it can
refer to the wrong workbook.

I must admit I'm confused why You have this issue...
I've made some online searches and noticed that the issue have been
discussed several times but no solution is given in any of the cases.

It would be great if someone from MSFT could comment it but I'm not sure if
they still support 2000 or not.

---------------
With kind regards,
Dennis
Weekly Blog .NET & Excel: http://xldennis.wordpress.com/
My English site: http://www.excelkb.com/default.aspx
My Swedish site: http://www.xldennis.com/

 

[LF]

Dennis,

Thank you for confirming this bug. I started to think I am the only one
affected (although after it happens on several machines, I found it hard to
believe). Now, comeon Microsoft don't pull this bullshit on us! This throws
the support for Office 2000 our of the windows for our application and my
clients are going to eat me alive

Regards,
Levente

 

[LF]

Dennis,

Thanks for confirming this mess. I just found out that the same problem is
also present in PowerPoint 2000, but it got resolved with some service pack.
Why is this not solved in Excel 2000 too? I am disperate for a solution, can
anyone please help?!?

Regards,
Levente

 

[NickHK]

XL2000 with all service packs applied:
I see .Saved property responding correctly.

NickHK

 

[LF]

Nick,

What is the version + build number of your Excel 200? What language is your
Excel 2000? What OS are you trying on? How are you trying to determine that
the Workbook.Saved property is working OK? Did you try my example
using/debugging a macro?

Thanks,
Levente

 

[NickHK]

Excel2000, English, W2K:
Using code below, there is no Excel prompt about saving the changes. Comment
out the .Saved line and the prompt appears. So .Saved is working for me.

Private Sub CommandButton1_Click()
Range("A1") = ""
Range("A1") = "New Text"

With ThisWorkbook
.Saved = True
.Close
End With

End Sub

NickHK

 

[LF]

Nick,

I just discovered the following: if my COM add-in is not registered, the
macro test succeeds. If I register my COM add-in, suddenly the Saved
property is no longer working. Even if I do not react to any event, if I do
not change anything on the doucments that get opened/closed. Any idea why?

Regards,
Levente

 

[LF]

Actually,

My previous findings are incomplete. If I catch no events (or return S_OK
from all of them), then the Saved property works OK. If, however, I catch
OnNewDocument and add a custom property to the document, then I set the
Saved flag to true (this is part of my appliation's processing, it works in
all other Office apps, also in all other Excels excelp 2000) then the Saved
property no longer functions properly. I do not understand. Any help?

Regards,
Levente

 

[LF]

Guys,

Another update. Apparently, after I touch the collection
Workbook.CustomDocumentProperties the Workbook.Saved property is no longer
functioning properly. What the hell is going on?!? This only misbehaves in
Excel 2000.

Any help or explanation is welcome! Thanks.
Levente

 

[NickHK]

I don't write COM add-ins, so I can't answer your question.

NickHK

 

[LF]

Nick,

This is NOT a COM add-in related problem. Try the following macro in Excel
2000 and you'll see the bug:

Sub TestBug()
ActiveWorkbook.CustomDocumentProperties.Add "Another", False, 4, "Test"
ActiveWorkbook.Saved = True
End Sub

To summarize: in Excel 2000, after you alter the
WorkBoox.CustomDocumentProperties collection in any way, the Workbook.Saved
property is no longer working. It stays False, no matter what you do. I
consider this a bug, and a very nasty + important one at that. Anyone at
Microsoft can tell us anything about this? Workarounds? Fixes? I am
desperate.

Best regards,
Levente

 

[NickHK]

OK, I see what you mean now.
Yes, .Saved always remains False.
But there are many short comings of the CustomDocumentProperties collection,
that seems somewhat screwy.

But if you do not intend to save that added properties in the WB, use a
hidden name or something else for the data.
Depends why you need to do this.

NickHK

 

[LF]

Nick,

Some of the properties I store on the workbook are transitory (only used
while the workbook is open) and some I need to store with the workbook.
These must be invisible to the user. Any suggestion where to store these?

Also, what "many shortcomings" are you talking about? I already know that
only string properties are supported and that each property cannot be larger
than 255 characters. Also, I also know that these custom document properties
are stored in a COM stream in the root storage (we all know that a .xls
document is a compound document, right?) and although COM supports other
types of entries there, Excel only uses string properties.This collection is
also damn slow (cannot make up for the fact that each property is limited in
size and try to store thousands of properties as chunks of what you intend
to store). Moreover, if Excel finds anything in this property collection
that it does not recognize (too long strings, entries of different type than
string) it will simply trash all the properties (I will loose them).
Finally, these properties are visible in File\Properties tab Custom (and
users can remove them - I use GUIDS as property names though, maybe they'll
refrain from deleting those). Anything else?

Regards,
Levente

 

[Cindy M.]

Hi Lf,

Apparently, after I touch the collection
Workbook.CustomDocumentProperties the Workbook.Saved property is no longer
functioning properly. What the hell is going on?!? This only misbehaves in
Excel 2000.

Office 2000, generally, had more problems with VBA than later versions. Call
it growing pains...

In any case, it can generally be a problem with Word and Excel if you make
changes that aren't directly in the "document". If you want to force the
Saved property to be true, either set it explicitly, or write and then delete
(*not* undo) something into an empty cell after "talking to" the Document
Properties.

An alternative to using document properties would be a hidden and protected
worksheet in the workbook. The Excel experts in the excel.programming
newsgroup might have some good suggestions, as well...

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 17 2005)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or
reply in the newsgroup and not by e-mail