Error exporting to MS Excel

[espositophp]

Hello, I have developed a program that exports the content of a StringGrid to MS Excel.

On my computer it works just fine but a lot of users who have installed my app told me they got the following error message:

Class not registered, ProgID: "Excel.Application".


This is the code I have used:

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, ComObj, Grids, ExtCtrls, StdCtrls, uSQLite3, SQLiteTable3;
 
 
function SaveAsExcelFile(AGrid: TStringGrid; ASheetName, AFileName: string): Boolean;
const
  xlWBATWorksheet = -4167;
var
  Row, Col: Integer;
  GridPrevFile: string;
  XLApp, Sheet, Data: OLEVariant;
  i, j: Integer;
begin
 
  Screen.Cursor := crHourGlass;
 
  // Prepare Data
  Data := VarArrayCreate([1, AGrid.RowCount, 1, AGrid.ColCount], varVariant);
 
  for i := 0 to AGrid.ColCount - 1 do
    for j := 0 to AGrid.RowCount - 1 do
      Data[j + 1, i + 1] := Trim(AGrid.Cells[i, j]);
 
  // Create Excel-OLE Object
  Result := False;
  XLApp := CreateOleObject('Excel.Application');
 
  try
    // Hide Excel
    XLApp.Visible := False;
    // Add new Workbook
    XLApp.Workbooks.Add(xlWBatWorkSheet);
    Sheet := XLApp.Workbooks[1].WorkSheets[1];
    Sheet.Name := ASheetName;
 
    Sheet.Range[RefToCell(1, 1), RefToCell(AGrid.RowCount, AGrid.ColCount)].Select;
    XLApp.Selection.NumberFormat := '@';
 
    // Fill up the sheet
    Sheet.Range[RefToCell(1, 1), RefToCell(AGrid.RowCount, AGrid.ColCount)].Value := Data;
 
    // Show Excel Worksheet
    try
 
      // Rende Bold le celle dei titoli
      XLApp.Application.Cells[1, 2].Font.Bold := True;
      XLApp.Application.Cells[1, 3].Font.Bold := True;
      XLApp.Application.Cells[1, 4].Font.Bold := True;
      XLApp.Application.Cells[1, 5].Font.Bold := True;
      XLApp.Application.Cells[1, 6].Font.Bold := True;
      XLApp.Application.Cells[1, 7].Font.Bold := True;
      XLApp.Application.Cells[1, 8].Font.Bold := True;
      XLApp.Application.Cells[1, 9].Font.Bold := True;
      XLApp.Application.Cells[1, 10].Font.Bold := True;
      XLApp.Application.Cells[1, 11].Font.Bold := True;
      XLApp.Application.Cells[1, 12].Font.Bold := True;
      XLApp.Application.Cells[1, 13].Font.Bold := True;
      XLApp.Application.Cells[1, 14].Font.Bold := True;
      XLApp.Application.Cells[1, 15].Font.Bold := True;
      XLApp.Application.Cells[1, 16].Font.Bold := True;
      XLApp.Application.Cells[1, 17].Font.Bold := True;
      XLApp.Application.Cells[1, 18].Font.Bold := True;
      XLApp.Application.Cells[1, 19].Font.Bold := True;
      XLApp.Application.Cells[1, 20].Font.Bold := True;
      XLApp.Application.Cells[1, 21].Font.Bold := True;
 
      XLApp.Columns['A:U'].EntireColumn.AutoFit;
 
      XLApp.Cells[1,1].Select;
 
      //Show Excel
      XLApp.Visible := True;
      Screen.Cursor := crDefault;
 
    except
      Screen.Cursor := crDefault;
      ShowMessage('An unforeseen error has occurred while exporting to Excel.');
    end;
 
  finally
    Screen.Cursor := crDefault;
  end;
 
end;

What am I missing?

Any help will be greatly appreciated.

 

[svanels]

Are you using some 3^rd party component to do this?
Perhaps you need to install the application that contains uSQLite3, SQLiteTable3

