Impromptu from Excel

[Beginner2009]

I would like to run impromptu report from excel, is it possible? if yes can someone tell me how to do it as I am not very familiar with VBA.

 

[NuJoizey]

i think you need to clarify your question. What do you mean by run a report? Do you mean using excel as a reporting tool to pull information from another source? do you mean adjusting the print areas to print out existing information in a complex spreadsheet in a readable fashion....or what?

 

[strongm]

I think the OP may be referring to Cognos Impromtu, a reporting tool, vaguely like Crystal Reports.

Unfortunately I haven't used Impromtu for at least 13 years since it was rubbish (13 years ago; don't know about now), so can't really help.

 

[NuJoizey]

ah i see. sorry for the ignorance - well at least i learned something......

 

[drlex]

I suspect it refers to the application Cognos Impromptu.
There are a few threads here regarding such, and the application can be automated - you may need to load the type library ('impclient.tlb') which can usually be found in the installation path Cognos\cern\bin\, where 'n' is a number between 1 (old version) and 5 (current).
There's an old reference on the IBM (owners of Cognos) support site -

http://www-01.ibm.com/support/docview.wss?uid=swg21336736

soi la, soi carré

 

[Beginner2009]

Hi everybody, I was happy to see that people already tried to answer to my question!
To clarify a bit I meant using excel as a reporting tool to pull information from Cognos Impromptu. I want to use excel to get impromptu datas and play with these data in excel (chart..):

Just a beginner with VBA, I am trying to "eat" as many infos as I can on the subject.

I found out this and tried to readapt it to my needs but does not work!!!. I do not need the query to send email, so If someone could take out the bit realted to "sending email".

many thx for your contribution.

Sub Main()
On Error GoTo Err_Main

' ******************* Declare Variables ************************

Dim objImpApp As Object
Dim objImpRep As Object
Dim strCatalog As String
Dim strReport As String
Dim strFile As String
Dim strCatalogUserId As String
Dim strCatalogPassword As String
Dim strDatabaseUserId As String
Dim strDatabasePassword As String
Dim strErrorMsg As String
Dim strSendTo As String
Dim strSubject As String
Dim strBody As String
Dim strSaveIt As String
Dim ErrorCount As Integer

' ******************* Define Variables ************************

' Full path and name of the catalog to use
strCatalog = "N:\EDW_OPS\Catalog\OPS Catalog (Personal).cat"
' Full path and name of the Report to run
strReport = "N:\Desktop\ppx imr\test.imr"
' Full path and name of the output file
strFile = "N:\Desktop\ppx imr\testexcel.xls"
' Impromptu Catalog User Id
'strCatalogUserId = "User ID" ' Optional
' Impromptu Catalog Password
'strCatalogPassword = "Password"
' Database User Id
strDatabaseUserId = "User ID" ' Optional
' Database Password
strDatabasePassword = "Password" ' Optional
' Message to return if an error is encountered
strErrorMsg = " AGAIN An error was encountered."
' Recipient List
strSendTo = "Email Group Name"
' Email Subject
strSubject = "Email Subject"
' Email Body
strBody = "add anything else to the body of the Email."
' Save a copy of the e-mail (True or False)
strSaveIt = "False"

' ******************* Begin Execution ************************

ErrorCount = 0
Set objImpApp = CreateObject("Impromptu.Application")
Set objImpRep = CreateObject("Impromptu.Application")
objImpApp.Visible -1
objImpApp.OpenCatalog strCatalog, strCatalogUserId, strCatalogPassword, _
strDatabaseUserId, strDatabasePassword
Set objImpRep = objImpApp.OpenReport(strReport)
objImpRep.RetrieveAll
objImpRep.ExportExcelWithFormat (strFile)
objImpRep.CloseReport

Dim objLotusSes As Object ' Lotus Notes Session
Dim objLotusDbs As Object ' Lotus Notes Database
Dim objLotusDoc As Object ' Lotus Notes Document
Dim objLotusItem As Object ' Lotus Notes RichTextFile
Dim objAttachedFile As Object ' Attachment

