Delphi 3 - Delphi and Microsoft Office: Automating Excel and Word - Page 5

By: Charles Calvert

Abstract: The goal of this two-part paper is to get you up to speed automating Excel and Word from a Delphi application.

Delphi and Microsoft Office: Automating Excel and Word
Copyright © 1997 by Charlie Calvert
Here is the examples.zip file.

Copying Data from Excel to Word
The process of copying data from Excel to Word has two parts. The first part involves copying the data to the clipboard, and the second part involves pasting the data into the Word document. In other words, you have to have both Excel and Word open to make this work. Furthermore, the tricky part is not so much copying the data from Excel, but inserting it correctly into Word.

Below this paragraph is the procedure which copies the data from Excel to the clipboard. Note that I have created two methods called CopyCellsToWord and CopyChartToWord. These methods handle the Word side of the process, allowing me to isolate the Excel code in on method:

procedure TForm1.CopyData;
var
  Sheets: Variant;
begin
  SetFocus;
  
  Sheets := XLApp.Sheets;

  Sheets.Item['Delphi Data'].Activate;
  Sheets.Item['Delphi Data'].Range['A1:A10'].Select;
  Sheets.Item['Delphi Data'].UsedRange.Copy;

  CopyCellsToWord;

  Sheets.Item['Chart1'].Select;
  XLApp.Selection.Copy;

  CopyChartToWord;

end;

To copy data from a range in a worksheet to the clipboard I first retrieve a Sheets object, then do the following:

  Sheets.Item['Delphi Data'].Activate;
  Sheets.Item['Delphi Data'].Range['A1:A10'].Select;
  Sheets.Item['Delphi Data'].UsedRange.Copy;

I first Activate the worksheet, then select a range of data in it, and finally copy the data to memory. In this process, I essentially mirror the actions I would take were I doing this manually. In other words, I first "click" on the work sheet I want to use, that is, I activate it. I then select a range of data from it, and finally I "press Ctrl-C" to copy it to the clipboard. Of course, I'm not really doing these things, but I'm executing in code the steps necessary to duplicate these actions as follows:

Call Activate Click on page with mouse
Call Select Select data with the mouse
Call Copy Press Crtl-C or pull down the Edit menu and choose Copy

After the program copies a range of cells to memory, the next step is to copy the cells to Word. In this explanation, however, I will temporarily pass over the act of copying the data to word, and instead show you how to copy the Chart to memory. Note however, that you obviously must do these things one at a time, since the clipboard can only hold one object in memory at a time. In short, you can't copy both the worksheet and the chart to two separate places in the clipboard, then copy them both to Word in one motion. The problem, of course, is that the clipboard has only one area available in memory.

Here is how to copy a chart to the clipboard:

Sheets.Item['Chart1'].Select;
XLApp.Selection.Copy;

This code first selects Chart1, then copies it to the clipboard. Again, I am mirroring the actions I would take where I doing this all manually. That is, I first select the object, then "press Ctrl-C" to copy it. Once again, I don't explicitly press Crtl-C, but instead perform the steps in code that duplicate this action.

Automation Inside Word
In the next few paragraphs I will be discussing the following procedure, which gets you up and running with Automation in Word:

procedure TForm1.CopyCellsToWord;
var
  Range: Variant;
  i: Integer;
begin
  WordApp := CreateOleObject('Word.Application');
  WordApp.Visible := True;
  WordApp.Documents.Add;
  Range := WordApp.Documents.Item(1).Range;
  Range.Text := 'This is a column from a spreadsheet: ';
  for i := 1 to 3 do WordApp.Documents.Item(1).Paragraphs.Add;
  Range := 
    WordApp.Documents.Item(1).Range(WordApp.Documents.Item(1).       
    Paragraphs.Item(3).Range.Start);
  Range.Paste;
  for i := 1 to 3 do WordApp.Documents.Item(1).Paragraphs.Add;
end;

To get started in Word, you just follow more or less the same steps you would in Excel:

  WordApp := CreateOleObject('Word.Application');
  WordApp.Visible := True;
  WordApp.Documents.Add;

This code creates a Word Application object, sets the Visible property of the object to True, and adds a single document to it.

To add text to the document, you can execute the following code:

  Range := WordApp.Documents.Item(1).Range;
  Range.Text := 'This is a column from a spreadsheet: ';

In this case the code retrieves a Range object representing the entire document, which of course starts out completely empty. To start to place text in the document, you can use the Text property of the Range.

You could simply paste the data from Excel directly in your document. However, you want to be able to have some control over the location where the cells are placed. To do this, you need some white space in the document, that is, you need a series of carriage returns through which you can iterate:

for i := 1 to 3 do WordApp.Documents.Item(1).Paragraphs.Add;

You can now use the Goto method of the Range or Document object to move back and forth across this range of paragraphs. Or, if you want, you can select a new Range, and then Paste your Excel data into that Range. In my experience, this second method is a simpler method of moving through a document. Here is the code for selecting a Range covering the third paragraph of a document:

Range := 
    WordApp.Documents.Item(1).Range(WordApp.Documents.Item(1).       
    Paragraphs.Item(3).Range.Start);