There is a FAQ about exporting data to excel, maybe it can be of some help.

Steven

 

[espositophp]

Hello, thanks for your reply.

Yes, I am using SQLITE but the database works without any problems.

When I use the above function, I only export to Excel the content of a StringGrid without making any reference to SQLITE.

The message "Class not registered, ProgID: Excel.Application" makes me understand that something is not properly registered in the Registry of the user's computer, but I don't know what it is and what I should tell the user.

Any further help will be appreciated.

 

[svanels]

I did not see OleServer, ExcelXP; or something similar connected with the servers tab

Steven

 

[whosrdaddy]

obvious stupid question, your users do have Excel installed?

-----------------------------------------------------
What You See Is What You Get
Never underestimate tha powah of tha google!

 

[espositophp]

That was the first question I had asked the users. They swear Excel is installed on their PCs and works fine.

The error message "Class not registered, ProgID: Excel.Application" speaks clearly: some key is not properly registered in the Registry of the user's computer, but I don't know what it is and what I should tell the user to do exactly.

I wondered how Excel could work if it were not correctly registered in the Windows Registry. Somebody answered me that it is possible because Excel does not depend upon that Registry entry - Excel could possibly refer to itself by Class GUID instead of ProgID.

So, I supposed some values in the Registry were deleted accidentally and I asked the users to reinstall Office, but even so the problem remained.

Please consider that I am not importing any Excel-connected library to my application. All I do is put ComObj in uses and the function I posted before. Again, the majority of my users are not having any problems using the export function but there are also some who get that error message.

Any further help will be appreciated.

 

[DjangMan]

One quick and dirty option would be to create a small VB app to invoke Excel - see if you have the same problem. You should be able to see the excel.application entry in the registry so perhaps you can get a copy of the appropriate registry keys to review from the 'bad' workstations. Could they have different versions of Excel?

You should re-arrange your code so that the VarArrayCreate is cleared - otherwise you'll lose a little bit of memory.

 

[espositophp]

Thanks for the tip about the additional VB app.

The incriminated application can be downloaded from my Website at the following address:

http://www.espositosoftware.it/videotecapro.htm

I would appreciate it very much if you could tell me whether the export function causes the same problem on your computers or not.

 

[DjangMan]

I have the error message. I'm running Win XP Pro SP2 and Office 2007. I'll be happy to help. But I'm traveling from Nigeria back to my home in Canada for the next three days so don't worry if I don't respond right away.

 

[whosrdaddy]

getting the same error message here.

I use this unit (found this one a long time ago on the net) to export my grids to Excel, you only need ADO on the client computer. follow the instructions in the unit.

{
  Exporting a DBGrid to excel without OLE

  I develop software and about 95% of my work deals with databases.
  I enjoied the advantages of using Microsoft Excel in my projects
  in order to make reports but recently I decided to convert myself
  to the free OpenOffice suite.
  I faced with the problem of exporting data to Excel without having
  Office installed on my computer.
  The first solution was to create directly an Excel format compatible file:
  this solution is about 50 times faster than the OLE solution but there
  is a problem: the output file is not compatible with OpenOffice.
  I wanted a solution which was compatible with each "DataSet";
  at the same time I wanted to export only the dataset data present in
  a DBGrid and not all the "DataSet".
  Finally I obtained this solution which satisfied my requirements.
  I hope that it will be usefull for you too.

  First of all you must import the ADOX type library
  which will be used to create the Excel file and its
  internal structure: in the Delphi IDE:

  1)Project->Import Type Library:
  2)Select "Microsoft ADO Ext. for DDL and Security"
  3)Uncheck "Generate component wrapper" at the bottom
  4)Rename the class names (TTable, TColumn, TIndex, TKey, TGroup, TUser, TCatalog) in
    (TXTable, TXColumn, TXIndex, TXKey, TXGroup, TXUser, TXCatalog)
    in order to avoid conflicts with the already present TTable component.
  5)Select the Unit dir name and press "Create Unit".
    It will be created a file named AOX_TLB.
    Include ADOX_TLB in the "uses" directive inside the file in which you want
    to use ADOX functionality.

  That is all. Let's go now with the implementation:
}

