Execute macro in scheduled Deski document???

system · January 11, 2006, 2:20pm

Hey,

is it possible to schedule a deski document and execute the macro in this document?
How do I execute a (vb)macro on the XIR2 server???

Thanks for your help!!!
Joachim

Joachim74 (BOB member since 2005-09-22)

system · January 11, 2006, 8:16pm

Put the code in the Document_AfterRefresh event, and as long as you are running on a Windows server, it will execute when a scheduled refresh occurs. There is no longer an option to trigger a VBA procedure as a separate action (like in BCA).

Dwayne Hoffpauir (BOB member since 2002-09-19)

system · March 8, 2006, 1:05pm

From BO I got the message, that VBA macros are no longer supported on schedules!!

Now I try to find a simple way to do the same with vbscript, but everything failes …

Joachim74 (BOB member since 2005-09-22)

system · March 8, 2006, 1:31pm

This is incorrect information.

Ensure that VBA is installed on the server. In my case the first installation did not install (I do not know why as i do have the logs of previous install to investigate further). I did a reinstall and VBA was installed this time and i am able to schedule a deski report with a macro.

Thanks, Mani
-----[/code]

nnemani (BOB member since 2004-09-28)

system · March 8, 2006, 2:07pm

I know that some functions still work,
but Business Objects does no longer support executing VBA on the server (scheduling): xir2_mig_devsuite_en.pdf (page 28 )

Changes to server side execution
In BusinessObjects Enterprise XI R2, the Desktop Intelligence report engine
and job server have changed. As a result, there are some constraints:
The new platform scheduler does not support custom VBA macros.

VBA add-ins cannot be executed on the server. The new report engine
does not support opening multiple documents at the same time. For
example, a document that includes a Web Connect data provider will fail
to refresh (DPVBAInterface code would need to be in the document itself
to be executed).
CreateObject(“BusinessObjects.Application”) will fail. The new report
engine is not a COM automation server.
Documents.Open or Documents.OpenFromEnterprise will fail. The new
report engine does not support opening multiple documents at the same
time.

Joachim74 (BOB member since 2005-09-22)

system · March 8, 2006, 2:19pm

Yes, I am aware of a limited support for VBA in XIR2.

nnemani (BOB member since 2004-09-28)

system · March 8, 2006, 2:46pm

That is simply not true. Please see my earlier post for a clarification.

Dwayne Hoffpauir (BOB member since 2002-09-19)

system · March 8, 2006, 2:58pm

One other limitation is anything that is interactive will not work. Things like custom menus / toolbars, VBA userforms, MsgBox will only work using the client installation of Desktop Intelligence. Executing the report on the server (interactive or scheduled) appears to simply “lock up” waiting for response to anything interactive.

Dwayne Hoffpauir (BOB member since 2002-09-19)

system · March 23, 2006, 4:06pm

Where and how could i do that. Could someone provide me more explanation about how to execute an embed VBA macro when a report is schedule, or which doc contains this information, can’t find anything in admin guide.

Thanks

jerryf (BOB member since 2006-02-13)

system · March 23, 2006, 5:53pm

The technique is not really specific to scheduling. It is related to the SDK, and you’ll find the documentation in the Developer / SDK areas. Briefly, the steps are as follows:[list]- From Desktop Intelligence, press Alt-F11 to start the VB Editor

On the left, double-click the ThisDocument module
Copy and paste in the following code:

Private Sub Document_AfterRefresh()
    'put your code here
End Sub

[/list]Whenever the document is refreshed (manually, or when scheduled), the code will run.

Dwayne Hoffpauir (BOB member since 2002-09-19)

system · April 3, 2006, 7:20pm

I have followed your directions and the macro works great on the client side but I can not get the macro to run on the server.

I have manually refreshed the doc on the server and I have also scheduled the dcsument and the effect is no macro execution.

here is my macro.

Thanks in advance for any help.
Michael Cornelison

Private Sub Document_Afterrefresh()
    Dim returnValue As String
    returnValue = SaveAsHTML()
End Sub

 Private Sub document_open()
    ThisDocument.Refresh
 End Sub


Function SaveAsHTML() As Boolean
    Dim ThisDoc As Document
    Dim ThisReport As Report
    On Error Resume Next  '* Refresh the current report
    Set ThisDoc = ActiveDocument
    Set ThisReport = ThisDoc.ActiveReport       'active report
    Dim HTMLName As String
    HTMLName = "C:\Temp\" &amp; ThisReport.Name
    Kill (HTMLName)                      'Delete existing file
    SaveAsHTML = ThisReport.ExportAsHtml(HTMLName)
End Function

Moderator Edit: Added BBCode to distinguish VBA Code. - Michael

mcornel (BOB member since 2004-05-11)

system · April 10, 2006, 9:00pm

