OLE Automation - Creating a Pivot Table in Excel w/ Lotusscript

HCL Domino Domino Forum
1

Hi,

I am using OLE automation to create an Excel file when the user clicks a button in a DB I am designing.

I have never done this before, but so far with straightforward worksheets, it has worked beautifully, and everything that I expect to go into each cell gets in there.

Now I am trying to have it create a pivot table. Here is the first line of the VB Code that is generated when I create an Excel macro:

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _

    "'ALL ACCRUED including buydesk'!C1:C30").CreatePivotTable TableDestination:= _

    "'[US ACCRUALS_FINAL TEMPLATE V3.xls]Summary'!R4C11", TableName:= _

    "PivotTable1", DefaultVersion:=xlPivotTableVersion10

When I put that in to my Lotusscript code, however (and I begin it with msExcel.ActiveWorkbook. … since msExcel is the name of my Excel object) I get the error in the status bar “Unexpected: :; Expected: Operator; )”

Any clue as to what I am doing wrong with that line?

Thanks

Ben

2

Subject: OLE Automation - Creating a Pivot Table in Excel w/ Lotusscript

Ben,

You need to translate the VBA argument list to LotusScript – there are a few GOOD posts on this forum on how to do that, but in a nutshell, you need to look at the function in EXCEL’s VBA Editor Help – see which arguments go in which position in the call, and then get the constants, like “xlPivotTableVersion10” and create constants within LotusScript to match them.

LotusScript doesn’t recognize the VBA calls – you need to get rid of the whatever := whatever and simply use positional arguments with the values. Confusing? Like I said, there are a few GOOD posts on here that explain it in detail.

Gary

3

Subject: RE: OLE Automation - Creating a Pivot Table in Excel w/ Lotusscript

Sorry, I forgot to mention that I have already coded the Const pieces of it, so that was not causing the error.

4

Subject: RE: OLE Automation - Creating a Pivot Table in Excel w/ Lotusscript

At the risk of begin redundant, did you read the FAQ of FAQs? It contains a specific link to a thread related to pivot tables that includes examples.

Give it a look and see if it helps.

Doug

(you get to the faqs when you post a new entry, it’s a link at the top of the initial page).

5

Subject: RE: OLE Automation - Creating a Pivot Table in Excel w/ Lotusscript

Also, there’s a general IBM technote with this title

Setting Third-Party Object Property to an Object Causes Error: “Automation Object Member Not Found”

under Ref # 1084270

might shed light.

6

Subject: OLE Automation - Creating a Pivot Table in Excel w/ Lotusscript

This has been discussed in the old 4.6 forum:

http://www-10.lotus.com/ldd/46dom.nsf/DateAllFlatweb/dc09168723293c9885256b9e004dd58b?OpenDocument

(this link is available via the FAQ of FAQs)

Long story short, the := construct does not work in LotusScript. Instead you have to pass these variables as parameters to the Add method like so:

Dim pivotCache as Variant

set pivotCache =ActiveWorkbook.PivotCaches.Add(sourcetype,sourcedata)

Call pivotCache.CreatePivotTable(tableDestination,tableName, defaultVersion)

oh and as an added bonus, you can’t use the xlDatabase or xlPivotTableVersion10 constants because they only exist in Excel and LotusScript doesn’t know what they are. So you either need to pass the constant value (1 for xlDatabase and the same for xlPivotTableVersion10) or declare constant variables like so:

Const xlDatabase% = 1

Const xlPivotTableVersion10% = 1

I prefer creating the constants so I know why I am passing that value if I have to come back and work with the code later.

hth.

brandt

7

Subject: RE: OLE Automation - Creating a Pivot Table in Excel w/ Lotusscript

Hi Brandt, thanks for the info. Here is the code I used:

Dim pivotCache As Variant

Set pivotCache = MsExcel.ActiveWorkbook.PivotCaches.Add(xlDatabase, "'ALL ACCRUED including buydesk'!C1:C30")

But it errors out with “Automation object member not found” as soon as it runs the Set line. Any idea what the problem is?

Thanks again!

Ben

8

Subject: RE: OLE Automation - Creating a Pivot Table in Excel w/ Lotusscript

Have you tried only passing the cell range? According to the Excel help, you only need to add the string before the range if you are passing a connection string from an external data source. “All ACCRUED…” should not be necessary and may be causing your issue.

9

Subject: RE: OLE Automation - Creating a Pivot Table in Excel w/ Lotusscript

“ALL ACCRUED including buydesk” is the name of the worksheet that contains the cells that I am using as the source data… so I think I need to keep that in there

10

Subject: RE: OLE Automation - Creating a Pivot Table in Excel w/ Lotusscript

I see the problem. According to the Excel help:

SourceData Optional Variant. The data for the new PivotTable cache. This argument is required if SourceType isn’t xlExternal. Can be a Range object, an array of ranges, or a text constant that represents the name of an existing PivotTable report. For an external database, this is a two-element array. The first element is the connection string specifying the provider of the data. The second element is the SQL query string used to get the data. If you specify this argument, you must also specify SourceType.

so basically you are passing a string as the datasource where you would need to pass a range. So you will need to create a worksheet object that corresponds to your data source and then pass the range object to the method. Right now, you are passing a string, and the method doesn’t like it.

brandt