Cognos Script Editor error 2

[KellyK]

Forgive me for the novice question but this is my first time trying to create a hotfile from an Impromptu report using a macro. I am getting an error "Unknown function: ErrHandler" and then another one "Label 'ErrHandler' is missing" I'm assuming this is an obvious fix so could anyone offer a hand? Here is my code:

Sub Main()

on error goto ErrHandler
Dim objImpApp As Object
Dim objImpRep As Object
Dim strHotFileName As String
Set objImpApp = CreateObject("CognosImpromptu.Application.cer3")
Set objImpRep = objImpApp.ActiveDocument
strHotFileName = "\\Dom1\Shared\Intermodal\Team Docs\Reporting\Kelly\G3\G3.ims"
objImpRep.ExportHotFile strHotFileName

objImpRep.CloseReport
objImpApp.Quit

Set objImpRep = Nothing
Set objImpApp = Nothing

ErrHandler:
msgbox "ERROR: " & Err & ": " & Error$

End Sub

Kelly

 

[griffindm]

Kelly,

It's a quirk of the product. You have to have named label at the left edge of the code in order to be recognized.

Move the ErrHandler from:

ErrHandler:

to

ErrHandler:


And it should work fine.

Dave G.


The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
Want good answers? Read FAQ401-2487 first!

http://www.decisionsupportgroup.com

 

[KellyK]

Thanks Dave!

Apparently that wasn't my only problem. Now I'm getting ERROR 91: Object Value is set to Nothing.



Kelly

 

[griffindm]

Kelly,

Try remming out your objImpApp.Quit line. I've seen this error before. The .Quit destroys the object. Then when the Set = nothing happens it throws the error.

Dave G.



The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
Want good answers? Read FAQ401-2487 first!

http://www.decisionsupportgroup.com

 

[drlex]

Dave, Kelly
Are you sure it's not due to the use of CreateObject and ActiveDocument - by opening Impromptu, there is no active report.
Either use GetObject in place of CreateObject to get an open report, or set a line after CreateObject where you open the required report (with prompts if required), like:
SetObjImpRep = ObjImpApp.Openreport("\\Dom1\report.imr")


soi la, soi carré

 

[griffindm]

Kelly,,

Lex has a point. Your script is not opening a report. Is one already open? If so, you will need the GetObject call rather than the CreateObject call.

Dave G.


The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
Want good answers? Read FAQ401-2487 first!

http://www.decisionsupportgroup.com

 

[KellyK]

I have my Impromptu report set up so that it retrieves data, then kicks off the macro in question, so yes, the report is already open. I will try the GetObject call and report back (no pun intended) my results. Thanks.

Kelly

 

[KellyK]

Here is my revised code:

Sub Main()

on error goto ErrHandler
Dim objImpApp As Object
Dim objImpRep As Object
Dim strHotFileName As String
Set objImpApp = GetObject("CognosImpromptu.Application")
Set objImpRep = objImpApp.ActiveDocument
strHotFileName = "\\Dom1\Shared\Intermodal\Team Docs\Reporting\Kelly\G3\G3.ims"
objImpRep.ExportHotFile strHotFileName

objImpRep.CloseReport

Set objImpRep = Nothing
Set objImpApp = Nothing

ErrHandler:
msgbox "ERROR: " & Err & ": " & Error$

End Sub


I get ERROR: 0 but it is creating the hotfile like it is supposed to, so I'm happy. Thanks for the help guys.

Kelly

 

[drlex]

Kelly,
Glad it's sorted and thanks for the star.
BTW, if you put the line

Exit sub

before

Errhandler:

then the routine will finish there and not display the "ERROR:0" message.

soi la, soi carré