Excel OLE Pivottable CalculatedFields automation object not found

HCL Domino Domino Forum
1

I’m running Excel 2003 with SP3 and I am trying to use the OLE inteface from Lotus Notes to create a Calculatedfield on my Pivottable.

I can add a calculated field manually to my sheet using the Formula…Calculatedfield dialog. I recorded my actions as a macro it works. I’ve used the simplest defaults here for brevity but when I run it from Lotuscript I get a popup that says…

Automation Object Member not found

Here is the offending line:

xl_Object.ActiveSheet.PivotTables(“Table5”).CalculatedFields.Add “Field1”, “= 0”, True

I have scoured the MSDN site and googled everywhere and cannot find any more details about this problem.

Even this simple code fails the same way…

Messagebox "Calculated fields = " + Cstr(xl_Object.ActiveSheet.PivotTables(“Table5”).CalculatedFields.Count)

Does anyone have any ideas or pointers?

Thanks

Jim

2

Subject: Excel OLE Pivottable CalculatedFields automation object not found

Simply add () after CalculatedFilelds. Your code should look’s like this xl_Object.ActiveSheet.PivotTables(“Table5”).CalculatedFields().Add “Field1”, “= 0”, True

And everything works now! Enjoy!

3

Subject: Excel OLE Pivottable CalculatedFields automation object not found

The Excel 2003 VisualBasic reference for the CalculatedFields collections says:

For OLAP data sources, you cannot set this collection, and it always returns Nothing.

Is Notes considered an OLAP data source via the OLE32 interface? All my pivotfield data is coming from a worksheet and I just need the calculatedfield to pivot percentages.

It would seem I would get a different error if this is my problem.

I guess no one else has tried to use the calculatedfields object or has any advice…

JIm