unit DBGridExportToExcel;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  ExtCtrls, StdCtrls, ComCtrls, DB, IniFiles, Buttons, dbgrids, ADOX_TLB, ADODB;


type TScrollEvents = class
       BeforeScroll_Event: TDataSetNotifyEvent;
       AfterScroll_Event: TDataSetNotifyEvent;
       AutoCalcFields_Property: Boolean;
  end;

procedure DisableDependencies(DataSet: TDataSet; var ScrollEvents: TScrollEvents);
procedure EnableDependencies(DataSet: TDataSet; ScrollEvents: TScrollEvents);
procedure DBGridToExcelADO(DBGrid: TDBGrid; FileName: string; SheetName: string);


implementation

//Support procedures: I made that in order to increase speed in
//the process of scanning large amounts
//of records in a dataset

//we make a call to the "DisableControls" procedure and then disable the "BeforeScroll" and
//"AfterScroll" events and the "AutoCalcFields" property.
procedure DisableDependencies(DataSet: TDataSet; var ScrollEvents: TScrollEvents);
begin
     with DataSet do
          begin
               DisableControls;
               ScrollEvents := TScrollEvents.Create();
               with ScrollEvents do
                    begin
                         BeforeScroll_Event := BeforeScroll;
                         AfterScroll_Event := AfterScroll;
                         AutoCalcFields_Property := AutoCalcFields;
                         BeforeScroll := nil;
                         AfterScroll := nil;
                         AutoCalcFields := False;
                    end;
          end;
end;

//we make a call to the "EnableControls" procedure and then restore
// the "BeforeScroll" and "AfterScroll" events and the "AutoCalcFields" property.
procedure EnableDependencies(DataSet: TDataSet; ScrollEvents: TScrollEvents);
begin
     with DataSet do
          begin
               EnableControls;
               with ScrollEvents do
                    begin
                         BeforeScroll := BeforeScroll_Event;
                         AfterScroll := AfterScroll_Event;
                         AutoCalcFields := AutoCalcFields_Property;
                    end;
          end;
end;

//This is the procedure which make the work:

procedure DBGridToExcelADO(DBGrid: TDBGrid; FileName: string; SheetName: string);
var
  cat: _Catalog;
  tbl: _Table;
  col: _Column;
  i: integer;
  ADOConnection: TADOConnection;
  ADOQuery: TADOQuery;
  ScrollEvents: TScrollEvents;
  SavePlace: TBookmark;
