How to run an Impromptu report into VB

[lstephane]

Hi,

I would like to know how can I run an Imprmptu report in Vb and, than, export results to a text file.

Thanks !

 

[mrpauly]

There are plenty of examples in this forum if you read. Just do a keyword search like 'vb' or 'visual basic' 'vb export report', etc. If you still need help, email me your specific needs. Also, if you click on Impromptu help under macros you find some good info.

Have fun...
Paul




Here is some sample code below....

Dim objImpApp As Object
Dim objImpRep As Object

'Cognos

'Registry Function Prototypes
Declare Function RegOpenKeyEx Lib "advapi32" Alias "RegOpenKeyExA" _
(ByVal hKey As Long, ByVal lpSubKey As String, ByVal ulOptions As Long, _
ByVal samDesired As Long, phkResult As Long) As Long

Declare Function RegQueryValueExStr Lib "advapi32" Alias "RegQueryValueExA" _
(ByVal hKey As Long, ByVal lpValueName As String, ByVal lpReserved As Long, _
ByRef lpType As Long, ByVal szData As String, ByRef lpcbData As Long) As Long

Declare Function RegCloseKey Lib "advapi32" (ByVal hKey As Long) As Long

'Registry Specific Access Rights
Const KEY_ALL_ACCESS = &H3F

'Define severity codes
Const ERROR_SUCCESS = 0&

'Predefined Registry Class Constant
Const HKEY_LOCAL_MACHINE = &H80000002

'Predefined Registry Value Type
Const REG_SZ = (1) 'Unicode null terminated string

Public Function fCognosDate(dDate As String) As String

fCognosDate = Right(dDate, 4) & "-" & Left(dDate, 2) & "-" & Mid(dDate, 4, 2)

End Function


Public Function fOpenReport(sReportPath As String, _
sReport As String, _
sPromptValues As String, _
sExportMethod As String, _
sExportPath As String, _
sFileName As String)

On Error GoTo errMessage
Dim x As Variant

sReport = sReportPath & sReport

'Report
Set objImpApp = GetObject("CognosImpromptu.Application&quot

'Prompt Values
If sPromptValues = "" Then
Set objImpRep = objImpApp.OpenReport(sReport)
Else
Set objImpRep = objImpApp.OpenReport(sReport, sPromptValues)
End If

'objImpApp.Activate
objImpRep.RetrieveAll

'Checks the contents of the first row and column of the query data
On Error Resume Next 'To turn off error trapping
x = objImpRep.GetDataValue(1, 1)
On Error GoTo errMessage

'If Report contains info export
If IsEmpty(x) = False Then
'Export Report
Select Case sExportMethod

Case "Excel"
'Export To Excel
sFileName = sExportPath & sFileName & ".xls"
objImpRep.ExportExcel sFileName

Case "Text"
sFileName = sExportPath & sFileName & ".csv"
objImpRep.ExportText sFileName

Case "CSV"
sFileName = sExportPath & sFileName & ".csv"
objImpRep.ExportASCII sFileName

Case "ExcelFormat"
objImpApp.Visible True
objImpApp.Activate
SendKeys "%F", 1
SendKeys "A", 1
SendKeys "{TAB}", 1
SendKeys "{DOWN 8}", 1
SendKeys "{ENTER}", 1
SendKeys "+{TAB}", 1
SendKeys sExportPath & sFileName, 1
Debug.Print sFileName
SendKeys "{ENTER}", 1

Case "Pdf"
'Publish to PDF
Dim objPDFPub As Object
Set objPDFPub = objImpRep.PublishPDF
objPDFPub.Publish sExportPath & sFileName & ".pdf"
Set objPDFPub = Nothing

End Select
End If

'Close Report
'objImpRep.CloseReport
Set objImpRep = Nothing
Set objImpApp = Nothing
fOpenReport = True
Exit Function


errMessage:
MsgBox "There is a problem." & vbCrLf & _
"Error Desc: " & Err.Description & vbCrLf & _
"Error Num: " & Err.Number & _
"Error Line: " & Err.LastDllError, vbCritical, cTitle
fOpenReport = False
Exit Function

End Function

 

[lstephane]

Thanks a lot...

I will try this today and keep you in touch...

 

[lstephane]

Hi Paul,

I just tryed your ecample and I received an error when I run the program.

-- BEGIN OF CODE --
Sub ValidTbl()
'Local variables<
Dim objImrApp As Object
Dim objImrRpt As Object

Dim strRptName As String
Dim strCountVal As String
Dim strConnect As String

Dim x

'Increment step counter
Call Step_Setup

'Initialization
Set objImrApp = GetObject(&quot;CognosImpromptu.Application&quot

'Connecting to Impromptu catalog
Set objImrRpt = objImrApp.OpenReport(g_strTblQryName)

'Open Impromptu report
End Sub
-- END OF CODE ---

--- BEGIN OF ERROR MSG ---
Run-time error '-2147221020 (800401e4)':

Automation error
Invalid syntax

<Continue> <End> <Debug> <Help>
--- END OF ERROR MSG ---

Can you help me ?

Stéphane Lambert, Analyst/Programmer - Cognos Specialist
Info Quest (IQ), Data Warehouse System
McKesson Canada Corporation

EMail: stephane.lambert@mckesson.ca