Copy data from DBGrid into Excel

forgot to mention something about selecting result of the query...

That 's easy too :

1. Query1.First
2. Copy all the fields of the record to the Excel sheet.
3. Query1.Next
4. Same as 2.
5. Repeat Until Query1.EOF

Delphi code :
var sheet: variant;
    i,j : integer;

begin
 Sheet := XLApp.Workbooks[1].WorkSheets['Delphi Data'];
 Query1.First;
 j := 0;
 While not Query1.EOF do begin
  i := 0;
  {iterate through your fields of record}
   begin    
    Sheet.Cells[j, i] := {field value};
    inc(i);
   end;
  Query1.Next;
  inc(j);
 end;
end;

look for more at the examples I gave you.

Kind regards, Zif.

I have already answered this question, see:
https://www.experts-exchange.com/topics/comp/lang/delphi/Q.10045961

Hi Zifnab

Thanks for your reply I'll check it out and get back to you.

Rgds John

You know your stuff with OleExcell Zif ;)

Dear tam97
For this I generally use Formula1 components that ships with delphi 3 on the activexpage.
Simply copy the results of the query to the formula1 cells and then use the f1book method savefiledlg as given below.

Syntax

F1Book1.SaveFileDlg pTitle, pBuf, pFileType

Part      Type      Description
pTitle      String      Title of the dialog box. Use a null string for the default title.
pBuf      String      Variable, passed by reference, that receives the returned name by which the workbook is saved.
pFileType      Integer      Variable, passed by reference, that receives the returned file type selected when the file is saved. Following are the F1FileTypeConstants:
Constants      Description
F1FileFormulaOne      Formula One format
F1FileExcel4      Excel 4.0 format
F1FileExcel5      Excel 5.0 format
F1FileFormulaOne3      Formula One v.3 format
Remarks
For more details refer to formula1 help.

regards
venks
The Save As dialog box allows you to save and name a file.

Hi Venks

Nice to hear from you again.

Will have a look at the Formula1 suggestion and get back to you.

Rgds John

Hi Venks

Had a look at the Formula 1 help and component, it seems a bit complex to me (I am a bit of a beginner).

Would it be possible to provide sample code for say a query that selects all from a table into the formula 1 component.

It may make it easier for me to understand.

Rgds John

Hi Zif

I tried your coding and understand what its trying to achieve, but I came against some compiling problems.

CreateOleObject('Excel.Application');
Sheet := XLApp.Workbooks[1].WorkSheets['Delphi Data'];
Sheet.Cells[j, i] := {field value};

There were the area's that throw a wobbly, it's more than likely due to my lack of understanding.

I downloaded the Excel and qexport zips, but had a major problem loading them into Delphi, the Vcl30.dpl file kept getting corrupted, maybe its because my system is using Windows 98 and the disk is 32 bit converted, who knows.

Any ideas on the coding problems stated above.

Thanks again

Rgds John

Hi tam97,

 Ok, I see, some problems, because I forgot to say that you needed to have a sheet already called 'Delphi pages'. Well, since you're a beginner I better give you an URL of a guy who wrote a great article about this stuff. I've you've read it and still got some problems, just ask!

http://members.aol.com/charliecal/

got to OLE automation: Delphi, Word and Excel.

Happy reading! It's clearly written!

Zif.

Hi Jeurk! ;-)

Hi Zif

Thanks again for your patience with me, I'll download the article you have suggested.

Looks like I know what my bedtime reading is for tonight!.

Many regards,

John

Hi Zif

Read through the article on OLE automation it was very helpful.

I now have a better understanding of what to do, so thanks for all your help, please accept the 250 points.

By the way I'd like to finish all the examples in the article but can not locate XLConst.dcu on my system, any ideas where I can get this file, it seems to be required for example 3 when it uses ComObj and XLConst.

Thanks again.

John

Hi John,

About XLConst, I believe you can download it from Charlies page too (they are included with the examples), or ... you can make it yourself. How to do this, you may ask?, well the explenation is also written in the article :

Finding the Constants Used in Excel

You can determine all the constants used by Excel by reading its type library. There are at least two simple ways to read a type library.

   1.You can read the type library with a third party tool, such as the OleView application that ships with the Microsoft SDK.
   2.You can ask Delphi to read the library for you, and to translate the information stored in the library into Object Pascal. Obviously, this is the preferred
     technique.

I have included the translations of the Excel and Word type libraries with this article. However, if you want to create your own versions of these libraries, then you
can select Project | Import Type Library from the Delphi menu, and then select the appropriate type library. A Delphi translation of the type library will be created
automatically. (Be sure you are using Delphi 3.01 or later when importing the type library, as some important fixes were made to this technology between Delphi 3.0
and Delphi 3.01.)

There will be a number of warnings at the top of the type library, but you can ignore them. Most of these warnings have to do with name conflicts. For instance, if
Excel uses an Object Pascal keyword in its code, then we need to make an alias for that keyword. For instance, Excel uses End in several places. This is a Delphi
keyword, and so we append a space on to the end of it, converting the word End to End_. This puts an end to name conflicts.