begin
  //
  //WorkBook creation (database)
  cat := CoCatalog.Create;
  cat.Set_ActiveConnection('Provider=Microsoft.Jet.OLEDB.4.0; Data Source=' + FileName + ';Extended Properties=Excel 8.0');
  //WorkSheet creation (table)
  tbl := CoTable.Create;
  tbl.Set_Name(SheetName);
  //Columns creation (fields)
  DBGrid.DataSource.DataSet.First;
  with DBGrid.Columns do
    begin
      for i := 0 to Count - 1 do
        if Items[i].Visible then
        begin
          col := nil;
          col := CoColumn.Create;
          with col do
            begin
              Set_Name(Items[i].Title.Caption);
              Set_Type_(adVarWChar);
            end;
          //add column to table
          tbl.Columns.Append(col, adVarWChar, 20);
        end;
    end;
  //add table to database
  cat.Tables.Append(tbl);

  col := nil;
  tbl := nil;
  cat := nil;

  //exporting
  ADOConnection := TADOConnection.Create(nil);
  ADOConnection.LoginPrompt := False;
  ADOConnection.ConnectionString := 'Provider=Microsoft.Jet.OLEDB.4.0; Data Source=' + FileName + ';Extended Properties=Excel 8.0';
  ADOQuery := TADOQuery.Create(nil);
  ADOQuery.Connection := ADOConnection;
  ADOQuery.SQL.Text := 'Select * from [' + SheetName + '$]';
  ADOQuery.Open;


  DisableDependencies(DBGrid.DataSource.DataSet, ScrollEvents);
  SavePlace := DBGrid.DataSource.DataSet.GetBookmark;
  try
  with DBGrid.DataSource.DataSet do
    begin
      First;
      while not Eof do
        begin
          ADOQuery.Append;
          with DBGrid.Columns do
            begin
              ADOQuery.Edit;
              for i := 0 to Count - 1 do
                if Items[i].Visible then
                  begin
                    ADOQuery.FieldByName(Items[i].Title.Caption).AsString := FieldByName(Items[i].FieldName).AsString;
                  end;
              ADOQuery.Post;
            end;
          Next;
        end;
    end;

  finally
  DBGrid.DataSource.DataSet.GotoBookmark(SavePlace);
  DBGrid.DataSource.DataSet.FreeBookmark(SavePlace);
  EnableDependencies(DBGrid.DataSource.DataSet, ScrollEvents);

  ADOQuery.Close;
  ADOConnection.Close;

  ADOQuery.Free;
  ADOConnection.Free;

  end;

end;

end.

-----------------------------------------------------
What You See Is What You Get
Never underestimate tha powah of tha google!

 

[whosrdaddy]

I tried your application and it's giving the error.

I tried your code and it works??

try to make a small testapp and let a user test it.

procedure TForm1.Button1Click(Sender: TObject);
var XLApp: OLEVariant;
begin
 XLApp := CreateOleObject('Excel.Application');
 XLApp.Visible:=True;
 showmessage('it works');
end;

