Skip to Content
0
Former Member
Aug 19, 2008 at 06:49 PM

VB: Scheduling a report

45 Views

Hi All,

I need to be able to add a user/group to a report, change the option to either shortcut or copy, and then schedule the report to the user's/group's inboxes? Can someone help please.....

Private Sub Schedule_Click()

On Error GoTo Err_Handler

Dim SessionManager As SessionMgr

Set SessionManager = CreateObject("CrystalEnterprise.SessionMgr")

Dim Sess As EnterpriseSession

'Log the user onto Crystal Enterprise

Set Sess = SessionManager.Logon(Worksheets("Sheet1").Cells(3, 2).Value, TextBox1, Worksheets("Sheet1").Cells(5, 2).Value, Worksheets("Sheet1").Cells(6, 2).Value)

'Check to see if logon failed.

Dim IStore As InfoStore

Dim pluginInterface

'Create the InfoStore object.

Set IStore = Sess.Service("", "InfoStore")

Dim Result As InfoObjects

Dim InfoObject As InfoObject

'Query for the report's scheduling options.

Dim row

Dim ScheduleInfo As SchedulingInfo

Dim user

Dim Report As Report

Dim NewParam

Dim parameter, CurrentValueCollection

Dim col

Dim Name

Dim format

Const ceCrystalReport = 0

Const ceFormatExcel = 1

Const ceFormatWord = 2

Const ceFormatPDF = 3

Const ceFormatRTF = 4

Const ceFormatTextPlain = 5

Const ceFormatTextPaginated = 6

Const ceFormatTextTabSeparated = 7

Const ceFormatTextCharacterSeparated = 8

Const ceFormatExcelDataOnly = 9

row = 9

Do Until Worksheets("Sheet1").Cells(row, 1).Value = ""

Set Result = IStore.Query("Select SI_ID from CI_infoobjects WHERE SI_PROGID = 'CrystalEnterprise.Folder' AND SI_NAME='" & Worksheets("Sheet1").Cells(row, 1).Value & "'")

Set Result = IStore.Query("Select SI_ID, SI_OWNERID, SI_SCHEDULEINFO, SI_PROCESSINFO from CI_infoobjects WHERE SI_PROGID = 'CrystalEnterprise.Report' AND SI_PARENT_FOLDER=" & Result(1).ID & " AND SI_Instance='False' AND SI_NAME='" & Worksheets("Sheet1").Cells(row, 2).Value & "'")

'Retrieve the report from the InfoObjects collection.

Set InfoObject = Result.Item(1)

' MANAGED OPTIONS

'Set Result1 = IStore.Query("Select SI_DEST_SCHEDULEOPTIONS, SI_PROGID " + " From CI_SYSTEMOBJECTS Where SI_NAME='CrystalEnterprise.Managed'")

'Set boInbox = Result1.Item(1)

'Create an interface to the scheduling options for the report.

Set ScheduleInfo = InfoObject.SchedulingInfo

'Set schedule options

ScheduleInfo.RightNow = True

'ScheduleInfo.Type = 0

ScheduleInfo.Type = ceScheduleTypeOnce

Set user = IStore.Query("Select SI_ID FROM CI_SYSTEMOBJECTS WHERE si_progid='CrystalEnterprise.User' AND SI_NAME='" & Worksheets("Sheet1").Cells(row, 3).Value & "'")

ScheduleInfo.ScheduleOnBehalfOf = user.Item(1).ID

Edited by: Kevin Pham on Aug 19, 2008 8:55 PM