Open report and save to .xls file

[klaasjanboven]

Hello every one. I'm trying to create a mcro that will open a report (at the background) an saves the output to a new .xls file
I can't get it right but i think thats because my programming skills in basic are not very high. This is what i got so far

Sub Main()
Dim objImpApp As Object
Dim objImpCat As Object
Dim objCatFolders As Object
Dim strReport As String
Dim strReportPath as String
Dim GebruikteCat as String
Dim strExcelFileName As String

GebruikteCat="L:\path\Impromptu\Catalogus\P_DDSSERVER.CAT"
ImpPath = "C:\Program Files\Cognos\cer4\bin\impadmin.exe"
ReportOne = "L:\path\Impromptu\Rapporten\fout_adressen.imr"
strExcelFileName = "c:\temp\blaat.xls"

objImpApp.OpenCatalog GebruikteCat, "Creator",,"user","pass",-1,,
Set objImpApp = CreateObject("CognosImpromptu.Application")
Set objImpCat = objImpApp.ActiveCatalog
Set objImpRep = objImpApp.OpenReport(ReportOne)
objImpApp.Visible True


MsgBox "We gaan dingen doen met "& ImpPath & " We doen dat met de volgende cat " & GebruikteCat & " met het volgende rapport " & ReportOne

On Error GoTo ErrorRoutine

Done:
Exit Sub

ErrorRoutine:
If Err = 429 Then
MsgBox "Open Impromptu en de catalogus, " & _
"en voer vervolgens de macro nogmaals uit."
Else
MsgBox "Foutnummer: " & Err & " opgetreden in regel " & Erl
End If
Resume Done

End Sub

 

[drlex]

klaasjanboven,
A couple of points I notice:
1) The error trapping line needs to be before the lines to test.
2) You're opening the catalog before Impromptu
You might also have too many commas on the opencatalog command.

Try this (amends in bold); I've added in some lines to save the report and close the application.

Sub Main()
   Dim objImpApp As Object
   Dim objImpCat As Object
   Dim objCatFolders As Object
   Dim strReport As String
   Dim strReportPath as String
   Dim GebruikteCat as String
   Dim strExcelFileName As String
   
        GebruikteCat="L:\path\Impromptu\Catalogus\P_DDSSERVER.CAT"
      ImpPath = "C:\Program Files\Cognos\cer4\bin\impadmin.exe"
      ReportOne = "L:\path\Impromptu\Rapporten\fout_adressen.imr"
      strExcelFileName = "c:\temp\blaat.xls"
   
         Set objImpApp = CreateObject("CognosImpromptu.Application")
	 [b]objImpApp.OpenCatalog GebruikteCat, "Creator",,"user","pass",-1[/b]   
         Set objImpCat = objImpApp.ActiveCatalog
         Set objImpRep = objImpApp.OpenReport(ReportOne)
         objImpApp.Visible True
         [b]objImpRep.ExportExcelwithFormat strExcelfilename
         objImpRep.CloseReport
         objImpApp.Quit
         Set objImpRep = Nothing
         Set objImpApp = Nothing[/b]
      
   MsgBox   "We gaan dingen doen met "& ImpPath & " We doen dat met de volgende cat " & GebruikteCat & " met het volgende rapport " & ReportOne
                     
   On Error GoTo ErrorRoutine
   
Done:
Exit Sub  
   
ErrorRoutine:
   If Err = 429 Then
      MsgBox "Open Impromptu en de catalogus, " & _
              "en voer vervolgens de macro nogmaals uit."
   Else
      MsgBox "Foutnummer: " & Err & " opgetreden in regel " & Erl
   End If
   Resume Done

End Sub

soi la, soi carré

 

[klaasjanboven]

Hello,

I've tried teh solution as written above but i'm still not getting the proper information. Instead i get an error:

File1.MAC(20) W--2125: 'objImpRep is not a recordtype'
File1.MAC(21) W--2125: 'objImpRep is not a recordtype'

Lines 20,21 are the next
objImpRep.ExportExcelwithFormat strExcelfilename
objImpRep.CloseReport

I Hope anyone can help me.

Klaasjan

 

[drlex]

Klassjan,
My error; you need to specify ObjImpRep as an object
Add in a line
Dim ObjImpRep as Object
to the other Dim statements.
lex

soi la, soi carré