>Hi All,
>
>Is there a way to programmatically re-order the columns in a table? I want to export the table to an Excel sheet in a user-defined order.
>Thanks.
>
>Luke
You can use an SQL. Here is a sample using Customer sample data:
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
lcColumnHeaders = "Country, Region, City, CustomerID, Company Name, Contact Name"
loStream = GetDataAsAdoStream("Provider=VFPOLEDB;Data Source="+m.lcDataSource, m.lcSQL)
oExcel = Createobject("Excel.Application")
With oExcel
.DisplayAlerts = .F.
.Workbooks.Add
.Visible = .T.
With .ActiveWorkBook.ActiveSheet
.Name = 'Customer List'
VFP2ExcelVariation(m.loStream, .Range("A1"), m.lcColumnHeaders)
.Columns.AutoFit()
Endwith
Endwith
Function VFP2ExcelVariation(toStream, toRange, tcHeaders)
Local loRS As ADODB.Recordset,ix
loRS = Createobject('Adodb.Recordset')
m.loRS.Open( m.toStream )
Local Array aHeader[1]
m.toRange.Offset(1,0).CopyFromRecordSet( m.loRS )
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