DBF:
The following method works if we want the entire dbf to excel
- Code: Select all Expand view
oBook := oExcel:Open( <fullpath\source.dbf> )
oBook:SaveAs( <fullpath\dest.xls> )
ADO: RecordSet
To convert full recordset to excel
- Code: Select all Expand view
oBook := oExcel:WorkBooks:Add()
oSheet := oExcel:Get( 'ActiveSheet' )
oSheet:Cells( DestRow, DestCol ):CopyFromRecordSet( oRecSet, [nMaxRows], [nMaxCols] )
oBook:SaveAs( <fullpath\dest.xls> )
Selected Rows and Columns of RecordSet:
Set Filter to required columns with oRs:Filter method. After copying into Excel, delete unwanted columns with oSheet:Columns(n):Delete() method.
DBF
Selected Rows and Columns / Calc Columns:
DBF -> RecSet -> Excel
We can use the above simpler and faster alternative copy method of recordset to copy selected rows, selected columns and even calculated columns of DBF.
We can read from DBF also into a RecordSet. The connections strings that can be used to open dbf files in ADO are
(1) Driver={Microsoft dBASE Driver (*.dbf)};DriverID=277;Dbq=c:\mydbpath;
(2) Driver={Microsoft dBase Driver (*.dbf)};datasource=dBase Files;
(3) Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\folder;Extended Properties=dBASE IV( or III) ;User ID=Admin;Password=;
Open a record set with the help of any of the above connetion strings from the DBF.
Select col1, col2, col3 + col5 as somename, ....etc from dbftable where <ourfiltercondition>
then convert this entire recordset into excel with the above method.
The above methods help faster conversion into excel under some circumstances.
