Run Impromptu Report Macro 2

[ADB1]

Hi,

I am wanting to create a macro that open an impromptu report, runs it and then saves it as xls. I have managed to create the following....

Sub Main()
Dim objImpApp as Object
Dim objImpRep as Object
Dim strExcelFileName As String
Set objImpApp = CreateObject("CognosImpromptu.Application")
objImpApp.Visible True
Set objImpRep = objImpApp.OpenReport("N:\Cognos Development\Land Rover Ops\Impromptu\Land Rover UK Stock VIN V2 Limited Columns.imr")
strExcelFileName = Left$(objImpRep.FullName, Len(objImpRep.FullName) - 4)
objImpRep.ExportExcel strExcelFileName & ".xls"
'objImpRep.CloseReport
'objImpApp.Quit
Set objImpRep = Nothing
Set objImpApp = Nothing
End Sub

However, this opens Impromptu and then requests the catalog logon details which I want to be able to pass thorugh in the macro so it can run overnight. Can anyone tell me how to do this?

Thanks.

A.

 

[drlex]

ADB1,
Try adding a line after objImpApp.Visible.True to open the catalogue with the details required

format is
Application.OpenCatalog CatalogName ,[UserClassID] ,[UserClassPassword] ,[DatabaseID] ,[DatabasePassword] ,[ConnectToDatabase] ,[DistributedCopyFilePath]

Since I don't use DB IDs, then my version is

objImpApp.OpenCatalog "k:\catalogues\budgets.cat","Creator",,,,1
[code]

Pop your user class password and Database info (in double quotes) between the commas after the UserClass ID ("Creator" in my example)

lex

[i]soi la, soi carré[/i]

 

[ADB1]

Thanks, I'll give it a go.

A.

 

[ADB1]

My macro seems to fall overe on the[DistributedCopyFilePath] part. I get the folowing error....

Impromptu Parameter 6 is invalid


I presume Paramaeter 6 is the CopyFilepAth part?!

A.

 

[drlex]

Unless you're using a distributed catalog, leave out parameter 6.
You should have something like

objImpApp.OpenCatalog "k:\catalogues\budgets.cat","Creator","catpassword","mydatabase",mydatabaseconnectionpassword",1

soi la, soi carré

 

[ADB1]

Thanks lex.

However, I still get the error. My line reads......

objImpApp.OpenCatalog "N:\Cognos Development\Ops\Catalogue\OPERATIONS.CAT","Creator",,"user1","password","Teradata",1

 

[drlex]

I can't reconcile your entries with the format required.
What is your userclass ID for the catalogue? Is it Creator or user1?

You need it to be in the format:

objImpApp.OpenCatalog "N:\Cognos Development\Ops\Catalogue\OPERATIONS.CAT", "[UserClassID]", "[UserClassPassword]", "[DatabaseID]", "[DatabasePassword]" ,1


soi la, soi carré

 

[ADB1]

Sorted after a bit of playing. Many thanks for your help.

A.

 

[drlex]

glad to help; thanks for the star.
The help feature of the macro editor is where I learnt about macros (not having any VBA experience).

soi la, soi carré

 

[ahstyx]

Hello,

I got a piece of code, similar to the one above. Since we have merged from WinNT to WinXP and Impromptu 6 to Impromptu 7.1 the code does not work anymore.
Calling the "OpenReport" statement it stops with a runtime error 24634. Translated from German, the info is: "Input-Variable invalid or not specified for this report".
The report itself runs as always, by starting it in Impromptu manually or by clicking it inside the Windows Explorer.

This ist the relevant part of the code:
impPath = "C:\Program Files\Cognos\cer3\bin\ImpUser.exe"
catPath = "W:\ALLE\Fondsbuchhaltung\Kataloge\v3fonds.cat"

'Impromptu starten
Shell (impPath), vbMaximizedFocus

'Impromptu als Objekt definieren
Set impromp = CreateObject("CognosImpromptu.Application")

' Katalog öffnen
impromp.opencatalog catPath, "Endbenutzer"

'At this point, the Macro stops!
Set bericht = impromp.OpenReport("c:\Documents and Settings\U32462\My Documents\Vermoegensaufstellung.imr")

bericht.ExportExcel ("c:\Documents and Settings\U32462\My Documents\Vermoegensaufstellung.xls")
bericht.CloseReport
Set bericht = Nothing

I exchanged some variables with the real paths for this post.
If someone can give me a hint for a solution, I would be very thankful.

Best regards from Cologne,
ahstyx.

 

[drlex]

One thing occurs to me, ahstyx - you're missing the catalog user - e.g. "Creator" and any password plus other information as required.
See the second post in this thread for the format expected.


soi la, soi carré

 

[griffindm]

It seems a little odd that you are opening Impromptu User via a Shell command. The CreateObject call will open it anyway.

Does the Catalog Open dialog appear before the macro stops?

Regards,

Dave Griffin


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

http://www.decisionsupportgroup.com

 

[ahstyx]

Thanks to both of you.

the catalog login-data is stored, so that I only have to confirm the catalog dialog when opening a report manually. With the code above it has been working all the time. It was not necessary to confirm the dialog. It did not appear.
Now it seems, that the catalog will not be opened, because in the Impromptu Application Window there is no active catalog. It seems a bit strange to me, because the error appears at the OpenReport command.

I tried it with "impromp.opencatalog catPath, "Endbenutzer"
,,,,1" , but there was no dirrerent reaction.

And I commented the Shell command out. Only difference is, that the Imp Application does not start. The error message ist the same at the same place.

Best regards,
ahstyx.

 

[drlex]

Another thought - have you upgraded the catalog from v6 to v7.1? Perhaps if Impromptu is not visible, then one cannot see a prompt for upgrading and the routine fails.
I'd consider creating a new test catalog and report, and then a simple macro like ADB's in the first post. That might help identify where the problem lies.

soi la, soi carré

 

[ahstyx]

Thanks, but this macro is running me mad.
The code from the top is running only on one of my computers. Thats some kind of progress, but now it seems, that I have trouble with the rights on the file system. After managing this, I will try to customize the code to my needs and then I will compare both code snippets, my old one and the new one.
I will give feedback if it works, cry for help again if not!

Best regards,
ahstyx.

 

[drlex]

Thanks, but this macro is running me mad.
The code from the top is running only on one of my computers. Thats some kind of progress, but now it seems, that I have trouble with the rights on the file system.

Agreed; one is better than none! I assume that Impromptu will run ok on the other machines; it can require configuration for where it stores temp. data and I've seen stand-offs between it and Windows on permissions issues.
Good luck!
lex

soi la, soi carré

 

[B555]

I had to do the same thing, here is an example of what I did. This will also e-mail the report to someone as well. I hope this helps. B555

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 = "Catalog name and Path.cat"
' Full path and name of the Report to run
strReport = "Report name and path.imr"
' Full path and name of the output file
strFile = "c:\winnt\temp\name of report.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 = "An error was encountered in the Main 6 Daily Macro. Please re-run and re-send."
' 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