VB & busobj 1

[kalkalai]

Hi,

I just wanted to automate running .rep files using Excel macros... I found some useful code on this forum to run busobj as follows

busobj.Application.CreateObject
...
set hp = busobj.Documents.Open(...rep)
hp.refresh

after refresh i am getting a prompt where i should type the start time and stop time and then press OK...

Can you help me to automate this start/stop time entrance?

thanx in advance.

 

[xlbo]

The object model is your friend. When in BOBJ, VBE, press F2 and the object model will appear. Prompts are a property of a document so you can look at the document properties where you will find that prompts are located as a document variable which can be accessed like:

Set myDoc = thisdocument
iVarCt = mydoc.Variables.Count
For j = 1 To iVarCt
 If mydoc.Variables.Item(j).Name = "Prompt Text" Then
    mydoc.Variables.Item(j).Value = "Value For Prompt"
 End If
Next j

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[kalkalai]

hi xlbo,

first of all thanks a lot it works, but...

below is some part of my code...

Set HP = Buso.Documents.Open("...rep")
Dim iVarCt As Variant
Dim j As Integer
iVarCt = HP.Variables.Count
For j = 1 To iVarCt
If HP.Variables.Item(j).Name = "2. Start time:" Then
HP.Variables.Item(j).Value = "27/07/2007 12:00:00 AM"
End If
If HP.Variables.Item(j).Name = "3. Stop time:" Then
HP.Variables.Item(j).Value = "28/07/2007 12:00:00 AM"
End If
Next j
HP.Refresh


it does not set these values directly, it still prompts me to enter the start/stop times... and requires me press OK.

How can I disable this prompt instead make it automatically use my values provided in vb code...

thanx in advance.

 

[xlbo]

Set HP = Buso.Documents.Open("...rep")
Dim iVarCt As Variant
Dim j As Integer
iVarCt = HP.Variables.Count

[b]Application.Interactive = false[/b]

For j = 1 To iVarCt
 If HP.Variables.Item(j).Name = "2. Start time:" Then
    HP.Variables.Item(j).Value = "27/07/2007 12:00:00 AM"
 End If
 If HP.Variables.Item(j).Name = "3. Stop time:" Then
    HP.Variables.Item(j).Value = "28/07/2007 12:00:00 AM"
 End If
Next j
HP.Refresh

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[kalkalai]

Thanx xlbo,

below is my code, it still doesn't use my start/stop times...

Dim Buso As busobj.Application
Dim DP As busobj.DataProvider
Dim HP As busobj.Document

Buso = CreateObject("BusinessObjects.Application")
Buso.Visible = True
Call Buso.LoginAs("****", "****")

AppActivate("BusinessObjects")

On Error GoTo ErrorHandler
HP = Buso.Documents.Open("C:\Traffic.rep")

Buso.Interactive = False
HP.Variables.Add("2. Start time:").Value = "01/08/2007 00:00:00"
HP.Variables.Add("3. Stop time:").Value = "02/08/2007 00:00:00"
HP.Refresh()

HP.Save()
HP.ExportSheetsAsHtml("C:\TestingBO", , , , , , , , , , False, 2)
HP.Close()

Buso.Interactive = True
Buso.Quit()

ErrorHandler:
Resume Next



Plz, can you help me?... Thanx in advance.

 

[xlbo]

what does it do ?

You are adding new variables to the report but presumably the queries are set up with parameters already.. If not, then you would need to add the parameters to the query before they will take effect. If they are set up already then adding new ones will not add values to the old ones....

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[kalkalai]

xlbo,

normally when i would press refresh after opening a .rep file it would have there values for start/stop times already...

when i am running my code it is not using the values that i set in the vb code, but rather it uses the ones that were in the prompt already... what would you suggest?

 

[kalkalai]

one more thing xlbo,

i am not using vbe on BO... i am using VS2005 VB win app.

 

[xlbo]

as per my last post - the code you are using is ADDING new variables - you need to put the values in variables that are ALREADY THERE

How do you expect this:

HP.Variables.Add("2. Start time:").Value = "01/08/2007 00:00:00"

To put a value in an EXISTING parameter ?

HP.Variables.("2. Start time:").Value = "01/08/2007 00:00:00"

may work but you may have to enumerate through the variables collection and checjk the NAME of the variable before assigning the value. All this was in my 1st post

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[kalkalai]

I changed to this... it still gives for the period in the prompt...

Buso.Interactive = False

Dim j As Integer

For j = 1 To HP.Variables.Count
If HP.Variables.Item(j).Name = "2. Start time:" Then
HP.Variables.Item(j).Value = "01/08/2007 00:00:00"
End If
If HP.Variables.Item(j).Name = "3. Stop time:" Then
HP.Variables.Item(j).Value = "02/08/2007 00:00:00"
End If
Next j


HP.Refresh()

 

[xlbo]

I really don;t kinow if there is any difference controlling BOBJ from VS than from internally. All I can tell you is that this code works in BOBJ V5.1.8

Set mydoc = myDocs.Open(fPath & docName)
    
    boApp.Interactive = False
    
    iVarCt = mydoc.Variables.Count
    
    For j = 1 To iVarCt
        If mydoc.Variables.Item(j).Name = "Select Area" Then
                mydoc.Variables.Item(j).Value = BRMArray(i)
        End If
    Next j
                
    mydoc.Refresh

This has been working for years. Not sure about stuffing dates in though - have you tried interrogating the parameters to see what IS in there? maybe some error trapping to see if it is trying and failing to enter the date you are passing in there...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[kalkalai]

xlbo really thanx a lot... that you went with this topic till the end... everything worked out!
[problem was the '2. Start time' had to be '2. Start Time:']

last thing that i would ask from you, can you suggest me where preferably on internet i can find material on programming vb+busobj... if you can suggest any book...

thanx once more again...

 

[xlbo]

I have not found any particularly useful books to be honest. Your best bet is to learn the object model which can be found by pressing F2 whilst in the VBE. From here you can learn which objects have which propertiers and how to access them by using the right click / help link on any item in the object model

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[kalkalai]

once more again thanx a lot xlbo!!!