CODEWITHSUNDEEP
delphi
Serkan Ulusoy
Serkan Ulusoy

Reputation: 25

DELPHI "the object invoked has disconnected from its clients"

When excel runs, the data file that I process is opened. (and my program stops if I close this file)

unit UBImportFromExcel;

interface

uses
  MainDM, Contnrs, DB, Classes, UGeneral, BSYSTMGlobals,
  SysUtils, ADODB, BDataSet, NThermo, ComObj, Graphics, Forms, ShellAPI, Windows, Math, DBClient;

type

  TBImportFromExcel = class
    private
      ExcelApp, ExcelWorkbook, ExcelWorksheet,
      Unassigned : Variant;
      iStartingRow : Integer;
      iSheetCount : Integer;
      iRowCount : Integer;
    public
      function ReadCell(iRow : Integer; iColumn : Integer) : string;
      procedure Worksheet(iWorksheetIndex : integer = 1);
    protected

    published
      property StartingRow : Integer read iStartingRow write iStartingRow;
      property SheetCount : Integer read iSheetCount;
      property RowCount : Integer read iRowCount;

      constructor Create(sFile : string); virtual;
      destructor Destroy; override;
  end;


implementation

{ TBImportFromExcel }

constructor TBImportFromExcel.Create(sFile : string);
begin
  ExcelApp        := CreateOleObject('Excel.Application');
  ExcelWorkbook   := ExcelApp.WorkBooks.Open(sFile);
  iSheetCount     := ExcelWorkbook.Worksheets.Count;
  iStartingRow    := 2;
end;

destructor TBImportFromExcel.Destroy;
begin
  ExcelWorkbook.Close(False);
  ExcelWorksheet := Unassigned;
  ExcelWorkbook := Unassigned;
  ExcelApp := Unassigned;
  inherited;
end;

function TBImportFromExcel.ReadCell(iRow, iColumn: Integer): string;
var
  oCell : OleVariant;
begin
  oCell       := ExcelWorksheet.Cells[iRow, iColumn];
  Result      := oCell.Value;
end;

procedure TBImportFromExcel.Worksheet(iWorksheetIndex: integer);
begin
  ExcelWorksheet  := ExcelWorkbook.Worksheets.Item[iWorksheetIndex];
  ExcelWorksheet.Activate;
  ExcelWorksheet.Select;
  iRowCount       := ExcelWorksheet.UsedRange.Rows.Count;
end;

end.

img1 img2 img3

If the excel file opened by itself is closed, I get the error in the header and my program is breaking. sorry for my bad english. createoleobject cannot create a standalone object. why?

Upvotes: 1

Views: 326

Answers (1)

MartynA
MartynA

Reputation: 30715

If I have understood you correctly, I think I may have a solution to your problem.

As well as Visible, Excel's OLE object has two other relevant properties:

  • DisplayAlerts which determines whether alerts produced by the Exccel application are displayed on-screen; and

  • Interactive, which determines whether the Excel object (created using CreateOleObject) can interact with an instance of Excel started by the user via the Windows gui. If it is set to False, the Excel OLE instance is completely "insolated" from the user's desktop instance.

Both of these are documented by MS, see here for the documentation for Interactive.

So I created a simple test app with just two buttons and two checkboxes and this code

procedure TForm1.Start;
begin
  sFile := ExtractFilePath(Application.ExeName) + 'Test.Xlsx';
  ExcelApplication := CreateOleObject('Excel.Application');

  ExcelApplication.Interactive := cbInteractive.Checked;
  ExcelApplication.Visible := cbVisible.Checked;
  ExcelApplication.DisplayAlerts := ExcelApplication.Visible; // no point in displaying alerts if Excel app is not visible

  ExcelWorkbook  := ExcelApplication.WorkBooks.Open(sFile);
end;

procedure TForm1.Stop;
begin
  ExcelWorkBook.Close;  // Needed to hide Excel if it was started with Visible = True
  ExcelApplication.Quit;
  ExcelWorkBook := Unassigned;
  ExcelApplication := Unassigned;
end;

procedure TForm1.btnStartClick(Sender: TObject);
begin
  Start;
end;

procedure TForm1.btnStopClick(Sender: TObject);
begin
  Stop;
end;

Setting the cbInteractive checkbox to False does indeed appear to isolate the OLE-created instance from any interactively-created one.

Anyway, have an experiment with that and see if it meets your needs.

Upvotes: 2

Related Questions