with the removal of the ability to run VBA externally to the reports, how does one now go about breaking up an instance of desktop intelligence report into multiple files (eg PDF) based on something other than users and profiles?

any ideas?
thanks

icytrue (BOB member since 2006-04-10)

system · April 14, 2006, 7:02pm

Did anyone make this to work? I am able to execute macro on client in after_refresh event but scheduler(server) it is not executing?? Do we have any setting on server ???

dcdas (BOB member since 2002-06-17)

system · April 17, 2006, 7:17pm

If breaking a report into muliple instances is the core issue, then this can be accomplished by using the “publication” feature of XI r2. The publisher is 6.5 is part of XIR2 and this should take care of bursting the reports to multiple groups.

NBG (BOB member since 2005-07-18)

system · April 18, 2006, 6:53pm

I think the “publisher” part is available only if you have a “premium” license which is an upgrade of the “professional” license.

You need premium to do multi-pass bursting and make use of publication profiles.

Irwin Miller (BOB member since 2002-08-16)

system · April 20, 2006, 5:53pm

This doesn’t surprise me, as Publisher was an expensive upgrade in v2.7.x and v6.x!

DJ

DJ06482 (BOB member since 2002-11-22)

system · April 20, 2006, 7:17pm

I have a macro that sets default values for a date prompt and then creates PDF files using the ExportPDF function. I also write to a log file to trace what is happening.

When I run this DeskI report from my workstation and set Application.Interactive true everything works fine.

But then I export to CMC and schedule it to run. The instance created has the correct date periods and the report is correct. But no PDF files are created and the log file reflects this. In the logfile, there is an indication of an ERROR 100 - No current report plus gibberish for the report name.

Anyone have any ideas or had similar experience?

Irwin Miller (BOB member since 2002-08-16)

system · April 20, 2006, 7:36pm

Sorry I meant Application.Interactive = False

Irwin Miller (BOB member since 2002-08-16)

system · April 20, 2006, 7:49pm

Could you post your code??

dcdas (BOB member since 2002-06-17)

system · April 20, 2006, 8:44pm

This is the logfile output:
ERROR: 100 - No current report

Start of macro for: ~fc_16b84454c824461
Start of refresh of: ~fc_16b84454c824461
Start of Filer and Export

It appears as if it does not recognize the variable ‘ThisDocument’.
In an earlier release (5.x) I had to use
Dim boDoc As busobj.Document


' =========================================
'  In ThisDocument
' =========================================
    
Private Sub Document_BeforeRefresh(Cancel As Boolean)
        
    ' FOR TESTING, uncomment this next line... It suppresses any prompts
     Application.Interactive = False
    
    ' If refreshed from batch (BCA) then set default prompt values
    If Application.Interactive = False Then
        Call InitSetup           ' set up path and file names
        Call SetReportParams
    End If
End Sub

Private Sub Document_AfterRefresh()
    ' If refreshed from batch (BCA) then IN THE FUTURE create PDF files
    If Application.Interactive = False Then
        Call FilterandExport
    End If
    
    ' FOR TESTING, uncomment out this next line... It enables any prompts
     Application.Interactive = True
End Sub
        

' =========================================
' In Modules
' =========================================

Option Explicit

    Dim sPathName, sLogFile, sLogMsg As String
    Dim sDocName, sExportPath, sExportFile As String
    Dim sVariableName, sFilterCond, sDateStr, sNextValue As String
    Dim boDoc As Document		' busobj.Document
    Dim borpt As Report			' busobj.Report
    Dim boFilterVar As DocumentVariable
    Dim i, nNumChoices As Integer
    Dim myFilterChoices As Variant
    
'====================================================================
Sub InitSetup()
    On Error GoTo ErrorHandler

    ' Default pathname, filename for PDF files
    
    ' For TESTING - write to local drive
    sPathName = "c:\temp\"
    sExportPath = sPathName &amp; "BO Reports\"
       
    sLogFile = sPathName &amp; "logfile.txt"

    ' This document is the active (Open) document
    Set boDoc = ThisDocument
    ' sDocName = bodoc.FullName   contains the path and .rep
    sDocName = boDoc.Name
    
    ' This report is the active (with focus) Report
    Set borpt = ActiveReport

    Call LogMessage("************************************")
    Call LogMessage("Start of macro for: " &amp; sDocName)

    Exit Sub

ErrorHandler:
    sLogMsg = "ERROR: " &amp; Err.Number &amp; " - " &amp; Err.Description
    Call LogMessage(sLogMsg)
    Resume Next

End Sub

