DBF / RecSet to Excel - Some simpler and faster Alternatives
Posted: Fri Feb 15, 2008 5:44 pm
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
ADO: RecordSet
To convert full recordset to excel
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.
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> )
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> )
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.