Set objLotusSes = CreateObject("Notes.NotesSession")
Set objLotusDbs = objLotusSes.GETDATABASE("", "")
objLotusDbs.OPENMAIL
Set objLotusDoc = objLotusDbs.CREATEDOCUMENT()
Set objLotusItem = objLotusDoc.CREATERICHTEXTITEM("Attachment")
Set objAttachedFile = objLotusItem.EMBEDOBJECT(1454, "", strFile)
With objLotusDoc
.Subject = strSubject
.Body = strBody
.SaveMessageOnSend = strSaveIt
.Send False, strSendTo
End With

Exit_Main:
If ErrorCount = 0 Then
objImpApp.Quit
' MsgBox "Macro Successful!"
End If
Set objImpApp = Nothing
Set objImpRep = Nothing
Exit Sub

Err_Main:
objImpApp.Quit
MsgBox "Error # " & Err & Chr$(13) & strErrorMsg
ErrorCount = 1
Resume Exit_Main

End Sub

 

[Beginner2009]

Right, I've worked on it but when I run the query I have have after 5 sec a pop up :

"Run-time error '33974':
You have to specify the User Class Name when openning catalog in User version of Impromptu"

And this the query:

Thanks for your help.
--------------
Sub Main()

' ******************* Declare Variables ************************

Dim objImpApp As Object
Dim objImpRep As Object
Dim strCatalog As String
Dim strReport As String
Dim strFile As String
Dim strCatalogUserId As String
Dim strCatalogPassword As String
Dim strDatabaseUserId As String
Dim strDatabasePassword As String
Dim strErrorMsg As String
Dim strSendTo As String
Dim strSubject As String
Dim strBody As String
Dim strSaveIt As String
Dim ErrorCount As Integer

' ******************* Define Variables ************************

' Full path and name of the catalog to use
strCatalog = "N:\EDW_OPS\Catalog\OPS Catalog (Personal).cat"
' Full path and name of the Report to run
strReport = "N:\Desktop\ppx imr\ABC.imr"
' Full path and name of the output file
strFile = "N:\Desktop\ppx imr\123.xls"
' Impromptu Catalog User Id
'strCatalogUserId = "azerty" ' Optional
' Impromptu Catalog Password
'strCatalogPassword = "123456789"
' Database User Id
strDatabaseUserId = "qsdfgh" ' Optional
' Database Password
strDatabasePassword = "987654321" ' Optional
' Message to return if an error is encountered
strErrorMsg = " aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa!!!!!."
' Recipient List

' ******************* Begin Execution ************************

ErrorCount = 0
Set objImpApp = CreateObject("Impromptu.Application")
Set objImpRep = CreateObject("Impromptu.Application")
objImpApp.Visible -1
objImpApp.OpenCatalog strCatalog, strCatalogUserId, strCatalogPassword, _
strDatabaseUserId, strDatabasePassword
Set objImpRep = objImpApp.OpenReport(strReport)
objImpRep.RetrieveAll
objImpRep.ExportExcelWithFormat (strFile)
objImpRep.CloseReport


Exit_Main:
If ErrorCount = 0 Then
objImpApp.Quit
' MsgBox "Macro Successful!"
End If
Set objImpApp = Nothing
Set objImpRep = Nothing
Exit Sub

Err_Main:
objImpApp.Quit
MsgBox "Error # " & Err & Chr$(13) & strErrorMsg
ErrorCount = 1
Resume Exit_Main

End Sub

 

[JerryKlmns]

Beginner2009

take a look on my working version. Runtime version of Cognos Access Manager was giving me most of the trouble.

Sub ExtractOvrDrfts()
On Error GoTo Err_Impromptu


