Cognoscript Sceduler help

[goofaholix]

Below is a prototype script that i've written by cobbling together the samples "Sales by Country (Publish to HTML).mac and Open Report.mac.

The idea is that it will be used as a template with the configurable settings (the prompt parameters and the type of file it will be exported to etc) being changed for each scheduled instance I need to set up, then the macro will be scheduled in Impromptu Scheduler.

Is there a better/tidier way to do it?
I think there is redundant code in the script but it tends to fall over when I try to delete it, is there any way to simplify it?
I noticed that if I've already got the report open it comes up with an "open as read only?": dialog box, so needs user input, is there any way I can alter the code to avoid that?
The next step is to add code to pass the file to MS Outlook SP-3 for emailing, as a non programmer I don't think I'm up to it, does anyone have a snippet of code that does that?

Thanks.

The script-------------------------------------------

'The configurable settings below must be set for each instance to be scheduled.

Option Explicit

'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

'Function for retrieving registry value
Declare Function GetInfo(lngClassKey As Long, strSectionKey As String, _
lngReserved As Long, lngSecurity As Long, strValueName As String) As Variant

'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)

Sub Main()
Dim ImpApp as Object
Dim ImpRep as Object
Dim ImpHTMLRep as Object
Dim ImpPath as String
Dim PubPath as String
Dim intPosition as Integer
Dim lngClassKey As Long
Dim strSectionKey As String
Dim lngReserved As Long
Dim lngSecurity As Long
Dim strValueName As String
Dim strPromptValues As String
Dim strReport As String
Dim strPub As String
Dim PublishType As String
Dim objPub as Object

'set the Application Object to Impromptu
Set ImpApp = CreateObject("CognosImpromptu.Application")

'make Impromptu invisible
ImpApp.Visible 0

'********************************************************
'The configurable settings:

'Path for the Impromptu report
ImpPath = "\\Dev1\ps enterprise\Impromptu Reports\Report Development\"

'The Report name
Const ReportOne = "test.imr"

'The Report Parameters
strPromptValues = "2004-01-01|2004-12-31|Summary||||||"

'The Type of file to Publish to
PublishType = "xls"

'Path to save the Published report
PubPath = "\\Dev1\ps enterprise\Impromptu Reports\Report Development\Output\"

'The Published file name
Const FileOne = "test.xls"

'********************************************************

'Run the report
strReport = ImpPath & ReportOne
Set ImpRep = ImpApp.OpenReport (strReport, strPromptValues)
ImpApp.Activate

'Publish the Report
strPub = PubPath & FileOne
If PublishType = "pdf" Then
Set objPub = ImpRep.PublishPDF
objPub.Publish strPub
Elseif PublishType = "xls" Then
ImpRep.ExportExcelWithFormat strPub
End If
End Sub



'------------------------------------------------------------------------------
Function GetInfo(lngClassKey As Long, strSectionKey As String, _
lngReserved As Long, lngSecurity As Long, strValueName As String) As Variant

Dim lngResult As Long
Dim lngOpen As Long
Dim lngQuery As Long
Dim lngClose As Long
Dim lngBuffer As Long
Dim strValueData As String
Dim vntValue, NullChar

'-- open the subkey --
lngOpen = RegOpenKeyEx(lngClassKey, strSectionKey, lngReserved, _
lngSecurity, lngResult)

'-- Set buffer space --
strValueData = String$(255, 0)
lngBuffer = Len(strValueData)

'-- read the value --
lngQuery = RegQueryValueExStr(lngResult, strValueName, lngReserved, _
REG_SZ, strValueData, lngBuffer)

If lngQuery = ERROR_SUCCESS Then
vntValue = strValueData
NullChar = InStr(vntValue, Chr(0))
If NullChar > 0 Then
vntValue = Left(vntValue, NullChar - 1)
End If
End If

'-- close key --
lngClose = RegCloseKey(lngResult)

'-- return string value from RegQueryValueExStr --
GetInfo = Left$(vntValue, lngBuffer)

End Function
-----------------------------------------



Bruce