We help IT Professionals succeed at work.

VFP Excel how do I create a pivot table for the information in another sheet?

Peter H.
Peter H. asked
on
I have created a spreadsheet with 2 sheets.  I have added a list of values on sheet 2 and want to create a pivot table for sheet one.  I can only find examples that create a pivot from an external data source.

How would I create a pivot table on sheet 1 for the data on sheet 2?
Comment
Watch Question

The code published on this page works (almost) without problems:
http://flylib.com/books/en/1.19.1.65/1/

The only necessary change was to add the Sheet No. 1, so change the code:
#DEFINE xlDatabase 1
oSourceData = oBook.Sheets[1].Range("A1:H2822")
oDestination = oBook.Sheets[1].Range("J1")
oPivotTable = oBook.Sheets[1].PivotTableWizard(xlDatabase, oSourceData, ;
oDestination, "SalesAnalysis", .T., .T.)

To:
#DEFINE xlDatabase 1
oSourceData = oBook.Sheets[1].Range("A1:H2822")
oSourceData.Sheets.Add
oDestination = oBook.Sheets[1].Range("A1")
oPivotTable = oBook.Sheets[1].PivotTableWizard(xlDatabase, oSourceData, ;
oDestination, "SalesAnalysis", .T., .T.)

And a few more changes in XLS file opening. You may test following code
* Clean out any existing references to servers.
CLOSE DATABASES ALL

* This prevents memory loss to leftover instances.
RELEASE ALL LIKE o*
* For demonstration purposes, make oExcel and oBook
* available after this program executes.
PUBLIC oExcel, oBook

OPEN DATABASE (_SAMPLES + "\TasTrade\Data\TasTrade")
SELECT Customer.Company_Name, ;
Customer.Country, ;
CMONTH(Orders.Order_Date) AS Order_Month, ;
YEAR(Orders.Order_Date) AS Order_Year, ;
Category.Category_Name,;
Products.Product_Name, ;
Order_Line_Items.Quantity, ;
Order_Line_Items.Unit_Price * Order_Line_Items.Quantity ;
AS Total_Price;
FROM Orders, Order_Line_Items, Customer, Products, Category ;
WHERE Order_Line_Items.Order_ID = Orders.Order_ID ;
AND Orders.Customer_ID = Customer.Customer_ID ;
AND Order_Line_Items.Product_ID = Products.Product_ID ;
AND Products.Category_ID = Category.Category_ID ;
INTO CURSOR Pivot
LastLine = ALLTRIM(STR(_TALLY + 1 ))
COPY TO (SYS(5) + CURDIR() + "Pivot") TYPE XL5

* Open the workbook, and best-fit all the columns.
oExcel = CREATEOBJECT('Excel.Application')
oBook = oExcel.WorkBooks.Open(SYS(5) + CURDIR() + "Pivot.XLS")

LastLine = ALLTRIM(STR(RECCOUNT('Pivot')+1))

*oBook = GETOBJECT(CURDIR() + "Pivot.XLS")
WITH oBook
*oExcel = .Application
.Application.Visible = .T.
.Windows[1].Activate()
.Sheets[1].Range("A1:H" + LastLine).Columns.AutoFit()
ENDWITH


#DEFINE xlDatabase 1
oSourceData = oBook.Sheets[1].Range("A1:H2822")
oBook.Sheets.Add
oBook.Sheets[1].Name = 'Pivot Result'
oDestination = oBook.Sheets[1].Range("A1")
oPivotTable = oBook.Sheets[1].PivotTableWizard(xlDatabase, oSourceData, ;
oDestination, "SalesAnalysis", .T., .T.)


oPivotTable.AddFields("country", "category_name")
#DEFINE xlDataField 4
oPivotTable.PivotFields["Quantity"].Orientation = xlDataField

WITH oPivotTable.PivotFields("category_name")
.PivotItems("Condiments").Visible = .F.
.PivotItems("Dairy Products").Visible = .F.
.PivotItems("Grains/Cereals").Visible = .F.
.PivotItems("Meat/Poultry").Visible = .F.
.PivotItems("Produce").Visible = .F.
.PivotItems("Seafood").Visible = .F.
ENDWITH
WITH oPivotTable.PivotFields("country")
.PivotItems("Argentina").Visible = .F.
.PivotItems("Austria").Visible = .F.
.PivotItems("Belgium").Visible = .F.
.PivotItems("Brazil").Visible = .F.
.PivotItems("Denmark").Visible = .F.
.PivotItems("Finland").Visible = .F.
.PivotItems("France").Visible = .F.
.PivotItems("Germany").Visible = .F.
.PivotItems("Ireland").Visible = .F.
.PivotItems("Italy").Visible = .F.
.PivotItems("Norway").Visible = .F.
.PivotItems("Poland").Visible = .F.
.PivotItems("Portugal").Visible = .F.
.PivotItems("Spain").Visible = .F.
.PivotItems("Sweden").Visible = .F.
.PivotItems("Switzerland").Visible = .F.
.PivotItems("UK").Visible = .F.
.PivotItems("Venezuela").Visible = .F.
ENDWITH

FOR EACH oItem IN oPivotTable.PivotFields("category_name").PivotItems
IF NOT oItem.Name $ ("Beverages Confections")
oItem.Visible = .F.
ENDIF
ENDFOR
FOR EACH oItem IN oPivotTable.PivotFields("country").PivotItems
IF NOT oItem.Name $ ("Canada Mexico USA")
oItem.Visible = .F.
ENDIF
ENDFOR

Open in new window


You may skip the pivot.xls file creation and start with the PivotWizard directly.

I am just not sure how legal is the above web link... To be sure about the copyright you should buy the original book: http://www.hentzenwerke.com/catalog/autofox.htm which is simply perfect.

Author

Commented:
It was the .orientation that I was looking for.  But you got me going in the right direction so full marks.

* 6. Define how data would initially be arranged in the pivot table.
oPivotTable.PivotFields("country").orientation = 1      && row
oPivotTable.PivotFields("yearmonth").orientation = 2      && column
oPivotTable.PivotFields("linetotal").orientation = 4      && data

Author

Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for peterhup's comment #a40645856
Assisted answer: 500 points for pcelba's comment #a40634216

for the following reason:

What I got was almost what I needed but the key missing piece was the .orientation