Excel Automation Error - Can not create instance... (NAV 4)

dgaber

Hi guys,

I'm trying to send rules for conditional formatting from Nav to Excel, trying to replicate the VBA code below:
With Range("A1")
.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
Formula1:="2", Formula2:="3"
.FormatConditions(1).Interior.ColorIndex = 33
End With

This is the code in NAV:
Range := Sheet.Range('A1');
Range.FormatConditions.Add(1,1,2,3)

IF ISCLEAR(test) THEN
CREATE(test);

test.ColorIndex := 33;

;

I can not translate the last line of code in NAV (.FormatConditions(1).Interior.ColorIndex = 33). When I try to create Excel automation type "Interior" (variable "test"). I get the message ("Could not create an instance of the OLE control or Automation server identified by...").

Do any of you have any ideas how to get around this problem?

I'm working on NAV 4, technically upgraded to 5, using Office 2013.

mohana_cse06

Are the other export to excel processes working fine?

If I remember correctly Office 2013 is officially compatible with NAV2009 and NAV2013..

dgaber

Other Excel processes are working OK. No problems with them.

After posting, I did some research. I tried to create instances of other Excel automations, but none of them worked. Except of those which were already included in codeunit (I've put them there months ago, but using office 2013 also).

I've tried to code same thing using office 2010 automation on another PC, but to no success.

EDIT: The code stops at "CREATE" command.

dgaber

Hey guys,

still trying to figure this one out.

I hope anyone of you can help me.

To tell you a bit more:
I'm using a codeunit which has encoded a lot of Excel automation functions, which I then call from reports to create and format excel files.

At this moment I can use all of the Excel automations which are already defined in codeunit. But I can not add any other without getting the above mentioned error.

I've tried to work on another PC with office 2010 installed but to no effect.

I would really appreciate any help or suggestions.

Thank you all!

geordie

Hi, seems Office isn't "seen" from NAV: first, are you able to compile Excel Buffer table?
Perhaps some type library registry entries for Office gone lost (in that case you can install them again using this package: http://www.microsoft.com/en-us/download/details.aspx?id=3508)?

dgaber

Hi there.

After hours of headbanging ](*,) I managed to solve the problem \:D/ . So if anyone wants to do conditional formatting from NAV to Excel, firstly here's the original VBA code:
With Range("A1")
.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _Formula1:="2", Formula2:="3"
.FormatConditions(1).Interior.ColorIndex = 33
End With

Secondly here's the proper NAV code, which is working without a problem:

Range := Sheet.Range('A1');
Range.FormatConditions.Add(1,1,2,3);
FormatCondition := Range.FormatConditions.Item(1);
FormatCondition.Interior.ColorIndex := 33;

Variables used in upper NAV code are:
Sheet (Excel Automation - Worksheet)
Range (Excel Automation - Range)
FormatCondition (Excel Automation - FormatCondition)

As mentioned in the question at the start of the thread I tried to initialize the "FormatCondition" as an automation server (with ISCLEAR and CREATE) and got the automation error.

Here's the trick I figured out:

In this case FormatCondition is not acting as automation, but more like a variable. So to "initialize" it, it has to get the number conditional format row (in this case it's 1). And this is all the "initialization" this automation needs. After this step you can play with design and colors of the cell without any problem.

If you'd like to add more conditional formats to one cell, just repeat the code above, but don't forget to change the number of row, when "initializing" FormatCondition automation.

Feeling happy, gonna grab a beer =D>