DBF / RecSet to Excel - Some simpler and faster Alternatives

[nageswaragunupudi]

Here are some simpler and faster alternative methods to convert small to medium sized dbf / Ado RecSets to excel.

DBF:

The following method works if we want the entire dbf to excel

Code: Select all

oBook := oExcel:Open( <fullpath\source.dbf> )
oBook:SaveAs( <fullpath\dest.xls> )

ADO: RecordSet

To convert full recordset to excel

Code: Select all

   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.