Running BO 5.1.8 Reports VIA VBA Code

[elmarko123]

Hello all,

I'm writing a macro in excel VBA that runs a number of business objects reports, refreshes the data based off a reference on a spreadsheet then loops through each report saving them down.

I can get the model to effectivly open the report, refresh the report, then save the report, but i cannot get the variables to pass to the popup boxes.

Here is my code (apologies if its messy, im self taught)

****************************************

Sub GetBOData() 

Dim Buso As busobj.Application 
Dim DP As busobj.DataProvider 
Dim HT As busobj.Report 
Dim HP As busobj.Document 
Dim i As Integer 'these two are position counters 
Dim z As Integer 
Dim w As Integer 

Application.Interactive = False 
Application.DisplayAlerts = False 
Application.ScreenUpdating = False 

i = 2 'this starts at two due to the headers on the spreadsheet 
z = 1 'X - this is an incremental counter to force business objects to only open once, as oppose to once for each query 

Do While Sheets("Control").Range("A" & i).Value >= 1 'basic loop beggining to check there are variables to process. 

If Sheets("Control").Range("A" & i).Value >= 1 Then 

Sheets(1).Range("h1").Value = Environ("username") 'this pulls the network ID from the environment & stores it 

j = Sheets(1).Range("A" & i).Value 'the below code is to turn the network ID into lower case & for other variables. 
h = Sheets(1).Range("i1").Value 
k = Sheets(1).Range("B" & i).Value 
m = Sheets(1).Range("C" & i).Value 

If z = 1 Then 

Set Buso = CreateObject("BusinessObjects.application") 

Buso.LoginAs h, h 
Buso.Visible = True 
AppActivate "BusinessObjects" 

Else 

End If 

Buso.Documents.Open (k & j & ".rep") 'this obtains the first in the list of BO reports that are required to be opended. 
Buso.Application.Interactive = False 

Here is my problem :/  
Buso.Variables("1.Select start date").Value = Sheets(1).Range("D" & i) 
Buso.Variables("2.Select end date").Value = Sheets(1).Range("E" & i) 


Buso.ActiveDocument.Refresh 

If Sheets(1).Range("E8").Value = "" Then 'this checks the value in the save section, if none is select then it defaults to desktop (again as per above references 
'the network logon ID as part of the file save path. 
u = "C:\Documents and Settings\" & h & "\Desktop\" 
Else 
u = Sheets(1).Range("h8").Value 'If the value isnt blank, then it uses the value that the user has inputted. 
End If 

If m = "Text" Then 'this part chooses which format to save the file as in business objects, driven by the user input on the control sheet. 
Buso.ActiveReport.ExportAsText (u & j & ".txt") 
ElseIf m = "PDF" Then 
Buso.ActiveReport.ExportAsPDF (u & j & ".PDF") 
End If 

Buso.Interactive = True 
'Buso.ActiveDocument.Save 'this is commented out currently to unsure the model ajusts no data. 
Buso.ActiveDocument.Close 'this closes the document after the report is produced. 

i = i + 1 'this increments the position of "i" by one each time, allowing the loop to read each value in A & B & C in sequence. 
z = z + 1 'this increments the section that opens business objects, once this value has passed 1 (IE, BO is open), it will no longer open business objects. 

Else 

End If 

Loop ' this closes the loop statment. 

Buso.Quit 'this closes business objects. 

Application.Interactive = True 'this small section re-enables the user to modify the application if required. 
Application.DisplayAlerts = True 
Application.ScreenUpdating = True 

End Sub

****************************************

Any advice of fixing the problem of passing the variables would be greatly appriciated, as then ill be able to automate my 20+ business objects querys to a singular button click on my excel spreadsheet.

NOTE : This code currently runs through without any errors, but the values do not update for the query (on the date ranges from the popup)

Many thanks,

M

[elmarko123]

Ok, ive found out the solution...., this may be usefull to others.

****************************************************************

    Set mydoc = Buso.Documents.Open(k & j & ".rep")

'This section here is to replace the line to open the file, this stores the filename for future 'reference later on.

    Buso.Application.Interactive = False

    c = mydoc.Variables.Count this stores the value of the amount of variables for later refence.
    
    For s = 1 To c
    
     If mydoc.Variables.Item(s).Name = "1. Select start date" Then
                mydoc.Variables.Item(s).Value = Sheets(1).Range("D" & i).Value
         
     End If
                If mydoc.Variables.Item(s).Name = "2. Select end date" Then
                mydoc.Variables.Item(s).Value = Sheets(1).Range("E" & i).Value
           End If
           
    Next s
    
Buso.ActiveDocument.Refresh

********************************************