'====================================================================
Sub SetReportParams()

    ' This procedure is called just before the document is refreshed
    ' It is only called in non-interactive mode (Application.Interactive is False)
    ' where prompts are not displayed
    
    Dim nMonth, nYear As Integer
    Dim dStart, dEnd As Date
        
    dEnd = Date - 1                         ' Ending date is yesterday
    dStart = DateAdd("d", -24, dEnd)        ' Starting date is 24 days before
    
    boDoc.Variables.Item("1. Enter Starting Date").Value = dStart
    boDoc.Variables.Item("2. Enter Ending Date").Value = dEnd
    
    Call LogMessage("Start of refresh of " &amp; sDocName)
    Call LogMessage("Between " &amp; Format(dStart, "mmm d yyyy") &amp; " and " &amp; Format(dEnd, "mmm d yyyy"))
    
End Sub

Sub FilterandExport()

    ' This procedure is called immediately after the document is refreshed:
    
    '    For a given report variable (such as Engine Family)
    '       filter the report for each value of the variable
    '    Save (export) each filtered report as a PDF file

    ' For debugging, this shows the actual variables in the document
    ' nNumChoices = bodoc.DocumentVariables.Count
    ' For i = 1 To nNumChoices
    '   myFilterChoices = bodoc.DocumentVariables.Item(i).Name
    ' Next i
    
    Call LogMessage("Start of Filter and Export")
    
    ' Date will become part of filename
    sDateStr = Format(Date, "yyyy-mm-dd")
    
    ' First export by each Engine Family using the report variable
    sVariableName = "Engine Family(Start in Period)"
    Call ExportByFilter("EF", sVariableName)
    
    ' sExportFile = sExportPath &amp; sDocName &amp; " " &amp; sDatestr &amp; ".PDF"
    ' Call LogMessage("Saving as " &amp; sExportFile)
    ' boDoc.ExportAsPDF (sExportFile)
    
    Call LogMessage("End   of macro for: " &amp; sDocName)
    
End Sub
Sub ExportByFilter(sLevel, sVariableName)

    Dim sFile As String
    Dim botab As Report
    Dim nNextValue As Integer

    ' This procedure examines sVariableName and determines the
    ' distinct values in the doc. For each value, a filter is applied
    ' to show only that value.  The filter is applied which produces
    ' a new view of the data.  That view is then saved as a PDF file.
    
    ' sLevel is a literal that becomes part of the PDF file name.
    
    Set boFilterVar = boDoc.DocumentVariables(sVariableName)

    ' find out how many values there are for this variable
    nNumChoices = UBound(boFilterVar.Values(boUniqueValues))

    ' collect the number of choices in a variant variable
    myFilterChoices = boFilterVar.Values(boUniqueValues)

    For i = 1 To nNumChoices
        ' Get the variable value
        sNextValue = myFilterChoices(i)

        ' build filter
        sFilterCond = "=<" &amp; sVariableName &amp; "> = " &amp; sNextValue
        
        ' Refresh each report tab
        For Each botab In boDoc.Reports
            botab.AddComplexFilter boFilterVar, sFilterCond
        
            ' Set the report title to include the filter value
            boDoc.DocumentVariables.Item("Report Title").Formula = "Engine Family: " &amp; sNextValue
          
            ' Recompute the report (which applies the filter)
            botab.ForceCompute
        Next botab
        
        ' now export to desired format, using the
        ' literal sLevel and the filter value as part of the name
        ' use boDoc object with ExportAsPDF method to save all report tabs
        sExportFile = sExportPath &amp; sDocName &amp; " " &amp; sDateStr &amp; " (" &amp; sLevel &amp; "-" &amp; sNextValue &amp; ")" &amp; ".PDF"
        Call LogMessage("Saving as " &amp; sExportFile)
        boDoc.ExportAsPDF (sExportFile)
    Next i

    ' invoke a filter that is always true to undo the filter
    
    For Each botab In boDoc.Reports
        ' botab.AddComplexFilter boFilterVar, "=(1=1)"
        ' botab.AddComplexFilter boFilterVar, "=<Engine Family(Start in Period)> <> " &amp; Chr(34) &amp; "N/A" &amp; Chr(34)
         botab.AddComplexFilter boFilterVar, "=<Engine Family(Start in Period)> <> 0"
       
        boDoc.DocumentVariables.Item("Report Title").Formula = "ALL ENGINES"
        botab.ForceCompute
    Next botab
    
End Sub

Sub LogMessage(sLogMsg)
    Dim msgText As String
    Dim nFile As Integer
    
    nFile = FreeFile        ' get the next free file handle
    Open sLogFile For Append As #nFile
    msgText = Format(Now, "mm/dd/yy hh:mm:ss") &amp; " - " &amp; sLogMsg
    Print #nFile, msgText   ' using Write instead of Print added quotes to the string
    Close #nFile
    
End Sub

Irwin Miller (BOB member since 2002-08-16)

Execute macro in scheduled Deski document???

Thanks in advance for any help. Michael Cornelison

Thanks in advance for any help.
Michael Cornelison