Click here to Skip to main content
Click here to Skip to main content
Add your own
alternative version

Tagged as

MS Access to MS Word

, 31 Jan 2013 CPOL
Export OLEObject (Word Document) to Word Document.
msaccesstomsword.zip
Properties
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Word = Microsoft.Office.Interop.Word;
using Access = Microsoft.Office.Interop.Access;
using System.Data.OleDb;
namespace MSAccessToMSWord
{
    internal abstract class Constants
    {   // http://blogs.msdn.com/b/mshneer/archive/2009/12/07/interop-type-xxx-cannot-be-embedded-use-the-applicable-interface-instead.aspx
        public const short acOLEActivate = 7;
        public const short acOLEVerbOpen = -2;
    }

    // This is a console application which uses MS Office Interop to run through an MSAccess DB containing MSWord documents
    // and exports them to a single MSWord document

    // We do this by automating MSAccess and MSWord.  Basically we are scripting the equivalent of double clicking on the MSWord field, copying
    // the contents and pasting them into the ouput Word document.  There is lots of screen activity and you may not be able to use
    // the computer while this is running.

    // To compile (I'm using VS 2010) and run, you'll need MSWord and MSAccess installed and 
    // 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

    class Program
    {
        static void Main(string[] args)
        {
            // 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";

            Object Nothing = System.Reflection.Missing.Value;
            Object oEndOfDoc = "\\endofdoc"; /* \endofdoc is a predefined bookmark */
            Object doNotSaveChanges = Word.WdSaveOptions.wdDoNotSaveChanges;
            Object doSaveChanges = Word.WdSaveOptions.wdSaveChanges;
            Word.Application WordApp = null;
            DateTime dt = DateTime.Now;

            // Start up Word
            try
            {
                WordApp = System.Runtime.InteropServices.Marshal.GetActiveObject("Word.Application") as Word.Application;
            }
            catch (System.Runtime.InteropServices.COMException ) { }
            if (WordApp == null)
                WordApp = new Word.Application();
            // Avoid incomprehensible warning about ambiguous method / non-method using method group.
            // http://social.msdn.microsoft.com/Forums/en-US/csharpgeneral/thread/90fa8541-316e-4fac-9885-7c28ef9653b1/
            Word._Document DstDoc = WordApp.Documents.Open(DstFile);
            DstDoc.GrammarChecked = true;
            DstDoc.SpellingChecked = true;
            DstDoc.Range().Delete();    // delete any contents that were there.


            // Start up Access
            Access.Application AccessApp = null;
            try
            {
                AccessApp = System.Runtime.InteropServices.Marshal.GetActiveObject("Access.Application") as Access.Application;
            }
            catch (System.Runtime.InteropServices.COMException ) { };
            if (AccessApp == null)
                AccessApp = new Access.Application();

            AccessApp.OpenCurrentDatabase(SrcDatabase);
            // Add a temporary form that holds the Bound Object Frame we'll use to invoke Word.
            Access.Form newForm = AccessApp.CreateForm(SrcDatabase);
            Access.BoundObjectFrame bof1 = AccessApp.CreateControl(newForm.Name, Access.AcControlType.acBoundObjectFrame) as Access.BoundObjectFrame;
            const String BOFName = "MyOLE";
            bof1.Name = BOFName;
            bof1.ControlSource = OLEObjectFieldName;
            String fmName = newForm.Name;
            AccessApp.DoCmd.Save(Access.AcObjectType.acForm, newForm.Name);
            AccessApp.DoCmd.Close(Access.AcObjectType.acForm, newForm.Name);
            AccessApp.DoCmd.OpenForm(fmName);
            Access.Form theForm = (Microsoft.Office.Interop.Access.Form)AccessApp.Forms[fmName];

            // 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;";

            AccessApp.DoCmd.OpenForm(fmName);
            theForm.Recordset.MoveFirst();
            Access.BoundObjectFrame bof = theForm.Controls[BOFName];
            int RecordCount = 0;
            while (!theForm.Recordset.EOF())
            {
                // 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));
                }

                theForm.Recordset.MoveNext();
                RecordCount++;
                
                const int nRec = 8979;
                //const int nRec = 4;
                if (RecordCount % nRec == 0)
                {   // MSWord / OLE infrastructure seems to leak.  This close / re-open prevents
                    // a "too many files open" failure.
                    DstDoc.Close(ref doSaveChanges, ref Nothing, ref Nothing);
                    DstDoc = WordApp.Documents.Open(DstFile);
                    DstDoc.GrammarChecked = true;
                    DstDoc.SpellingChecked = true;
                    Console.WriteLine("Elapsed Time:  {0}", (dt - DateTime.Now).Duration().ToString());
                    Console.WriteLine(String.Format("FYI:  Record {0} close / reopened {1}.", id, DstFile));
                }
            }
            DstDoc.Close(ref doSaveChanges, ref Nothing, ref Nothing);
            AccessApp.DoCmd.Close(Access.AcObjectType.acForm, fmName);
            AccessApp.DoCmd.DeleteObject(Access.AcObjectType.acForm, fmName);

            AccessApp.CloseCurrentDatabase();
            Console.WriteLine("Elapsed Time:  {0}", (dt - DateTime.Now).Duration().ToString());
            Console.WriteLine("Press any key to continue and exit the application (closes this console window)");
            Console.ReadKey();
        }
    }
}

By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.

If a file you wish to view isn't highlighted, and is a text file (not binary), please let us know and we'll add colourisation support for it.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Mike Rosen
Architect LizardTech
United States United States
No Biography provided

| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.150123.1 | Last Updated 31 Jan 2013
Article Copyright 2012 by Mike Rosen
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid