Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
Converting to xls
Message
From
11/05/2018 06:58:08
Cetin Basoz
Engineerica Inc.
Izmir, Turkey
 
General information
Forum:
Visual FoxPro
Category:
Visual FoxPro and .NET
Miscellaneous
Thread ID:
01659920
Message ID:
01659929
Views:
43
>Convert a dbf to xls - I have forgotten how to do it

IF what you are converting is not a simple and small file (less than 16K rows if I remember right) you can use "Copy To". It sort of works and creates old for excel files. For anything robust you could use something like "Vfp2Excel". There are many samples on the internet using Vfp2Excel. Here is a link for a broader discussion and code with Vfp2Excel:

A thread about Excel and compatibility

And here is one of the sample codes:
Local loStream As ADODB.Stream, lcDataSource, lcSQL, lcColumnHeaders
lcDataSource = _samples+'data'
TEXT to m.lcSQL textmerge noshow
Select Country, Region, City, Cust_Id, Company, Contact
From Customer
ENDTEXT
*** We want to use custom headers on columns, rather than fieldnames
*** Our list should match the order of columns in our select
*** We could simply omit passing headers, then field names would be used as column names
lcColumnHeaders = "Country, Region, City, CustomerID, Company Name, Contact Name"
loStream = GetDataAsAdoStream("Provider=VFPOLEDB;Data Source="+m.lcDataSource, m.lcSQL)

*** Main Excel automation part now
oExcel = Createobject("Excel.Application")
With oExcel
	.DisplayAlerts = .F.
	.Workbooks.Add
	.Visible = .T.
	With .ActiveWorkBook.ActiveSheet
		.Name = 'Customer List'
		* Send the data - safe copy to replacement
		VFP2ExcelVariation(m.loStream, .Range("A1"), m.lcColumnHeaders)
		.Columns.AutoFit()
	Endwith
Endwith

* Author: Cetin Basoz
* This is based on earlier VFP2Excel function codes
* that has been published on the internet, at various sites
* since 2001. Not to be messed with others' code who named the same but has
* nothing to do with the approaches taken here (unless copy & pasted and claimed
* to be their own work, < s > that happens).
* Note that this works with 64 bits excel too.

Function VFP2ExcelVariation(toStream, toRange, tcHeaders)
	Local loRS As ADODB.Recordset,ix
	loRS = Createobject('Adodb.Recordset')
	m.loRS.Open( m.toStream )
	* Use first row for headers
	Local Array aHeader[1]
	m.toRange.Offset(1,0).CopyFromRecordSet( m.loRS )  && Copy data starting from headerrow + 1
	For ix=1 To Iif( !Empty(m.tcHeaders), ;
			ALINES(aHeader, m.tcHeaders,1,','), ;
			m.loRS.Fields.Count )
		m.toRange.Offset(0,m.ix-1).Value = ;
			Iif( !Empty(m.tcHeaders), ;
			Alltrim(aHeader[m.ix]), ;
			Proper(m.loRS.Fields(m.ix-1).Name) )
		m.toRange.Offset(0,m.ix-1).Font.Bold = .T.
	Endfor
	m.loRS.Close()
Endfunc

Procedure GetDataAsAdoStream(tcConnection, tcSQL)
	Local loStream As 'AdoDb.Stream', ;
		loConn As 'AdoDb.Connection', ;
		loRS As 'AdoDb.Recordset'
	loStream = Createobject('AdoDb.Stream')
	loConn = Createobject("Adodb.connection")
	loConn.ConnectionString = m.tcConnection
	m.loConn.Open()
	loRS = loConn.Execute(m.tcSQL)
	m.loRS.Save( loStream )
	m.loRS.Close
	m.loConn.Close
	Return m.loStream
Endproc
Çetin Basöz

The way to Go
Flutter - For mobile, web and desktop.
World's most advanced open source relational database.
.Net for foxheads - Blog (main)
FoxSharp - Blog (mirror)
Welcome to FoxyClasses

LinqPad - C#,VB,F#,SQL,eSQL ... scratchpad
Previous
Next
Reply
Map
View

Click here to load this message in the networking platform