The files you want to import usually have either a TLB or EXE extension. When working with Office 97, however, you want one with an OLB extension. The file to
use with Word is MSWORD8.OLB, and the one to use with Excel is EXCEL8.OLB. On my system, I found these entries in the …\Microsoft Office\Office
directory.

The Pascal translations of the interfaces to all the objects used in Excel or Word are found in the files created by importing EXCEL8.OLB and MSWORD8.OLB.
Throughout this part of the paper, I will ignore these interfaces, and show you how to work directly with variant objects. However, in Part II of the paper I will
return to this subject, and show you how to work with interfaces. At that time, I will present a discussion of the relative merits of working with variants and
interfaces.

Regards, Zif.

Have a nice day!

Dear tam97
Try the following component for exporting the resulys of a query to  a table.I just found it and hope you find it useful.
I am not further writing about the formula 1 since most of the functions are a little complex and frankly I never like OCXs.
If you want an example for this let me know your e mail address and I will post it to you
Venks -----------------------------------------------------------------{ An "Export Query" component for Delphi32.                                            }
{ Copyright 1996, Jean-Fabien Connault.  All Rights Reserved.                          }
{ This component can be freely used and distributed in commercial and private          }
{ environments, provided this notice is not modified in any way.                       }
{ -------------------------------------------------------------------------------------}
{ Feel free to contact me if you have any questions, comments or suggestions at        }
{   JFConnault@mail.dotcom.fr (Jean-Fabien Connault)                                   }
{ You can always find the latest version of this component at:                         }
{   http://www.worldnet.net/~cycocrew/delphi/                                          }
{ -------------------------------------------------------------------------------------}
{ Date last modified:  01/28/97                                                        }
{ -------------------------------------------------------------------------------------}

{ -------------------------------------------------------------------------------------}
{ TQExport v1.01                                                                       }
{ -------------------------------------------------------------------------------------}
{ Description:                                                                         }
{   A component that allows you to export the result of a Query to a Word              }
{   document or an Excel sheet.                                                        }
{ Properties:                                                                          }
{   property FileName: String;                                                         }
{   property Query: TQuery;                                                            }
{ Procedures and functions:                                                            }
{   function ExportWord:boolean;                                                       }
{   function ExportExcel:boolean;                                                      }
{ Needs:                                                                               }
{   Excels package version 2.0 from Tibor F. Liska (liska@sztaki.hu)                   }
{          and Stefan Hoffmeister (Stefan.Hoffmeister@Uni-Passau.de)                   }
{                                                                                      }
{ See example contained in example.zip file for more details.                          }
{ -------------------------------------------------------------------------------------}
{ Revision History:                                                                    }
{ 1.00:  + Initial release                                                             }
{ 1.01:  + Fixed to use Excels package version 2.0                                     }
{ -------------------------------------------------------------------------------------}

unit QExport;

interface

uses
  SysUtils, WinTypes, WinProcs, Messages, Classes, Graphics, Controls,
  Forms, Dialogs, DBTables, OleAuto, Excels, LibConst;

type
  TQExport = class(TComponent)
  private
    { Private-déclarations }
    {$IFNDEF INSTALLED}
    FExcel: TAdvExcel;
    {$ENDIF}
    FFileName: String;
    FQuery: TQuery;
  protected
    { Protected-déclarations }
  public
    { Public-déclarations }
    constructor Create(AOwner: TComponent); override;
    destructor Destroy; override;
    function ExportWord:boolean;
    function ExportExcel:boolean;
  published
    { Published-déclarations }
    property FileName: String read FFileName write FFileName;
    property Query: TQuery read FQuery write FQuery;
  end;

procedure Register;

implementation


procedure Register;
begin
  RegisterComponents(LoadStr(srDAccess), [TQExport]);
end;

constructor TQExport.Create(AOwner: TComponent);
begin
  FExcel := TAdvExcel.create(self);
  inherited Create(AOwner);
end;

destructor TQExport.Destroy;
begin
  FExcel.free;
  inherited Destroy;
end;

{*****************************************************************************}
{* FUNCTION ExportWord                                                       *}
{*****************************************************************************}

function TQExport.ExportWord: boolean;
var
  S, Lang: string;
  MSWord: Variant;
  L,i: Integer;
begin
 { OLE Automation }
 result := true;
 try
    MsWord := CreateOleObject('Word.Basic');
 except
    ShowMessage('Could not start Microsoft Word.');
    result := false;
    Exit;
 end;

  { Détermination du langage utilisé }
  try
    Lang := MsWord.AppInfo(16);
  except
   MessageDlg('Cette version de Microsoft Word n''est pas supportée.',mtInformation,[mbOk],0);
   result := false;
   Exit;
  end;
  //Form1.Caption := Lang;

  { Requête }
  with FQuery do
   begin
    L := 0;
    { Construction de la chaine (titre) }
    for i := 0 to FQuery.FieldCount - 1 do
    begin
     S := S + FQuery.Fields[i].Fieldname;
     if i <> FQuery.FieldCount - 1 then  S := S + ';';
    end;
    S := S + #13;
    inc(L);

    Close;
    Open;
    try
     First;
     while not EOF do
      begin
       { Construction de la chaine (valeurs) }
       for i := 0 to FQuery.FieldCount - 1 do
        begin
         S := S + (FQuery.Fields[i]).AsString;
         if i <> FQuery.FieldCount - 1 then  S := S + ';';
        end;
       S := S + #13;

       Inc(L);
       Next;
      end;

      { Langue anglaise }
      if (Lang = 'English (US)') or (Lang = 'English (UK)') then
      begin
       MsWord.AppShow;
       MSWord.FileNew;
       MSWord.Insert(S);
       MSWord.LineUp(L, 1);
       MSWord.TextToTable(ConvertFrom := 2, NumColumns := FQuery.FieldCount);
       MSWord.FileSaveAs(FFileName, 0);
      end;

      { Langue française }
      if Lang = 'Français' then
      begin
       MsWord.FenAppAfficher;
       MsWord.FichierNouveau;
       MSWord.Insertion(S);
       MSWord.LigneVersHaut(L, 1);
       MSWord.TexteEnTableau(ConvertirDe := 2, NbColonnesTableau := FQuery.FieldCount);
       MSWord.FichierEnregistrerSous(FFileName, 0);
      end;

     finally
      Close;   // Close Query
    end;
  end;

end;

{*****************************************************************************}
{* FUNCTION ExportExcel                                                      *}
{*****************************************************************************}

function TQExport.ExportExcel: boolean;
var
  MSExcel: Variant;
  i, L, Row, Column: integer;
  S: string;
begin

 result := true;

 try
    MsExcel := CreateOleObject('Excel.Application');
 except
    ShowMessage('Could not start Microsoft Excel.');
    result := false;
    Exit;
 end;


 { Tentative de connexion à Excel }
 try
  FExcel.Connect;   { Same as Excel.Connected := True; }
 except
  result := false;
  Exit;
 end;

 FExcel.StartTable;        { Create new workbook }

 with FQuery do
  begin
   L := 1;
   { Construction de la chaine (titre) }
   for i := 0 to FQuery.FieldCount - 1 do
   begin
    S:= FQuery.Fields[i].Fieldname;
    Row := L;
    Column := i + 1;
    try
     FExcel.PutStrAt(Row, Column, S);
    except
     //ShowMessage('Error with string "'+S+'"');
    end;
   end;
   inc(L);

   Close;
   Open;
   try
    while not EOF do
     begin
      { Construction de la chaine (valeurs) }
      for i := 0 to FQuery.FieldCount - 1 do
       begin
        S := (FQuery.Fields[i]).AsString;
        Row := L;
        Column := i + 1;
        try
         FExcel.PutStrAt(Row, Column, S);
        except
         //ShowMessage('Error with string "'+S+'"');
        end;
       end;
       inc(L);
       Next;
     end;
    finally
     Close;  // Close Query
    end;
  end;

 FExcel.EndTable;          { Show excel table }

 FExcel.Exec('[SAVE.AS("' + FFileName + '";1;"";FALSE;FALSE)]');
 FExcel.Exec('[CLOSE(FALSE)]');
 FExcel.Exec('[QUIT]');

end;



end.

Hi venks,

believe it's the same component as  http://sunsite.icm.edu.pl/delphi/ftp/d20free/qexport.zip

Kind Regards, Zif.

Thanks Guys for your replies.

Venks

I'm sure this is the component I downloaded which cause my system to hang when installed, I'll check it out again because at the time I was loading umpteen components at a time.

Zif

I imported the type libraries at the time I don't know if I appened the Pascal reserved word End, I'll give it another go.

By the way you guys have some amount of patience, it's much appreciated.

Rgds John

Venks & Zif

Checked out your last comments.

Venks
Loaded Excels no problem, but QExport (the one that would really help) won't load due to the absnce of LibConst.dcu, any ideas ?, did you get yours to load OK and if so where the $%&6!! is LibConst.dcu.

Thanks for your help.

Zif
I tried again with the type library, discovered I'm on version 3.00 not 3.01 as the author stated was required, maybe I could download an upgrade patch, I'll try the Borland site.

Failing that I'll become a bus driver instead !

The one drawback to the net being worldwide is that if you two guys were helping me in my home town I'd buy you a drink or three, still if you ever drop into Scotland let me know.

Rgds John

Hi tam97, mmm drinks.... and in Scotland.... mmmmm.

Ok, About 3.01, you've to buy it! Yep, strange not?
When you've got 3.01 download 3.02 (this one = free!) as well.

Zif.

Dear tam97
Same problem out here.Even you have lots of patience trynig out all our suggestions.
The file libconst.dcu is not traceable.I will let you know if something else comes up.
Venks

Hi Zif

Thanks for your reply.

I'll have to save up my pennies to buy the updated version of Delphi, I think I'll wait till Delphi 4 is established.

Cheers
John

Hi Venks

Thanks for your reply.

I'll try contact the author of the component as it seems as if this would do the job required.

If I get any info I'll let you know, by the way venks are you from Holland ? I'm sure your've helped me before.

Cheers
John