OleObject using Delphi works differently in Office 365 and Office 2013

user_5311501 0
Viewed 1694

I am developing a small tool using Delphi Pascal to open an XLSX file, and write a cell on it. It behaves differently on a machine using Office 2013 and Office 365.

Here is the code:

var
  ExcelApp: OleVariant;
  anExcelFileName: String;
begin
  try
    ExcelApp := CreateOleObject('Excel.Application');
    anExcelFileName := 'D:\sample.xlsx';

    ExcelApp.Workbooks.Open(anExcelFileName);
    ExcelApp.Visible := True;

    ExcelApp.Workbooks[1].Sheets[1].Range['A1'].Value := 'HELLO';
  except
    on E: Exception do
      showMessage('Error on something: ' + E.Message);
  end;
end;

In Office 2013, the code will access file sample.xlsx in drive D, open it, and write HELLO in cell A1.

In Office 365, the code will open two files. First it will open sample.xlsx and it open a new blank workbook, and write HELLO in the new blank workbook.

How to get the old behavior in office 365?

delphi-2010 delphi excel
1 Answers

Your code fails because it makes the assumption that the workbook that you open will be the first one in the collection of workbooks, and that assumption does not always hold.

Workbooks.Open returns the newly opened workbook. Use that object for future references to the workbook. Like this:

var
  ExcelApp, Workbook: OleVariant;
  anExcelFileName: String;
begin
  try
    ExcelApp := CreateOleObject('Excel.Application');
    anExcelFileName := 'D:\sample.xlsx';

    Workbook := ExcelApp.Workbooks.Open(anExcelFileName);
    ExcelApp.Visible := True;

    Workbook.Sheets[1].Range['A1'].Value := 'HELLO';
  except
    on E: Exception do
      showMessage('Error on something: ' + E.Message);
  end;
end;
user_47966100
Related