I was able to show my client how to easily create charts from the same pivot table in a variety of ways.
A great resource for getting started in doing this is the book Microsoft Automation with Visual Foxpro. I used a combination of the examples in the book, recording Excel macros, etc.
The reason I needed a good cross tab solution was that a long time client of mine had used Foxfire for many years. However, I just recently upgraded them to VFP9 and SQL Server and was evaluating the effort required to move to the next version of Foxfire which is still in beta. Needless to say, I was not having much luck. So, long story short, they needed the ability to run several reports from their new app ASAP. Many of these were cross-tabs which Foxfire did a pretty good job of making easy (but not an option for now). I wanted to keep it just as simple and was looking for a more dynamic alternative to Foxpro reports (FRX) to handle the output.
My co-worker Andy Needham suggested I take a look automating Excel. Great idea! In a single day I had 5 cross-tabs created in Excel and the client loves it!
Here is some sample code from the class I created: (Note: VFP cursor already loaded in Excel using COPY TO
LOCAL llReturn, ;
llReturn = .T.
lobook = GETOBJECT(This.cExcelfile)
loExcel = lobook.Application
loexcel.Visible = .T.
loExcel.ActiveCell.FormulaR1C1 = "Customer Name"
loSourceData = lobook.Sheets.Range("A1:C"+TRANSFORM(this.nlastline))
loDestination = loBook.Sheets.Range("A1")
loPivotTable = lobook.Sheets.PivotTableWizard(1,loSourceData,loDestination,This.cReporttitle,.T.,.T.)
.PivotFields(this.ccountcolumn).Orientation = 4
.PivotFields("Order_Month").NumberFormat = "mmmm-yyyy"
.NullString = ""
.PageFieldOrder = 2
.PrintTitles = .F.
.RepeatItemsOnEachPrintedPage = .F.
.PrintTitles = .T.
.RepeatItemsOnEachPrintedPage = .T.
loExcel.Selection.RowHeight = 16.5
.PrintTitleRows = "$1:$2"
.PrintTitleColumns = "$A:$A"
.LeftHeader = "&D&T"
.CenterHeader = This.cReporttitle
.RightHeader = "Page &P"
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = loExcel.InchesToPoints(0.25)
.RightMargin = loExcel.InchesToPoints(0.25)
.TopMargin = loExcel.InchesToPoints(.25)
.BottomMargin = loExcel.InchesToPoints(.25)
.HeaderMargin = loExcel.InchesToPoints(0.25)
.FooterMargin = loExcel.InchesToPoints(0.25)
.PrintHeadings = .F.
.PrintGridlines = .F.
.PrintComments = -4142
.PrintQuality = 600
.CenterHorizontally = .F.
.CenterVertically = .F.
.Orientation = 2
.Draft = .F.
.PaperSize = 1
.FirstPageNumber = -4105
.Order = 2
.BlackAndWhite = .F.
.Zoom = .F.
.FitToPagesWide = 1
.FitToPagesTall = .F.
loPivotTable = null
loSourceData = null
loDestination = null
loExcel = null
loBook = null
A couple things to note: the structure of all these particular cross tabs is the same except for the count column (cu_name, order_month, my_count), so I set that to the cCountColumn property before calling this execute method. nLastLine is set from the RECCOUNT() of my result set. I change the header column of cu_name to Customer Name to make it look nicer.