Frederic Bell - 2008-06-20

I chose officepartner over using the servers because it was easier to learn. My project required me to open an existing spreadsheet (to print invoices) and populate it with data in a loop, one spreadsheet at a time. At first, my idea was to open and close Excel in each loop but I received an OLE error when creating a range that already existed. To remedy that, I made sure to CurrWorkBook.AsWorkbook.Close( before OpExcel.disconnect. But then, I realized I could open the workbook just once and keep it open, and only close it at the end. Success.

I'm including the source code of the main loop here for your interest. (I know it would have helped me if I'd had it when I started)

begin

  Count := QryControl.RecordCount;
  Counter := 0;

  if OpExcel1.Connected then
    raise exception.create('OpExcel is still connected!')
  else
    OpExcel1.connected := true;

  OpExcel1.Visible := not cbAutoPrint.Checked;
  OpExcel1.Caption := 'Tax/Commerical Invoice';
  if cbAutoprint.Checked then
    OpExcel1.WindowState := xlwsNormal;
  FileName := ExtractfilePath(application.ExeName) + 'New Invoice 2008.xls';

  // connect before add
  CurrWorkBook := OpExcel1.Workbooks.Add;
  CurrWorkBook.Filename := Filename;
  CurrWorkBook.Activate;

  Range := CurrWorkbook.Worksheets[0].Ranges.Add;
  // Range := OpExcel1.Workbooks[0].WorkSheets[0].Ranges.Add;
  Range.name := 'Page1';
  Range.Activate;

  Range := CurrWorkbook.Worksheets[1].Ranges.Add;
  // Range := OpExcel1.Workbooks[0].WorkSheets[1].Ranges.Add;
  Range.name := 'Addendum';
  Range.Activate;

  While not QryControl.Eof do
  begin

    inc(Counter);

    // --------
    try
      Populate(CurrWorkBook, QryControl);
    except
      on e: Exception do
      begin
        MessageDlg('An error occured: '+ e.Message + #13#10 +
        'Press a key to exit', mtError, [mbOk], 0);
        raise;
      end;
    end;
    // --------

    // CurrWorkBook.SaveAs(FileName);
    if cbAutoPrint.Checked then
      begin
        CurrWorkBook.Print;
        // OpExcel1.Workbooks[0].Print;
        // close before disconnect
        //CurrWorkBook.AsWorkbook.Close(False, EmptyParam, EmptyParam, 0);
        //OpExcel1.Connected := False;
      end
    else
      begin
        beep;
        if (Counter < Count) then
        begin
          if (MessageDlg('View the next invoice?', mtConfirmation, [mbOk, mbCancel], 0) = mrCancel) then
            BREAK;
        end
        else
          MessageDlg('Close the last invoice?', mtConfirmation, [mbOk], 0);
        // CurrWorkBook.AsWorkbook.Close(False, EmptyParam, EmptyParam, 0);
        // OpExcel1.connected := False;
      end;

    QryControl.Next;
  end;

  QryControl.Close;
  CurrWorkBook.AsWorkbook.Close(False, EmptyParam, EmptyParam, 0);
  OpExcel1.connected := False;
  if cbAutoPrint.Checked then
    ShowMessage('Printing Completed!');

end;