Dim objAuthApp As Object
Dim objAuthDoc As Object
Dim objImpromptu As Object
Dim objReport As Object
Const TheCatalog As String = "S:\Cognos\Impromptu\Catalog\Mart.cat"
Const Report_Folder="L:\"
Const TheReport As String = "MonthlyData.imr"
Const TheUserClassID As String = "xxxAdministrators"
Const TheUserName As String = "xxxxxx"
Const TheUserPassword As String = "xxxxxxxxx"
Const TheExportedFile As String = "MonthlyData.txt"
Dim TheDate As String
TheDate = Format(DMax("Formalised", "Ecl1", "CurMonth=True"), "yyyy-mm-dd")
'Runtime version of Cognos Access Manager cannot create the following objects to log on to the database automagicaly
'Set objAuthApp = CreateObject("Authenticator2.Application")
'Set objAuthDoc = objAuthApp.Documents.OpenWithBasicSignon("Default", TheUserName, TheUserPassword, TheUserClassID)
Set objImpromptu = CreateObject("CognosImpromptu.Application")
objImpromptu.Visible True
objImpromptu.OpenCatalog TheCatalog ' , TheUserClassID, , , , 1 'Using Cognos Access Manager
objImpromptu.Visible False
Set objReport = objImpromptu.OpenReport(Report_Folder & TheReport, TheDate)
objReport.RetrieveAll
If Dir(Report_Folder & TheExportedFile) <> "" Then Kill Report_Folder & TheExportedFile
objReport.ExportASCII Report_Folder & TheExportedFile, True, False, ";"
objReport.CloseReport
objImpromptu.Quit
'objAuthDoc.Close
'objAuthApp.Quit

Exit_Impromptu:
  Set objReport = Nothing
  Set objImpromptu = Nothing
'  Set objAuthDoc = Nothing
'  Set objAuthApp = Nothing
  Exit Sub

Err_Impromptu:
  MsgBox Err.Number & ": " & Err.Description
  Resume Exit_Impromptu

End Sub

I also have a version that feeds a file full of pick list values into an imr

 

[Beginner2009]

