BUG: Excel 2002 May Modify the VBA Macro Storage of an OLE Object on Load (817600)


The information in this article applies to:

  • Microsoft Excel 2002

SUMMARY


You can use OLE to load a workbook, a worksheet, or a chart object in Excel 2002. However, if the object contains Microsoft Visual Basic for Applications (VBA) macros, Excel may recompile the VBA project. This problem occurs if the VBA project was previously saved in an earlier version of Excel. This causes the underlying VBA substorage to be rewritten, therefore dirtying the root storage of the object on load. As soon as the OLE host saves the object, the full changes are saved to the root storage. This process is not repeated for that object again. Subsequent open attempts no longer dirty the object.

This process may cause a problem for some OLE hosts. The problem occurs if the following conditions are met:
  • The OLE host uses IPersistStorage::Load (or OleCreateFromFile) to load an Excel object that was saved with a previous version of Excel in Excel 2002.
  • The root storage is opened in a nontransacted mode and then passed directly to Excel 2002 for the load.
  • The host does not explicitly save the object by using IPersistStorage::Save (or OleSave), after the load completes or when IOleClientSite::SaveObject is called.
  • The host does not check the IPersistStorage::IsDirty flag before closing the object or does not save the object because of some other condition.
This problem can leave the VBA substorage for the object in an incomplete state. As a result, the VBA project does not open correctly the next time that you try to open the object.

If you experience this condition, the VBA project stream cannot be completely read by the VBA engine, and you may receive the following error message:
Error accessing file. Network connection may have been lost.

RESOLUTION

All OLE hosts must check the IPersistStorage::IsDirty flag to discover if an OLE object must be fully saved before the object is closed. If the host intends to allow the user to abort changes to a loaded object, the host must open the underlying IStorage in transacted mode and then commit the changes when fully saved. If the user decides to abort the changes, the program must call IStorage::Revert. If the program does not do this, the problem occurs for the OLE host.

Due to the potential for data loss, Microsoft recommends that users who open persisted Excel objects in Excel 2002 immediately save the object at least one time after loading it. This makes sure that the VBA project is fully written to the substorage for that version of Excel. This avoids the problem regardless of how persisted Excel objects are opened the next time.

STATUS

Microsoft has confirmed that this is a bug in the Microsoft products that are listed at the beginning of this article.

MORE INFORMATION

Some users may see this problem more frequently than other users because of another bug in Excel 2002 that may leave the IPersistStorage::IsDirty flag unset after a successful load. This bug may leave containers that rely on this flag to perceive that the object is unchanged and therefore close the object without saving.

This issue has been addressed in Service Pack 2 for Microsoft Office XP.

REFERENCES

For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

321471 XL2002: You Cannot Activate Embedded Excel Workbook That Contains VBA Code

Modification Type:MinorLast Reviewed:9/27/2006
Keywords:kbOLEApp KB817600 kbAudDeveloper
©2004 Microsoft Corporation. All rights reserved.