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.
Add a comment
|
1 Answer
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.
2 Comments
Bossa
I used your previous answer on importing an excel an it worked 100%. I anticipated from that how an export should work. But it did not function properly. Your answer to the problem is the same as what I originally coded. The problem is 1): If the program export to a file that do not exist already, it creates a file. But when I use Excel to open the file, it gives an error: Excel cannot open the file 'Çodes.xlsx'because the file format or the file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file. Can you help?
RRUZ
if you want to use the 2007 excel format you must pass the
acSpreadsheetTypeExcel12Xml value which is defined as acSpreadsheetTypeExcel12Xml= $0000000A; else just rename the file extension to xls.