Please note that this is one single line of code. I wrap it here because I must in order to fit it in a word processing or HTML document. In your code, however, you want to type it in on one single line. This code states that I want to define a Range on the third paragraph of the document. I explicitly state that the Range starts at the beginning of the paragraph, but I do not define the end of the Range. In a moment I will show you how to also specify the end of a Range.

I can now Paste in the Excel code with a single easy to write line:

Range.Paste;

After pasting in this Range of Cells, I find that several new Paragraphs have been added to my document. There is no specific way for me to be sure how many, since the number of cells I paste in may vary with different versions of my program. So when get ready to paste in the Chart from Excel, I begin by asking how many paragraphs are currently in the document:

procedure TForm1.CopyChartToWord;
var
  Range: Variant;
  i, NumPars: Integer;
begin
  NumPars := WordApp.Documents.Item(1).Paragraphs.Count;
  Range := WordApp.Documents.Item(1).Range(
    WordApp.Documents.Item(1).Paragraphs.Item(NumPars).Range.Start,
    WordApp.Documents.Item(1).Paragraphs.Item(NumPars).Range.End);

  Range.Text := 'This is graph: ';
  for i := 1 to 3 do WordApp.Documents.Item(1).Paragraphs.Add;

  Range := WordApp.Documents.Item(1).Range(
    WordApp.Documents.Item(1).Paragraphs.Item(NumPars + 2).Range.Start,
    WordApp.Documents.Item(1).Paragraphs.Item(NumPars + 2).Range.End);

  Range.PasteSpecial(,,,,wdPasteOleObject);  
end;

I return the paragraph count in the variable NumPars. I then create a domain that ranges over the last paragraph of the document. In other words, I count the paragraphs in the document, and then say I want to establish a Range on the last paragraph. Once again, this is one way to position yourself in the document:

Range := WordApp.Documents.Item(1).Range(
WordApp.Documents.Item(1).Paragraphs.Item(NumPars).Range.Start,
WordApp.Documents.Item(1).Paragraphs.Item(NumPars).Range.End);

Once I've located myself in the proper position, the next step is to enter a single descriptive line of text, followed by a few additional paragraphs:

Range.Text := 'This is graph: ';
for i := 1 to 3 do WordApp.Documents.Item(1).Paragraphs.Add;

I then once again position myself on the last paragraph in the document:

Range := WordApp.Documents.Item(1).Range(
  WordApp.Documents.Item(1).Paragraphs.Item(NumPars + 1).Range.Start,
  WordApp.Documents.Item(1).Paragraphs.Item(NumPars + 1).Range.End);

Notice that when creating this Range I explicitly state that it "ranges" from the beginning of the paragraph to the end. In an earlier example, I filled in the first part of the range, but left the second part open. In this case, either method would work, but I show you both so you can see various examples of how the syntax works.

In that same spirit, I use a slightly different technique when pasting in the Chart data:

Range.PasteSpecial(,,,,wdPasteOleObject);  

In this case I call the PasteSpecial method, and ask to insert an OLE object. This insures that you can edit the document in place by double clicking on it. The PasteSpecial method takes a wide range of parameters, but I won't discuss them here, since that subject is fairly tangential to this paper. For more details, look up PasteSpecial in the Word Visual Basic help. Remember that this help file is not installed by default, so you may need to run the Word install and explicitly ask for the file, or else copy it off the CD manually.

Mailing a Document
The Documents object also has Save, SaveAs, and Open methods you can use when opening or saving a document. In fact, there are many methods and properties associated with most of the objects discussed in this article. The only way to get to know them all is to open up the Word or Excel help and start browsing through them. This article is meant to give you a conceptual overview of how to use Word and Excel automation objects. It is not a complete examination of the subject.

Here is how to save the current Word document:

WordApp.Documents.Item(1).SaveAs('c:foo.doc');

The following code allows you to send a mail message:

procedure TForm1.MailDocument;
begin
  WordApp.Documents.Item(1).SaveAs('c:foo.doc');
  WordApp.Options.SendMailAttach := True;
  WordApp.Documents.Item(1).SendMail;
end;

If you have Microsoft Mail set up on your computer, you can send a document to another user directly from Excel. The code for doing this is shown above. This code will automatically pop up the mail services, allowing you to pick a user and send the document. The document you created will automatically be attached to your message. If you don't have mail set up on your machine, then this code obviously won't work.

Summary of Part I
That is all I'm going to say on the basic steps involved with automating Excel and Word from Delphi. This is obviously a large subject, and much more could be said about it. However, the information you have seen here should open up the topic sufficiently to allow you to perform most tasks.

By now it should be obvious to you that the automation objects in Excel and Word are extremely powerful. Were you creating an actual word processor, you couldn't ask for much more in terms of functionality, but of course you would look for a little better performance!

The next installment of this paper, found in Part II, will cover interfaces and dispinterfaces. These are extremely important topics, and indeed your education in automating Excel or Word is definitely not complete without an understanding of these important topics. In fact, you will find that in Part II, I rewrite all the code shown you so far, giving you a second, somewhat more complicated, but considerably more powerful set of tools. So far I've shown you code that duplicates more or less what you can do in Visual Basic. The second part of the article shows how you can leverage the power of Delphi to take full advantage of automation.


Server Response from: ETNASC01