Paul,
No problem. I'm also going to do a bit of a paper over the weekend regarding
this as I think there are a lot of developers who may be interested. Since I
started looking at Excel a little closer I've discovered lots of other
"goodies".
Simply substitute your cDataPath to point to the VFP database and modify the
cSQL statement as well as the Row/Column and data items to show in the Pivot
accordingly. The other thing about using OLE-DB is that you can use inbuilt
VFP functions such as Cast(), Alltrim() as well as "+" to concatenate
strings. All things that ODBC croaks on.
Dave Crozier
************
* Description: Creates a pivot table from data stored in
* from a SQL Select from VFP Database
* Date: 30/08/2007
* Author: Dave Crozier
*
Close Tables all
cDataPath="c:\dev\Flexipol\Flexispec\Data"
oExcel = createobject("excel.application")
oExcel.Application.Visible = .T.
* Create a workbook.
oWorkbook = oExcel.Workbooks.Add()
* Define an Excel range object to dump the results.
oTargetSheet = oWorkbook.Sheets.Add()
oTargetRange = oTargetSheet.range("A2")
* Create a pivot table object without using the Wizard method.
* Notice that in this case we are using the OLE-DB provider
* rather than the ODBC driver.
oPivotCache = oWorkbook.PivotCaches.Add( 2 ) && external data
With oPivotCache
.Connection = "OLEDB;Provider=vfpoledb.1;data source=" + cDataPath
* Create your own SQL Select Here
cSQL= ;
"select all " ;
+"Machine.Id as Machine, " ;
+"Personnel.Surname as Surname, " ;
+"Timesheet_Detail.Weight as Weight, " ;
+"Timesheet_Detail.Waste as Waste " ;
+"from Timesheet_Detail " ;
+"Join Timesheet_Header on
Timesheet_Header.Primary_Key=Timesheet_Detail.Primary_Header_Key " ;
+"Join Machine on
Machine.Primary_Key=Timesheet_Detail.Primary_Machine_Key " ;
+"Join Timesheet_Personnel on
Timesheet_Personnel.Primary_Header_Key=Timesheet_Header.Primary_Key ";
+"Join Personnel on
Personnel.Primary_Key=Timesheet_Personnel.Primary_Personnel_Key " ;
+"Where Timesheet_Header.Primary_Key<100"
.Commandtext = cSQL
oPivotTable = .CreatePivotTable( oTargetRange, "PivotTable" )
*************************************
* Define how the data would initally be arranged in
* the pivot table.
*
With oPivotTable
.PivotFields("Surname").orientation = 1 && row
.PivotFields("Machine").orientation = 2 && column
.PivotFields("Waste").orientation = 4 && data
Endwith
*
Endwith
*
Return
*
* End of Code
***********
Internal Virus Database is out-of-date.
Checked by AVG Free Edition.
Version: 7.5.476 / Virus Database: 269.11.10/943 - Release Date: 08/08/2007
17:38
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/[EMAIL PROTECTED]
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
