Error while opening an excel through VBA(XIR2-Deski)

sdk development
1

Hi,

I have written a Vb Macro(For XIR2-Deski report) to save a report as .xls file. Now i want to modify its formatting(in .xls file) using Macro itself.

While searching about it i got this code to get started with it.

Dim ExcelApp As Object
Set ExcelApp = CreateObject(“Excel.Application”)

But this is displaying following error:
“Error Number: 429 Error Description: ActiveX component can’t create object” :roll_eyes:

Any suggestion on how to go about it.

Thanks in advance

Sabu

sabu (BOB member since 2006-10-09)

2

Did you set a reference to the Excel library (from the VB Editor, Tools, References…)?

Dwayne Hoffpauir :us: (BOB member since 2002-09-19)

3

I could not find “Microsoft Excel Library” in Toos->References.
Also checked by adding “Microsoft Office 9.0 Object Library” but still giving same error. :nonod:

Please suggest if any other library needs to be added.

Varun

sabu (BOB member since 2006-10-09)

4

There should be one labeled Microsoft Excel 9.0 Object Library. If you don’t see it, I’d suspect a corrupted / missing Office installation.

Dwayne Hoffpauir :us: (BOB member since 2002-09-19)

5

Hi,
Could not find “Microsoft Excel 9.0 Object Library” in Tools->References.

But when i opened VB editor in Microsoft Excel 2003(Excel File), i could find “Microsoft Excel 11.0 Object Library” there in Tools->References.

Any idea on how to add this library in BO(XIR2) VBA.

Thanks,
Sabu

sabu (BOB member since 2006-10-09)

6

I would suspect that Excel/Office has not been installed.

The MS product must be installed on the client running the macro for you to get the libraries.

dirmiger :us: (BOB member since 2002-08-28)

7

Im having the similar problem when trting to run a scheduled vbs script. The script has to create a busobj process. Only thing is, when there is already a busobj process present on the server it gives me the following error:

ActiveX cannot create object. :hb:

Have you checked if there are any Excel processes running while you are executing your code? If yes, then terminiate these processes and execute the code again.

Judging the error you got, the code you are using can compile, so it isn’t a problem with the references you have.

HPE (BOB member since 2006-10-03)

8

I’ve not tried this with VB scripts, but I use the following with VBA:

Dim BOApp as busobj.Application
'start BusObj, careful not to grab an existing session
Set BOApp = New busobj.Application
Do Until BOApp.Variables("BOUSER").Value = ""
    Set BOApp = New busobj.Application
Loop

Maybe that will help in your case as well.

Dwayne Hoffpauir :us: (BOB member since 2002-09-19)

9

Thnx Dwayne, tried your solution, but to no avail. It works well with VBA but not with .vbs script. I keep getting a COM error. This was explainend in another trhread i thougth.

Assessing the problem further I have discovered that my .vbs script will not execute because one of the libraries i need is beying accessed (don’t whether this is good English) by another sceduled report. That is wat gave me the ActiveX error

Now trying to call the macro in the after refresh event.

A post on the forum scared me though. I’m working with a client where we have installed BOXI r2 SP2. According to the post i’ve read VB code won’t execute in the after refresh event. I won’t execute at all. I’ve tested this with a simle report and the follwong vb-code:

 Private Sub AfterRefresh()
ThisDocument.ExportasPDF("path")
End sub

This works when i refresh the report in desktop intelligence but doesn’t work when i schedule the same report.

That brings me to my question:

Is it really true that VB will not execute once you have installed SP2?

Thnx in advance

Harmen

HPE (BOB member since 2006-10-03)

10

Apparently yes. It will take a patch to fix it.

Dwayne Hoffpauir :us: (BOB member since 2002-09-19)

11

Back to SP1 it is then… Thnx Dwayne!

HPE (BOB member since 2006-10-03)

12

Dwayne: have we confirmed that BO considers the failure to execute document event procedures using VBA with scheduled DeskI documents a failure they are going to correct?

dirmiger :us: (BOB member since 2002-08-28)

13

I have not, but I seem to remember another BOB member making that statement.

Dwayne Hoffpauir :us: (BOB member since 2002-09-19)

14

I found the post where another member stated that SP2 didn’t support the use of VBA in scheduled reports.

Here’s the post. https://bobj-board.org/t/53683

I’ve searched the knowledge base but couldn’t find anything. I logged a case with BO also, i’ll wait and see what i get back.

HPE (BOB member since 2006-10-03)

15

BO has confirmed that macro’s will not execute in scheduld documents after installing SP2. They are looking into the problem.

HPE (BOB member since 2006-10-03)