Excel 2007 Pivot Table Conflict

navinse

Hey,

I have been trying to make a simple pivot table from Navision 4.0 SP2 to Excel 2007. I have used CU 424 as reference, other posts and created a macro in Excel. Everything seems to be working correctly until I try to call my pivot cache automation variable. It states that the variable needs to be instantiated. I read on some forums regarding owc 12 that there might be a conflict with the new owc and pivot table creation. Is this true or does my code look bad?

The variable is of type Automation and subtype of 'Microsoft Excel 12.0 Object Library'.PivotCache. Here is the code segment that it breaks on:


TempExcelBuffer.CreateBook;

TempExcelBuffer.CreateNewSheet('ILE DATA','1',CompanyInformation.Name,USERID,2);

TempExcelBuffer.CreateNewSheet('ILE PIVOT','2',CompanyInformation.Name,USERID,1);

xlPivotCache := xlApp.ActiveWorkbook.PivotCaches.Add(1,('ILE PIVOT!A1:C4'));

xlPivotCache.CreatePivotTable('','PivotTable1');

TempExcelBuffer.GiveUserControl;

I appreciate any help that you give and let me know if I am unclear.

girish.joshi

somewhere in your code you need to say

if not exists(xlPivotCache) then create(xlPivotCache) ;

You need to issue a create command on automation variables before working with them.

navinse

Thanks for the reply.

I tried the suggestion but it had an error that there cannot be a assignment of text to automation. So I just put in a CREATE(xlPivotTable) and it came back with an error of:

Could not create an instance of the OLE control Automation Server identified by GUID:(long string of characters):'Microsoft Excel 12.0 Object Library'.PivotCache.
Check that the OLE control or Automation server is correctly installed.

Any ideas? I appreciate the help. ](*,)

girish.joshi

in my post above, I wrote if not exists

it should have said:

if isclear(xlPivotCache) then create (xlPivotCache)

...
end of your code
...
Clear(xlpivotcache)

Your error could be because you are using a class that isn't defined in the automation variable.

automation is not correctly installed.

navinse

Stlll no luck. I put in your code segment and it still gave the error about not being able to create an instance of the OLE control in Automation server. Sorry, but just a novice on the automation instances. How can you tell that you have the automation server/instance installed properly? Thanks for the help in advance.