Click here to Skip to main content
11,575,635 members (62,085 online)
Click here to Skip to main content

A little known trick to help with MS Office automation

, 2 Dec 2005 CPOL 134.1K 53
Rate this:
Please Sign up or sign in to vote.
This article exposes a trick to help in creating apps with MS Office automation.

Introduction

Have you ever wondered how to do Microsoft Office automation? Do you have some experience with Word or Excel or some other product, but you just can’t seem to program it to do what you want? Well, I discovered a neat little trick, which I am going to share with you, that has often helped me figure out how to do Office automation.

Background

What is Microsoft Office automation? It is the process of using OLE and COM object hooks into the Microsoft Office suite in your programming code. Why would you want to do this? I have found that often there is a need to do something or display something and it would be really handy if we could just use MS Office behind the scenes to do it. Perhaps you want some custom output in Word or Excel format. In many cases, you can use some reporting tool that will export to RTF or Excel. Still, sometimes the output you are trying to create is unique enough that you need to program it with MS Office automation.

The most recent thing I have done with MS Office automation was I used Word to add a page footer with a field that had the current date time stamp on it. My application was already using Word to print RTF documents. The RTFs were created using Crystal Reports. The users wanted a printed date on the RTF. The only way I could think of doing it was using Word Automation.

The Trick

One word: Macros. I know it sounds too easy. I found, a few years ago, when I was doing some Excel automation in a Delphi app, that I could not find any good reference on the web or a book that would help me figure out what I wanted to do. Anyway, I finally figured out that if I started to record a macro in Excel (this works in Word etc.) and did whatever I was trying to do in automation, I found that the macro code was always very close to the code I needed to write in automation to get the same results.

An Example

Here is how you start a macro in word:

Starting a Macro in Word

Next, we will insert a date into the header while the macro is recording.

Insert DateTime in Header

Next, we stop the macro and look at the code it created. Click on Tools > Macro > Macros and this window comes up:

Edit the Macro code

Select the macro you just recorded and click Edit.

The Macro code

You do have to know what code you are looking for. This is the macro code we are interested in:

ActiveWindow.ActivePane.View.SeekView = wdSeekCurrentPageHeader
Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldDate

The C# code looks like this:

wordApp.ActiveWindow.ActivePane.View.SeekView = 
Microsoft.Office.Interop.Word.WdSeekView.wdSeekCurrentPageFooter;

wordApp.Selection.Fields.Add(wordApp.Selection.Range,
ref fieldtype, ref datetime, ref isFalse);

You can see how similar the code is to the macro code. Here is the full source to add the current date-time to a Word document:

// give any file name of your choice. 
object fileName = "c:\\temp\\test.doc"; 
object  read_only  = false;
object  visible  = true;
object  isFalse    = false;

//provide a date format for the date we are inserting.
object  datetime = @"DATE \@ ""yyyy/MM/dd hh:mm:ss""";
object  fieldtype = 
Microsoft.Office.Interop.Word.WdFieldType.wdFieldDate;

//  the way to handle parameters you don't care about in .NET 
object missing = System.Reflection.Missing.Value; 

//   Open the document that was chosen by the dialog 
Microsoft.Office.Interop.Word.ApplicationClass wordApp = 
new Microsoft.Office.Interop.Word.ApplicationClass();

wordApp.Visible = false;

Microsoft.Office.Interop.Word.Document aDoc = 
wordApp.Documents.Open( 
    ref fileName, ref missing, ref read_only, 
    ref missing, ref missing,
    ref missing, ref missing, ref missing, ref missing,
    ref missing, ref missing, ref visible, ref missing, 
        ref missing, ref missing, ref missing );

try
{
    wordApp.ActiveWindow.ActivePane.View.SeekView = 
      Microsoft.Office.Interop.Word.WdSeekView.wdSeekCurrentPageFooter;

    wordApp.Selection.Fields.Add(wordApp.Selection.Range,
ref fieldtype, ref datetime, ref isFalse);
    aDoc.Save();
                
}
finally
{
    aDoc.Close(ref isFalse, ref missing, ref missing);
    wordApp.Quit(ref isFalse,ref missing,ref missing);
}

Conclusion

So I suggest if you find yourself in need of using MS Office automation and you are just not sure how to do what you need to do, record a macro while you are doing it and then look at the macro code. There is a good chance that the macro code will point you in the right direction for the code you need to write.

License

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

Share

About the Author

