Does anyone know of a previous post or suggestion on how to reference prompts using vba from another application?
Issue: Run BO script from Excel using inlist of values stored in cell
I am trying to fill in prompt values using a vba macro which comma delimitates numbers in Excel and concatenates them into the top most left cell (A1). I am not sure if I need to use the UserResponce function, make the prompt a variable in the report, or have the excel list be a dataprovider. I have searched through the forum but ot found a complete example.
Thanks for your help,
Chris
Code:
Public Function UpdateBOScripts()
Dim oBO As busobj.Application
Dim oDoc As busobj.Document
Dim strList As String
Set oBO = New busobj.Application
With oBO
.Visible = True
.LoginAs xxx
.Interactive = False
Set oDoc = .Documents.Open("C:\BO.rep")
With oDoc
.Variables("Prompt").Value = strList
.Refresh
.Save
.ActiveReport.ExportAsText "C:\List.xls"
.Close
End With
.Interactive = True
.Quit
End With
Set oBO = Nothing
End Function
{moderator edit … added code formatting to improve readability}
Your code looks fine. The only piece missing is filling in the strList variable, right? Instead of using an Excel cell to hold the concatenated list, some variation of the code in this utility should work.
Right, that utility is very much like one we have internally here. In fact, that post led me to the idea that this could be done. I was under the impression that the utility you referred to uses the clipboard and the user has to paste this in the prompt or if I used this in my macro I would have to send key strokes to paste the values into the prompt. I am frequently wrong though, can I just assign the prompt to the values?
I should point out that the utility here actually has a variable which contains the values. I tried to say (.Variables(1).Value = strlist) where the strList was set to this other variable. I guess I am asking if this is the correct method? I am new to vba so I am not sure…
Ok, once again I was wrong. I guess I should have tested a valid value. Thanks, my code above worked once I set the prompt value = my inlist variable in Excel.
I see by now you’ve figured it out. Yes, the utility put the results on the clipboard, but it was just a sample. Your solution to take the result to the prompt instead is the correct solution. Glad it’s working for you!
(BOB member since 2006-05-17)