How do I save an updated compound storage in memory as an .XLS file that can be re-opened by Excel?

Question

I was asked to post this question in this forum.

Office documents are stored in our database.  I retrieve them, and load them as compound storage files using the StgOpenStorage method.  I use OLE COM interfaces to load the Office App that created the original file.  The user is allowed to edit these files.  When the Office Apps are closed, the storage is updated with the changed data by the respective app. 

I now have updated storage data in memory.  I need to save this data to disk in the proper Office native format.  .xls for Spreadsheets, .doc for Word Documents.

I use the following code to write the updated storage object to disk for Word files. 

BOOL CPortalCntrItem::SaveToFile(CString& sSaveFile)
{
USES_CONVERSION;

	// Save the OLE Object to a file system file. The object has been loaded from 

	// storage using ::OleLoad.
	// m_lpObject contains the interface pointer to the IID_IOleObject.
	HRESULT hr;
	if (m_lpObject)
	{
	  LPPERSISTFILE lpPersistFile;
	  LPCTSTR lpsSaveFile = (LPCTSTR) sSaveFile;
	  hr = m_lpObject->QueryInterface(IID_IPersistFile, (LPVOID FAR *)&lpPersistFile);
       if (SUCCEEDED(GetScode(hr)))
       {
		Tr0("Got a reference to Persist Stream Interface.");
	     if (lpPersistFile)
     		{
		  // This should be interpreted as a Save Copy As.
    		  hr = lpPersistFile->Save(T2COLE(lpsSaveFile), FALSE);
		    lpPersistFile->Release();
		  Tr1("Saving Data to this file: %s", sSaveFile);
    		}
	  }			

	  if (lpPersistStorage != NULL)
	  {
	 	lpPersistStorage->Release();
	  }
	
	  if (hr == SUCCESS)
	  {
		Tr1("Saved Data to this file: %s", sSaveFile);
	  }
	  else
	  {
		Tr1("ERROR, SaveToFile exits with code %d", hr);
	  }
	}
	return (GetScode(hr) == S_OK);
}

Trouble is, this does not work for EXCEL files. 

hr = m_lpObject->QueryInterface(IID_IPersistFile, (LPVOID FAR *)&lpPersistFile); 

returns NULL for the lpPersistFile.  hr = 0x80010114 - The requested object does not exist.
So, I have the IID_IOleObject data pointer (m_lpObject), the storage pointer originally retrieved through StgOpenStorage, and updated when Excel closed, and I also have a valid IID_IPersistStorage pointer retrieved from the m_lpObject interface pointer.

If I call OleSave using the storage pointer in memory, my disk file contains the storage file, but it cannot be read as an Execl spreadsheet.  Excel reports invalid format.

Am I missing something easy?  The alternative method that I have tried, which was to attempt to open the "Workbook" stream, and attempt to manually find the storage that represents the .xls file, has not worked, as no storage that I retrieve and load into a new Compound Storage document opens successfully in Excel.

How do I take this existing storage and save it as an openable Excel file?

I am stuck using MFC OLE, and I am not allowed to use up-to-date automation techniques like new(Excel.Application).

Do I really have to sift through BIFF records to locate the needed storage?  If so, maybe you could help with which storage I actually need to save as a disk file?  I have had no luck so far.

ADDITIONAL EDIT:

One thing to add since I originally posted this.  Since I am trying to save after the OnClose call that I receive from the OLE server (Word, Execl), is it possible that my storage object is no longer connected to the data?

I do override the IOleClientSite interface, so maybe I should be saving the diskfiles there (SaveObject)?  It means creating the filename as a global string, so that other modules can access it various times, but I can try that.

 

Answer 1

Hi PJK_Ventyx,

Thank you for your question.  A colleague will contact you to follow up on this issue. 

Regards,

Mark Miller

Escalation Engineer

US-CSS DSC PROTOCOL TEAM

Answer 2

Hi PJK_Ventyx,

I'm the engineer assigned to help you with this issue.  Generally, your code looks to be correct however, as you've already suggested, you may be directing Excel to save the file after the storage has been released.  Your best bet would be to call IPersistFile->Save() before calling IOleObject::Close().  Alternatively you could try the above save code in your IOleClientSite::SaveObject which is called during IOleObject::Close() processing in the object. 

Each embedding server may exhibit slightly different behaviour even though there are standard procedures defined by the interfaces in OLE.  One server may hold onto the persistent storage longer than another for one reason or another.

Over all, you should not need to parse the Excel file format in order to cause Excel to store it's data to an .xls file. 

Best regards,
Tom Jebo
Escalation Engineer
Microsoft Open Specifications

Answer 3

Thank you for the response.  The iterface pointer for IOleObject isn't closed until after all processing is done and the saved file is sent back to the server DB.

One thing you mention, that I would like to try is to QueryInterface for IPersistFile in SaveObject ClientSite call.  I was surprised when Word produced a usuable PersistFile pointer but Excel did not after the OnClose.

I believe I was wrong in thinking I had to look at the storage.  The main clue for me finally was that, while the storage and its BIFF records looked like a compound storage Excel file, the "ObjectPool" did not exist. 

So all of the errors I was getting were as you noted that Excel may not be hanging onto the persistent storage.  After modifying the structure of my IOleClientSite overrides a bit, I was able to save a file that was openable by Excel.  I was pretty happy when it worked for nearly all the office files  :*)

Problem solved. Thank You.

Answer 4

Hi PJK_Ventyx,

Glad to hear you got it working! 

Tom

Answer 5

I have an involved question about modifying the Ribbon's in Excel and Word 2007+.  I must use C++ and MFC, and found the task rather daunting.  Can you tell me which forum I would put that in?

Answer 6

Hi PJK_Ventyx,

Based on your brief description of the issue, I think the Microsoft Office for Developers forum (specifically Excel) may be an appropriate starting point, http://social.msdn.microsoft.com/Forums/en-US/category/officedev,oldevelopment,sharepoint2010,sharepoint,projectserver2010,projectprofessional2010,uc

I hope this helps.

Regards,
Mark Miller
Escalation Engineer
US-CSS DSC PROTOCOL TEAM