2004. augusztus 10., kedd

Creating data in Excel and copying it to Word from MS Office 97


Problem/Question/Abstract:

How to transfer data from Excel to Word?

Answer:

Below is demonstration how to perform this task.

Add these global variables:

XLApp: Variant;
WordApp: Variant;

Add these constants:

xlWBATWorksheet = -4167;
wdDoNotSaveChanges = 0;

For creating data in Excel we shall start it first:

//Starting Excel application:
XLApp := CreateOleObject('Excel.Application');
// Making it visible:
XLApp.Visible := True;
// Adding workbook:
XLApp.Workbooks.Add[XLWBatWorksheet];
// Specifying name of worksheet:
XLApp.Workbooks[1].Worksheets[1].Name := 'Delphi Data';

Now inserting data to Excel:

procedure TForm1.InsertData2Excel;
var
  Sheet: Variant;
  i: Integer;
begin
  Sheet := XLApp.Workbooks[1].Worksheets['Delphi Data'];
  for i := 1 to 10 do
    Sheet.Cells[i, 1] := i;
end;

And copying data from Excel to Word.
This process consists of two phrases:

Data should be copied from Excel into Windows clipboard.
Data should be pasted from Windows clipboard into the Word.

For successful completion both Excel and Word should be running.

Copying data from Excel into Windows clipboard:

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

  Sheets := XLApp.Sheets;
  // Selecting our worksheet:
  Sheets.Item['Delphi Data'].Activate;
  // Selecting our cells:
  Sheets.Item['Delphi Data'].Range['A1:A10'].Select;
  // Copying selected cells into clipboard:
  Sheets.Item['Delphi Data'].UsedRange.Copy;
  // Inserting copied data into Word
  InserData2Word;
end;

procedure TForm1.InsertData2Word;
var
  Range: Variant;
  i: Integer;
begin
  // Starting Word:
  WordApp := CreateOleObject('Word.Application');
  // Making it visible:
  WordApp.Visible := True;
  // Adding new document:
  WordApp.Documents.Add;
  // Inserting description text into new document:
  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;
  // Inserting data from clipboard
  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;

Don't forget to close Excel and Word by your program termination:

procedure TForm1.FormDestroy(Sender: TObject);
begin
  if not VarIsEmpty(XLApp) then
  begin
    XLApp.DisplayAlerts := False; // Discard unsaved files....
    XLApp.Quit;
  end;

  if not VarIsEmpty(WordApp) then
  begin
    WordApp.Documents.Item(1).Close(wdDoNotSaveChanges);
    WordApp.Quit;
  end;
end;

Nincsenek megjegyzések:

Megjegyzés küldése