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:
Next, we will insert a date into the header while the macro is recording.
Next, we stop the macro and look at the code it created. Click on Tools > Macro > Macros and this window comes up:
Select the macro you just recorded and click Edit.
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:
object fileName = "c:\\temp\\test.doc";
object read_only = false;
object visible = true;
object isFalse = false;
object datetime = @"DATE \@ ""yyyy/MM/dd hh:mm:ss""";
object fieldtype =
Microsoft.Office.Interop.Word.WdFieldType.wdFieldDate;
object missing = System.Reflection.Missing.Value;
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.