Hi Jerry, Many thanks for your answer.
Unfortunately I am still struggling with that error message ("Run-time error '33974':
You have to specify the User Class Name when openning catalog in User version of Impromptu").

Can you check what went wrong in the query and why do I have that re-occuring message poping up.
Many thanks.

 

[JerryKlmns]

Beginner2009

When I run this code, I leave cognos to ask for user credentials since

'Runtime version of Cognos Access Manager cannot create the following objects to log on to the database automagicaly
'Set objAuthApp = CreateObject("Authenticator2.Application")
'Set objAuthDoc = objAuthApp.Documents.OpenWithBasicSignon("Default", TheUserName, TheUserPassword,

So I just open the catalog

objImpromptu.OpenCatalog TheCatalog

and let it bark for burglars!!

Did you try the same??

 

[Beginner2009]

Jerry,

did you mean changing my query from:

Sub Main()

' ******************* Declare Variables ************************

Dim objImpApp As Object
Dim objImpRep As Object
Dim strCatalog As String
Dim strReport As String
Dim strFile As String
Dim strCatalogUserId As String
Dim strCatalogPassword As String
Dim strDatabaseUserId As String
Dim strDatabasePassword As String
Dim strErrorMsg As String
Dim strSendTo As String
Dim strSubject As String
Dim strBody As String
Dim strSaveIt As String
Dim ErrorCount As Integer

' ******************* Define Variables ************************

' Full path and name of the catalog to use
strCatalog = "N:\EDW_OPS\Catalog\OPS Catalog (Personal).cat"
' Full path and name of the Report to run
strReport = "N:\Desktop\ppx imr\ABC.imr"
' Full path and name of the output file
strFile = "N:\Desktop\ppx imr\123.xls"
' Impromptu Catalog User Id
'strCatalogUserId = "azerty" ' Optional
' Impromptu Catalog Password
'strCatalogPassword = "123456789"
' Database User Id
strDatabaseUserId = "qsdfgh" ' Optional
' Database Password
strDatabasePassword = "987654321" ' Optional
' Message to return if an error is encountered
strErrorMsg = " aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa!!!!!."
' Recipient List

' ******************* Begin Execution ************************

ErrorCount = 0
Set objImpApp = CreateObject("Impromptu.Application")
Set objImpRep = CreateObject("Impromptu.Application")
objImpApp.Visible -1
objImpApp.OpenCatalog strCatalog, strCatalogUserId, strCatalogPassword, _
strDatabaseUserId, strDatabasePassword
Set objImpRep = objImpApp.OpenReport(strReport)
objImpRep.RetrieveAll
objImpRep.ExportExcelWithFormat (strFile)
objImpRep.CloseReport


Exit_Main:
If ErrorCount = 0 Then
objImpApp.Quit
' MsgBox "Macro Successful!"
End If
Set objImpApp = Nothing
Set objImpRep = Nothing
Exit Sub

Err_Main:
objImpApp.Quit
MsgBox "Error # " & Err & Chr$(13) & strErrorMsg
ErrorCount = 1
Resume Exit_Main

End Sub
---------------
Into this:

Sub Main()

' ******************* Declare Variables ************************

Dim objImpApp As Object
Dim objImpRep As Object
Dim strCatalog As String
Dim strReport As String
Dim strFile As String
Dim strCatalogUserId As String
Dim strCatalogPassword As String
Dim strDatabaseUserId As String
Dim strDatabasePassword As String
Dim strErrorMsg As String
Dim strSendTo As String
Dim strSubject As String
Dim strBody As String
Dim strSaveIt As String
Dim ErrorCount As Integer

' ******************* Define Variables ************************

' Full path and name of the catalog to use
strCatalog = "N:\EDW_OPS\Catalog\OPS Catalog (Personal).cat"
' Full path and name of the Report to run
strReport = "N:\Desktop\ppx imr\ABC.imr"
' Full path and name of the output file
strFile = "N:\Desktop\ppx imr\123.xls"
' Impromptu Catalog User Id
'strCatalogUserId = "azerty" ' Optional
' Impromptu Catalog Password
'strCatalogPassword = "123456789"
' Database User Id
strDatabaseUserId = "qsdfgh" ' Optional
' Database Password
strDatabasePassword = "987654321" ' Optional
' Message to return if an error is encountered
strErrorMsg = " aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa!!!!!."
' Recipient List

' ******************* Begin Execution ************************

ErrorCount = 0
Set objImpApp = CreateObject("Impromptu.Application")
Set objImpRep = CreateObject("Impromptu.Application")
objImpApp.Visible -1
objImpApp.OpenCatalog strCatalog
Set objImpRep = objImpApp.OpenReport(strReport)
objImpRep.RetrieveAll
objImpRep.ExportExcelWithFormat (strFile)
objImpRep.CloseReport

Exit_Main:
If ErrorCount = 0 Then
objImpApp.Quit
' MsgBox "Macro Successful!"
End If
Set objImpApp = Nothing
Set objImpRep = Nothing
Exit Sub

Err_Main:
objImpApp.Quit
MsgBox "Error # " & Err & Chr$(13) & strErrorMsg
ErrorCount = 1
Resume Exit_Main

End Sub
--------------------------------------------------
If yes I still have the same results: ("Run-time error '33974':
You have to specify the User Class Name when openning catalog in User version of Impromptu").

Grrrrrr!!!

 

[JerryKlmns]

This line

Set objImpRep = CreateObject("Impromptu.Application")

has to be removed

I suggest that you take some time and modify my code to meet your needs

 

[drlex]

The error message indicates you need to change the OpenCatalog command to include the UserClass.
The syntax is:
Application.OpenCatalog CatalogName ,[UserClassID] ,[UserClassPassword] ,[DatabaseID] ,[DatabasePassword] ,[ConnectToDatabase] ,[DistributedCopyFilePath]

so your line of code should be

objImpApp.OpenCatalog strCatalog, strCatalogUserId

and you'll need to uncomment the line where you set the value for strCatalogUserId.

soi la, soi carré

 

[Beginner2009]

Thanks guys for feedback,

drlex, would you please update the query with your remark as I tried but without results.
Thanks

Jerry,
I tried to take yours and update it to my needs but it gives me the same results.

 

[drlex]

Your code runs for me, even without the changes Jerry and I have suggested, when I use a basic report on an unprotected catalog. FWIW, I am using Impromptu 7.4 and Excel 2007.

Perhaps you could try creating a simple catalog (without protection & hence the need to identify the user class) & a report from it so that you can test the code that you have?


soi la, soi carré