CODEWITHSUNDEEP
delphiexcel-2007delphi-xe2
Bossa
Bossa

Reputation: 11

How to export an Access database to Excel, using Delphi

Im writing a database application, using Delphi and need to export data from an access database to an Excel spreadsheet. I Could manage the reverse sequence (import excel to access) using a docmd.spreadsheet. Works 100%. But I do not know how to set the parameters for the export. I need help please.

Upvotes: 1

Views: 2555

Answers (1)

RRUZ
RRUZ

Reputation: 136411

Check this sample code, Also I recommend you which you read the DoCmd.TransferSpreadsheet Method documentation too.

{$APPTYPE CONSOLE}

uses
  SysUtils,
  ActiveX,
  ComObj;

procedure ExportDataAccess(const AccessDb, TableName, ExcelFileName:String);
Const
  acQuitSaveAll             = $00000001;
  acExport                  = $00000001;
  acSpreadsheetTypeExcel9   = $00000008;
  acSpreadsheetTypeExcel12  = $00000009;
var
 LAccess : OleVariant;
begin
 //create the COM Object
 LAccess := CreateOleObject('Access.Application');
 //open the access database
 LAccess.OpenCurrentDatabase(AccessDb);
 //export the data
 LAccess.DoCmd.TransferSpreadsheet( acExport, acSpreadsheetTypeExcel9, TableName, ExcelFileName, True);
 LAccess.CloseCurrentDatabase;
 LAccess.Quit(1);
end;

begin
 try
    CoInitialize(nil);
    try
      ExportDataAccess('C:\Datos\Database1.accdb','Sales','C:\Datos\MyExcelFile.xls');
      Writeln('Done');
    finally
      CoUninitialize;
    end;
 except
    on E:EOleException do
        Writeln(Format('EOleException %s %x', [E.Message,E.ErrorCode]));
    on E:Exception do
        Writeln(E.Classname, ':', E.Message);
 end;
 Writeln('Press Enter to exit');
 Readln;
end.

Upvotes: 4

Related Questions