this will show excel and closes it when the user clicks on the dialog. This works on the same computer as the one I tested your app on.
the problem could be linked to the delphi version you are using or maybe due to the installation (forgot any DLL's?)

try to sort it out, I think nothing's wrong with your code...

/Daddy

-----------------------------------------------------
What You See Is What You Get
Never underestimate tha powah of tha google!

 

[espositophp]

Thank you very much indeed for your help.

Is there anybody there who could export to Excel without getting the error message?

Thanks again.

 

[rcloutie]

I use something very similar but all users are using the same regional settings... The only thing I can see is maybe your locale...

Here's some code to export without formatting any cells:

for iRow := 0 to RowCount-1 do
begin
     for iCol := 1 to ColCount-1 do
     begin
          sTemp := Cells[iCol, iRow];
          sLine := sLine + #34 + sTemp + #34 + #09;
     end;
     sLine := sLine + #13;
     if iRow = 0 then sLine := sLine + #13;
end;
lstExcel := TStringList.Create;
lstExcel.Text := sLine;
lstExcel.SaveToFile('C:\test.xls');
lstExcel.Free;

Hope this helps and good luck,

Rej Cloutier

 

[espositophp]

Thanks.

Honestly, I don't think that the problem may be due to the cell formatting. Excel doesn't start at all and the reason for it seems to be connected with the absence of some key in the Windows Registry.

I would also not exclude that my locale may be responsible for it, as you suggested. I'm going to ask my users whether they have a non-Italian version of Office installed on their machines.

Today I formatted one of my notebook computers, installed Office 1997 (the oldest version I have) and tried my app. I didn't have any problems. Please consider that on the other machines I have MS Excel 2002. My application is developed in Delphi 2005.

I will appreciate any further help you may have to offer.

 

[rcloutie]

I don't think it's a cell's formatting issue neither.

I only suggested you to bypass the use of Excel object via late binding. If you're using a TStringList to save a xls file, you don't have to make any reference to the Excel.Application object.

I remember I had a problem using Excel to format data and solved it this way...

try
vSelect.NumberFormat := 'Standard' // French
except
vSelect.NumberFormat := 'General' // English
end;

Again, hope this helps,

Rej Cloutier

 

[espositophp]

Thanks again for your help. I am sure that the solution to my problem lies in late binding, as you said. Unfortunately, I don't know what changes I have to make to my code in order to switch from early binding to late binding.

The following is the original function I use in my application to export the content of a StringGrid to Excel. Can anybody please help me to modify the code below in such a way as to transform it into late binding?

I suppose the first thing I have to do is delete ComObj from uses. What should I do next?

function RefToCell(ARow, ACol: Integer): string;
begin
  Result := Chr(Ord('A') + ACol - 1) + IntToStr(ARow);
end;

function SaveAsExcelFile(AGrid: TStringGrid; ASheetName, AFileName: string): Boolean;
const
  xlWBATWorksheet = -4167;
var
  Row, Col: Integer;
  GridPrevFile: string;
  XLApp, Sheet, Data: OLEVariant;
  i, j: Integer;
begin

  Screen.Cursor := crHourGlass;

  // Prepare Data
  Data := VarArrayCreate([1, AGrid.RowCount, 1, AGrid.ColCount], varVariant);

  for i := 0 to AGrid.ColCount - 1 do
    for j := 0 to AGrid.RowCount - 1 do
      Data[j + 1, i + 1] := Trim(AGrid.Cells[i, j]);

  // Create Excel-OLE Object
  Result := False;
  XLApp := CreateOleObject('Excel.Application');

  try
    // Hide Excel
    XLApp.Visible := False;
    // Add new Workbook
    XLApp.Workbooks.Add(xlWBatWorkSheet);
    Sheet := XLApp.Workbooks[1].WorkSheets[1];
    Sheet.Name := ASheetName;

    Sheet.Range[RefToCell(1, 1), RefToCell(AGrid.RowCount, AGrid.ColCount)].Select;
    XLApp.Selection.NumberFormat := '@';

    // Fill up the sheet
    Sheet.Range[RefToCell(1, 1), RefToCell(AGrid.RowCount, AGrid.ColCount)].Value := Data;

    // Show Excel Worksheet
    try

      // Rende Bold le celle dei titoli
      XLApp.Application.Cells[1, 2].Font.Bold := True;
      XLApp.Application.Cells[1, 3].Font.Bold := True;
      XLApp.Application.Cells[1, 4].Font.Bold := True;
      XLApp.Application.Cells[1, 5].Font.Bold := True;
      XLApp.Application.Cells[1, 6].Font.Bold := True;
      XLApp.Application.Cells[1, 7].Font.Bold := True;
      XLApp.Application.Cells[1, 8].Font.Bold := True;
      XLApp.Application.Cells[1, 9].Font.Bold := True;
      XLApp.Application.Cells[1, 10].Font.Bold := True;
      XLApp.Application.Cells[1, 11].Font.Bold := True;
      XLApp.Application.Cells[1, 12].Font.Bold := True;
      XLApp.Application.Cells[1, 13].Font.Bold := True;
      XLApp.Application.Cells[1, 14].Font.Bold := True;
      XLApp.Application.Cells[1, 15].Font.Bold := True;
      XLApp.Application.Cells[1, 16].Font.Bold := True;
      XLApp.Application.Cells[1, 17].Font.Bold := True;
      XLApp.Application.Cells[1, 18].Font.Bold := True;
      XLApp.Application.Cells[1, 19].Font.Bold := True;
      XLApp.Application.Cells[1, 20].Font.Bold := True;
      XLApp.Application.Cells[1, 21].Font.Bold := True;

      // Adegua le dimensioni delle celle al loro contenuto
      XLApp.Columns['A:U'].EntireColumn.AutoFit;

      XLApp.Cells[1,1].Select;

      //Rende visibile il foglio di Excel
      XLApp.Visible := True;
      Screen.Cursor := crDefault;

    except
      Screen.Cursor := crDefault;
      ShowMessage('Si è verificato un errore durante l''esportazione dei dati in Excel.' + #13 + #10 + #13 + #10 + 'Accertarsi che il pacchetto Microsoft Office sia installato sul proprio computer.');
    end;

  finally
    Screen.Cursor := crDefault;
  end;

end;

I'm counting on your help. Thank you very much indeed.

 

[DjangMan]

You're doing late binding now. Early binding will make your application run faster but it can be trickier to program Excel because Delphi isn't as flexible with parameters. Here's some of my code to get Excel up and running.

Note that these are just chunks but they should get you started. It isn't a hard conversion and my code works with Excel 2000, 2003 and 2007.

uses
  SysUtils, Classes, OleServer, Excel2000;

//I've added an XLApplication component to my form.
  private
    { Private declarations }
    LCID               : cardinal;

  public
    { Public declarations }
    XLSheet   : _Worksheet;
    XLBook: _Workbook;

function TdmXL.StartExcel: boolean;
begin
  LCID := GetUserDefaultLCID;
  XLApp.Connect;
  result := true;
end;

function TdmXL.OpenExcelBook(const TemplateName: string): boolean;
begin
  try
    result := true;
    Forms.Application.ProcessMessages;

    if FileExists(TemplateName)=False then
    begin
      raise Exception.Create(TemplateName+' was not found.');
      result := false;
      exit;
    end
    else
      XLBook := XLApp.Workbooks.Open(TemplateName, '0', False,EmptyParam, EmptyParam, EmptyParam,                                  EmptyParam, EmptyParam, EmptyParam,                                     EmptyParam, EmptyParam, EmptyParam,                                     EmptyParam, LCID);
  except
    on e:exception do
    begin
      AddError('Error during Open Excel Book');
      result := false;
    end;
  end;
end;


procedure TdmXL.ShowExcel;
begin

  XLApp.CutCopyMode[LCID] := xlOff; //remove any selection box
  XLApp.ScreenUpdating[LCID] := True;

  XLApp.Visible[LCID] := True;

  if assigned(XLBook) then
    XLBook.Activate(LCID);

end;

procedure TdmXL.DisconnectExcel;
begin
  XLApp.Disconnect;
end;


//to work with a sheet I use:
  XLSheet := XLBook.Worksheets[aSheetNumber] as _Worksheet;

procedure TdmXL.ActivateSheet(const aSheetNo: integer);
begin
  (XLBook.Worksheets.Item[aSheetNo] as _Worksheet).Activate(LCID);
end;

//I think the code below should be referencing .Value2 - I'm not sure why the code doesn't.
procedure TdmXL.SetCellValue(const aCol, aRow: integer;
  const aCellValue: string);
begin
  XLSheet.Range[xlColID(aCol,aRow),xlColID(aCol,aRow)].Value := aCellValue;
end;

HTH

 

[espositophp]

Thanks for your code.

I'm very sorry if I didn't understand that mine was already late binding.

I thought the problem may be due to the ComObj that I put in Uses. I hoped I could get rid of that declaration as the error message my users get says that the Class is not registered (ProgID: "Excel.Application"). Isn't that Class connected with ComObj?

I'm new to Excel as I have been using VB6 for the last 12 years. In VB6 late binding can be done without having to declare any libraries in the Declarations section.

So, if the error message is not due to early binding, what may it depend on?

Thanks in advance for any further help.

 

[espositophp]

Hello, I have made significant changes to my software in order to allow it to export to Excel.

I would very much appreciate it if those who gave me a helping hand downloading the software and getting the error message while exporting to Excel would be so kind as to download the updated version and tell me whether the problem persists or not.

The page from which my software can be downloaded is the following:

http://www.espositosoftware.it/videotecapro.htm

Thank you very much indeed.

 

[whosrdaddy]

Hi espositophp,

tested your soft and Excel opens now, no more errors.

Vista Ultimate + Office2007 here

/Daddy




-----------------------------------------------------
What You See Is What You Get
Never underestimate tha powah of tha google!