Unable to get the PivotTableWizard property of the Worksheet class

HCL Domino Domino Forum
1

I have been trying to get PivotTables in Excel to work from Lotusscipt.

I followed serveral examples in the FAQ, incuding this one that is supposed to be a “working example”.

http://www-10.lotus.com/ldd/nd6forum.nsf/55c38d716d632d9b8525689b005ba1c0/27b1b8a23c6721968525752f004150dc?OpenDocument&Highlight=0,PivotTableWizard

I cannot see how it is a working example when xlSheet is never set.

I would assume that xlSheet is supposed to be set to the object of the sheet where you want to put your pivot table.

When I do this, I get

Unable to get the PivotTableWizard property of the Worksheet class

error.

PivotTableSheetName = “Pivot Table”

With excelWkb.Sheets(PivotTableSheetName).PivotTableWizard

		.SourceData = ("Data!R1C1:R3C3")

		.Name = "AvgCycleTimeByArea"

		.RowGrand = False

		.ColumnGrand = False

	End With

AM I missing something here?

I am using Excel 2003.

Notes 6.5.2

2

Subject: Unable to get the PivotTableWizard property of the Worksheet class

Bruce,

Can you show us the whole code? Because I am not certain how the “working example” ever works since xlApp is never defined and xlSheet is never set in the “working example.”

If I could see the whole code, I may be able to make some suggestions. Thanks.

Brandt

3

Subject: RE: Unable to get the PivotTableWizard property of the Worksheet class

Thanks Brandt

Got it working my own way. Turns out my problem was a really dumb one of not setting a string varible. All along I assumed it was a problem with the Excel VB code but I still don’t see how the sample code should work.

4

Subject: RE: Unable to get the PivotTableWizard property of the Worksheet class

The worksheet variable seems to be a wasted declaration – getting the sheet through the Workbook.Sheets collection accomplishes the same thing (in the With block).