MS Access to MS Word

• Download source code - 4.58 KB

Introduction

This program demonstrates how to get Word documents which are stored in an Access database (datatype OLEObject) into a single Word output report. It should easily generalize to other OLEObjects (Excel, PDF, images...).

Background

A common use of Access is to interoperate with Word by creating a form that lets users create documents (fonts, images, sophisticated editing) and store them in a field (type OLEObject) in the database. When the user double clicks on the associated control (Bound Object Frame), Word pops up (in the control) so they can view and edit the document. Sweet! A natural follow-on then would be to create a report in Word format that includes this data. Unfortunately, while Access has a nice Report / Export / Word capability, OLEObjects are totally unsupported there.

To do that, you need to either parse the OLEObject itself or somehow automate Access to activate the OLEObject bound to the Word Document field, copy the contents to the system clipboard, and paste it into the output document, and do that again for every record.

I was unable to make the OLEObject parsing technique work (including the very promising but perhaps outdated work by Stephen Labans). That left the Copy-Paste technique which I've implemented in C# using the Office Primary Interop Assemblies (PIA) in the attached source file.

The original impetus for me was the creation of a special-purpose glossary. We had numerous authors separately create definitions (frequently involving advanced typography) in Word. Then we had a few interns tasked with copying and pasting the definitions into an Access database (the application invoked Word so each definition was a small Word document stored in the OLEObject field of the database). We managed the corpus in Access and then finally needed to produce the glossary document. That's when we found out there's no way to export OLEObjects (even if they are just Word documents) to Word. The solution is the attached script. I hope it saves others some of the grief I went through to get this working.

Using the Code

To compile the code, you'll need Visual Studio (I'm on 2010), Word, and Access installed. Open the project and update the references for Microsoft.Office.*:

// you'll need to set references to Microsoft.Office.{Core, Interop.Access, Interop.Word}.  Maybe something like this:
// C:\Windows\assembly\GAC_MSIL\Office\14.0.0.0__71e9bce111e9429c\Office.dll
// C:\Program Files (x86)\Microsoft Visual Studio 10.0\Visual Studio Tools for Office\PIA\Office12\Microsoft.Office.Interop.Access.dll
// C:\Program Files (x86)\Microsoft Visual Studio 9.0\Visual Studio Tools for Office\PIA\Office11\Microsoft.Office.Interop.Word.dll

Now, you'll need to say where the output goes (create the empty document first), where the input comes from, and the name of the field that holds the Word documents.

// Edit Output MSWord document, Input Access Database Name and the DB Field name that holds the MSWord documents
const String DstFile = @"C:\dev\oletest\MyOutput.docx"; 
const String SrcDatabase = @"C:\Users\mrosen\Downloads\ASPRS_OLD_TERMS_2003_10072012_SHAREVERSION.mdb";
const String OLEObjectFieldName = @"ORG_DEF";

You'll also need to write the SQL query used to get the data:

// Edit:  write the SQL Select we'll use to get the data.
//theForm.RecordSource = "SELECT Top 8 OLDTERMS_Original.* FROM OLDTERMS_Original ;";
theForm.RecordSource = "SELECT OLDTERMS_Original.* FROM OLDTERMS_Original;"; 

Finally, you'll need to specify what you want Word to do with each row of the query above. You'll do this using Word's PIA:

// Edit
// Given a record in the above RecordSource, populate the MSWord document accordingly
int id = theForm.Recordset.Fields[0].Value;
string term = theForm.Recordset.Fields[3].Value;
DstDoc.Bookmarks.get_Item(ref oEndOfDoc).Range.InsertAfter(String.Format("TERM({0}):{1}", id, term));
DstDoc.Paragraphs.Add(Nothing);
DstDoc.Bookmarks.get_Item(ref oEndOfDoc).Range.InsertAfter(String.Format("DEFINITION:"));
try
{
    bof.Verb = Constants.acOLEVerbOpen;
    bof.Action = Constants.acOLEActivate;
    Word._Document SrcDoc = WordApp.ActiveDocument;
    SrcDoc.Range().Copy();
    DstDoc.Bookmarks[oEndOfDoc].Range.Paste();
    SrcDoc.Close(ref doNotSaveChanges, ref Nothing, ref Nothing);
}
catch (System.Runtime.InteropServices.COMException ex)
{
    DstDoc.Bookmarks.get_Item(ref oEndOfDoc).Range.InsertAfter(String.Format(
      "(No defintion available, id = {0}, msg={1})", id, ex.Message));
    DstDoc.Paragraphs.Add(ref Nothing);
    Console.WriteLine(String.Format("Exception caught (record {0}:  Error Number {1}, {2}", 
                      id, ex.ErrorCode, ex.Message));
}

You should be ready to go now. No doubt you'll need several tries for the last part so keep the query short while you're testing ("top 8" in SQL Select above). There's a lot of screen activity when the program is running and so you may not be able to use your computer while this is going on.

Points of Interest

Note that one of the things we don't require is an Access Form. To invoke Word, we need a Bound Object Frame on a Form. But we create this on the fly and delete it when we're done.

History

• 15 Nov 2012. Initial article submission.
• 16 Nov 2012. Re-submitted as a "Tip" with minor edits.