Macro in Excel and Cognos

[zimo975]

Hello,
I have a macro in Excel that opens an Impromptu Report.
If the report is simple it functions.
Now I must open a report that contains a Cognos Macro that at the moment is activated at the opening of the Report.

If I execute the Macro Excel it is not activated. If I open it manually it functions.

The VBA Code I use is the following

Set Imp_report = Imp_Application.OpenReport(ReportCoda, StringaParametri)

Imp_report.retrieveall

Can you please help me?
Thanks,
Zimo

 

[YamaCog56]

Rather than run a VB macro to open the Impromptu report from MS Excel, perhaps you could use a CognosScript macro to open the Impromptu report, save the report and then open MS Excel with the report output?

What is the macro doing when the report opens? Perhaps this logic could be done elsewhere rather than when the Impromptu report is open?

 

[zimo975]

The fact is that I have to run the same report 27 times because every time I have to run it with a different string of parameters.
Don't you know how to initialize the Impromptu Macro?
Thanks,
Zimo

 

[tommyfoggy]

zimo975

I am in need of an Excel macro that opens an Impromptu report. How is this done?
Thanks,
Tom

 

[jamguy]

tommyfoggy,

I have an example of a macro that open a PowerPlay report, it does some extra stuff like copying the results to a csv file, etc.....i think you can use this for Impromptu as well:

Option Explicit
Dim CognosID, ddeChannel As Long
Dim PowerplayReport, PowerPlay, objrep As Object
Dim ReportName, WorkBookName
Dim ReportPath, Workbookpath, Filename, MainTab, SheetName, CognosDirectory, CognosFile As String

Sub StartCognosUpdate(Filename, MainTab, SheetName, ReportName, CognosDirectory)
'This sub opens a Cognos .ppr file and saves it as an Excel file, copying all data
'and pasting it the Cognos Extract sheet as indicated by the calling sub.

'This sub checks to make sure that the directory and file are correct
'If an error occurs the subroutine will end without doing anything.

'Assign default values
CognosFile = CognosDirectory + ReportName

'Verifies existence of the file
Const lTitle = "Locating Cognos File"
If Dir(CognosFile) = "" Then
MsgBox prompt:="Can't Find " & CognosFile, Buttons:=vbExclamation, Title:=lTitle
Exit Sub
End If

Application.StatusBar = "Opening Cognos and Retrieving Lastest Cube Update"
WorkBookName = SheetName & ".xls"
ReportPath = CognosDirectory & ReportName
Workbookpath = CognosDirectory & WorkBookName

UnLockSheet (SheetName)

[b]
'Opens Cognos and saves the .ppr file as Excel
Set PowerplayReport = GetObject(ReportPath)
Set PowerPlay = PowerplayReport.Application

PowerplayReport.SaveAs Left(Workbookpath, Len(Workbookpath) - 3) & "asc", 3, True
PowerplayReport.Close
PowerPlay.Quit
Set PowerplayReport = Nothing
Set PowerPlay = Nothing
[/b]
'Clears all data from the Cognos Extract sheet and pastes in the data from the Cognos .ppr that was saved as Excel
Sheets(SheetName).Select
Cells.ClearContents
Workbooks.Open Filename:=CognosDirectory + WorkBookName
Cells.Copy
Windows(Filename).Activate
Cells.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
LockSheet (ActiveSheet.Name)
Range("A1").Select 'deactivates selection of extract
Windows(WorkBookName).Close
Application.StatusBar = False
Kill CognosDirectory & WorkBookName

End Sub

 

[zimo975]

Hi jamguy,
about the activation of the Impromptu Macro don't you know anything?
Thanks,
Simona

 

[jamguy]

zimo975,

Not sure how within Excel, but I have passed parameters to an Impromptu Report via a Cognos Script then saved that IMR report as an IQD file....the syntax may be similar in VBA, but I haven't tried.

Sub open_report (s_openReport as string, s_OpenPath as string)

'REPORT PATH AND FILE NAME
myrep = s_OpenPath + s_openReport 

'PROMPT FOR YEAR AND MONTH
prompt_string = iYr + "|" + iPrd 
'SET THESE VALUES TO ANYTHING YOU WANT

set objImpRep = objImpApp.OpenReport(myrep, prompt_string)
iqd_file = gs_iqd + getfield(s_openReport, 1, ".") + ".iqd"

objImpRep.ExportTransformer iqd_file
objImpRep.closeReport

set objImpRep = Nothing

 

[ltborst]

Zimo,

I see that you have a macro that activates when the report opens. What version of cognos are you using? How did you do this?

Lisa