HowTo: Store Microsoft Office documents in your custom data storage

One of the tasks that developers often run into is saving and retrieving Office documents (or document copies) from external data storage. This storage can be an SQL database, Web-service, FTP server as well as a shared network resource of a local area network. Let's try to handle this task for such popular MS Office applications as Word, Excel, PowerPoint and Outlook using Visual Studio 2005 VB.NET and C#.

Data storage implementation

Let's start with implementing data storage. In our case it will be a Web-service with three methods:

1. SaveData. This method is used to save the document as a byte array. The first parameter is the type of the document being saved, the second one is the document name and the third is its contents.

<WebMethod()> _
Public Sub SaveData(ByVal type As Integer, ByVal name As String, ByVal data As Byte())

2. LoadData. This method is used to load the document as a byte array. The first parameter is the type of the document being inquired, the second is the document name, and the returned result is the contents of the document or null if the inquired document does not exist.

<WebMethod()> _
Public Function LoadData(ByVal type As Integer, ByVal name As String) As Byte()

3. GetListOfData. It is used for getting a list of saved documents names.

<WebMethod()> _
Public Function GetListOfData(ByVal type As Integer) As String()

The document types can be defined in the following way:

Private Enum DocTypes
  WordDocument = 0
  ExcelWorkbook = 1
  PowerPointPresentation = 2
  OutlookEmail = 3
End Enum

COM add-in implementation

Now let's proceed to implementing the COM add-in for MS Word, Excel, PowerPoint and Outlook. Each Object Model of these host applications contains its own features, that's why the code turned out to be quite bulky and it doesn't make sense to publish it here in full. I will make an outline of the main problems and their solution methods and you can use the links at the end of this post to download the entire code. So, the main problems are:

How to save an active document

• Outlook. It's easy, you take Outlook._MailItem from the active inspector, save it into a temporary file using the SaveAs method, read the contents of this file using the System.IO. File.ReadAllBytes method and pass the contents onto our web-service through the SaveData method.
• Excel and PowerPoint. It's not that easy in this case. These documents can be edited after they are opened from our data storage. That's why you need to somehow mark them to save the subsequent alteration back to the storage. You can use the CustomDocumentProperties collection to mark the document. Add a string value with a unique name to this collection, save the active document to a temporary file using the SaveAsCopy method, read the contents of this file using the System.IO. File.ReadAllBytes method, pass it on to our web-service with the SaveData method, remove the unique record from the CustomDocumentProperties collection of the active document and save the local document.
• Word. It is the most complex case, because the Object Model of this application doesn't have the SaveAsCopy method. So we will have to use the SaveAs method and then reopen the original document that the user worked with before saving. All other operations are the same as in Excel or PowerPoint.

How to open a document from data storage

• Irrespective of host application you need to get a local copy of the saved data. You get a list of document names using the GetListOfData method, then suggest the end-user selecting the necessary document, then get its contents as bytes array using the LoadData method and save the received bytes array into a file.
• Excel, Word and PowerPoint.Open a workbook, document or presentation using the Open method of the respective host application.
• Outlook. Since the Outlook Object Model doesn't have the Open method, you can open the saved msg file using the following VB.NET code:

System.Diagnostics.Process.Start(<PATH_TO_EMAIL_MESSAGE_FILE>)

How to save a document opened from data storage

And a few words about saving a document which was opened from data storage:

• Intercept the event of the standard Save button. To do this you'll need two components, AddinExpress.MSO.ADXBuiltInControl with Id = 3 for Word, Excel and PowerPoint 2000 – 2003 and AddinExpress.MSO.ADXRibbonCommand with IdMso = "FileSave" for Word, Excel and PowerPoint 2007.
• First we check the CustomDocumentProperties collection in the ADXBuiltInControl.ActionEx and ADXRibbonCommand.OnAction event handler. If the document is marked with the unique record, cancel the standard operation, save the document using the Save method, read the file contents using the System.IO. File.ReadAllBytes method and transfer the received bytes array to our web-service using the SaveData method.

Ah, almost forgot… What do you do with a heap of temporary files that were created when opening documents from data storage? Just delete them. To do this, add a full path to each file that was created to a special list and then remove all the files from this list in the AddinBeginShutdown event handler.

Everything described above is one of several possible options of handling such tasks. Additionally, you can try the following points:  disable a standard SaveAs button, abandon custom buttons and process standard Save and Open, process keyboard shortcuts, etc.

You may also be interested in:

Developing Office COM add-in in .net
Creating Outlook add-in in VB.NET, C#
Building Excel Automation add-in 

Available downloads:

C# sample add-in for VS 2005 
VB.NET sample add-in for VS 2005