Answered by:
Export VFP dbf to Excel

Question
-
How can I export a VFP dbf into a spreadsheet in Excel? There are 2 columns that have memo data, will that open in Excel as well?
Monday, April 21, 2008 9:30 PM
Answers
-
And here is a single table with memo sample:
Code Snippet#Define xlTop -4160
Local oExcel
oExcel = Createobject("Excel.Application")
With oExcel
.WorkBooks.Add
.Visible = .T.
VFP2Excel(_samples+'data\testdata.dbc',;
'select emp_id,first_name,last_name,'+;'CAST(CHRTRAN(notes,CHR(13),"") as M) as Notes from employee',;
.ActiveWorkBook.ActiveSheet.Range('A1'))
* This part is cosmetic
With .ActiveWorkBook.ActiveSheet
.UsedRange.VerticalAlignment = xlTop && set all to top
With .Columns(5)
.ColumnWidth = 80 && 80 chars width
.WrapText = .T.
Endwith
.UsedRange.Columns.AutoFit
.UsedRange.Rows.AutoFit
Endwith
EndwithFunction VFP2Excel
Lparameters tcDataSource, tcSQL, toRange
Local loConn As AdoDB.Connection, ;
loRS As AdoDB.Recordset,;
ix
loConn = Createobject("Adodb.connection")
loConn.ConnectionString = "Provider=VFPOLEDB;Data Source="+m.tcDataSource
loConn.Open()
loRS = loConn.Execute(m.tcSQL)For ix=1 To loRS.Fields.Count
toRange.Offset(0,m.ix-1).Value = Proper(loRS.Fields(m.ix-1).Name)
toRange.Offset(0,m.ix-1).Font.Bold = .T.
Endfor
toRange.Offset(1,0).CopyFromRecordSet( loRS )
loRS.Close
loConn.Close
EndfuncTuesday, April 22, 2008 8:50 AM
All replies
-
There are many samples here. ie:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2185983&SiteID=1
Tuesday, April 22, 2008 12:01 AM -
And here is a single table with memo sample:
Code Snippet#Define xlTop -4160
Local oExcel
oExcel = Createobject("Excel.Application")
With oExcel
.WorkBooks.Add
.Visible = .T.
VFP2Excel(_samples+'data\testdata.dbc',;
'select emp_id,first_name,last_name,'+;'CAST(CHRTRAN(notes,CHR(13),"") as M) as Notes from employee',;
.ActiveWorkBook.ActiveSheet.Range('A1'))
* This part is cosmetic
With .ActiveWorkBook.ActiveSheet
.UsedRange.VerticalAlignment = xlTop && set all to top
With .Columns(5)
.ColumnWidth = 80 && 80 chars width
.WrapText = .T.
Endwith
.UsedRange.Columns.AutoFit
.UsedRange.Rows.AutoFit
Endwith
EndwithFunction VFP2Excel
Lparameters tcDataSource, tcSQL, toRange
Local loConn As AdoDB.Connection, ;
loRS As AdoDB.Recordset,;
ix
loConn = Createobject("Adodb.connection")
loConn.ConnectionString = "Provider=VFPOLEDB;Data Source="+m.tcDataSource
loConn.Open()
loRS = loConn.Execute(m.tcSQL)For ix=1 To loRS.Fields.Count
toRange.Offset(0,m.ix-1).Value = Proper(loRS.Fields(m.ix-1).Name)
toRange.Offset(0,m.ix-1).Font.Bold = .T.
Endfor
toRange.Offset(1,0).CopyFromRecordSet( loRS )
loRS.Close
loConn.Close
EndfuncTuesday, April 22, 2008 8:50 AM -
This is excellent, Cetin!Tuesday, April 22, 2008 6:40 PM
-
Thanks Dan
Wednesday, April 23, 2008 9:38 PM