kubben
Web Developer
United States United States
I started my programmer career over 18 years ago doing COBOL and SAS on a MVS mainframe. It didn't take long for me to move into windows programming. I started my windows programming in Delphi (Pascal) with a Microsoft SQL server back end. I started working with vb.net when the beta 2 came out in 2001. After spending most of my programming life as a windows programmer I started to check out asp.net in 2004. I achieved my MCSD.net in April 2005. I have done a lot of MS SQL database stuff. I have a lot of experience with Window Service and Web services as well. I spent three years as a consultant programing in C#. I really enjoyed it and found the switch between vb.net and C# to be mostly syntax. In my current position I am programming in both vb.net and C#. Lately I have been using VS2012 and writing a Windows 8 app. You can search for the app it is called ConvertIT.

On a personal note I am a born again Christian, if anyone has any questions about what it means to have a right relationship with God or if you have questions about who Jesus Christ is, send me an e-mail. ben.kubicek[at]netzero[dot]com You need to replace the [at] with @ and [dot] with . for the email to work. My relationship with God gives purpose and meaning to my life.

You may also be interested in...

Comments and Discussions

 
GeneralYou saved my bacon Pin
sgharp22-Oct-09 9:54
membersgharp22-Oct-09 9:54 
GeneralRe: You saved my bacon Pin
kubben22-Oct-09 11:05
memberkubben22-Oct-09 11:05 
General2 issues on your article Pin
BobCarp9-Jun-09 8:14
memberBobCarp9-Jun-09 8:14 
GeneralRe: 2 issues on your article Pin
kubben9-Jun-09 17:07
memberkubben9-Jun-09 17:07 
GeneralNice! Thanks Pin
PHinker2-May-09 1:55
memberPHinker2-May-09 1:55 
GeneralRe: Nice! Thanks Pin
kubben2-May-09 1:57
memberkubben2-May-09 1:57 
GeneralRe: Nice! Thanks Pin
PHinker2-May-09 4:26
memberPHinker2-May-09 4:26 
GeneralRe: Nice! Thanks Pin
kubben2-May-09 9:11
memberkubben2-May-09 9:11 
QuestionRecord macro In C# Pin
Member 41487886-Nov-08 22:23
memberMember 41487886-Nov-08 22:23 
AnswerRe: Record macro In C# Pin
kubben7-Nov-08 0:20
memberkubben7-Nov-08 0:20 
GeneralRe: Record macro In C# Pin
Member 41487887-Nov-08 2:00
memberMember 41487887-Nov-08 2:00 
GeneralRe: Record macro In C# Pin
kubben7-Nov-08 2:15
memberkubben7-Nov-08 2:15 
GeneralRe: Record macro In C# Pin
SREHMANISLD13-Oct-11 19:30
memberSREHMANISLD13-Oct-11 19:30 
QuestionHow to end the table in the word document automation Pin
Member 418197531-Mar-08 17:49
memberMember 418197531-Mar-08 17:49 
AnswerRe: How to end the table in the word document automation Pin
kubben1-Apr-08 1:47
memberkubben1-Apr-08 1:47 
QuestionHow can we create a word document with footer on each page and the text in footer is like pages X of Y Pin
Member 418197530-Mar-08 20:59
memberMember 418197530-Mar-08 20:59 
AnswerRe: How can we create a word document with footer on each page and the text in footer is like pages X of Y Pin
kubben31-Mar-08 1:11
memberkubben31-Mar-08 1:11 
GeneralRe: How can we create a word document with footer on each page and the text in footer is like pages X of Y Pin
Member 418197531-Mar-08 17:45
memberMember 418197531-Mar-08 17:45 
GeneralRe: How can we create a word document with footer on each page and the text in footer is like pages X of Y Pin
kubben1-Apr-08 1:45
memberkubben1-Apr-08 1:45 
QuestionHow can we add field to a table cell placed in header section. Pin
Qasim Raza6-Apr-07 7:06
memberQasim Raza6-Apr-07 7:06 
AnswerRe: How can we add field to a table cell placed in header section. Pin
kubben6-Apr-07 8:26
memberkubben6-Apr-07 8:26 
GeneralAvoid POP ups Pin
IamADotNetGuy10-Aug-06 3:09
memberIamADotNetGuy10-Aug-06 3:09 
GeneralRe: Avoid POP ups Pin
kubben10-Aug-06 3:55
memberkubben10-Aug-06 3:55 
GeneralLot of problems with office automation Pin
IamADotNetGuy4-Aug-06 1:12
memberIamADotNetGuy4-Aug-06 1:12 
GeneralRe: Lot of problems with office automation Pin
kubben4-Aug-06 3:05
memberkubben4-Aug-06 3:05 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.150624.2 | Last Updated 2 Dec 2005
Article Copyright 2005 by kubben
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid