Excel VBO - How to add parameters to Open Workbook action?

[AP.Philippa]

Dear BP community,

We recently have updated our Excel VBO to the most recent version available on the Blue Prism DX. A great new feature in this version is that the Open Workbook action now appears to have a timeout functionality build into the code (we hope that this will decrease the chance on warning status)!

We unfortunately seem to be unable to modify the code stage (including the new timeout functionality) in a way that it can use custom parameters, such as password or the updatelinks flag. For example adding the parameter “UpdateLinks:=0” to the code stage, results in compiler errors..

Old code example (UpdateLinks):

Dim wb as Object = GetInstance(handle).Workbooks.Open(Filename:=filename, UpdateLinks:=0)

name = wb.Name

wb.Activate()

New code attempt:

name = ExecWithTimeout(Timeout, "Open Workbook",

Function()

    Dim instance = GetInstance(handle)

    Dim workbooks As object = GetProperty(instance, "Workbooks")

    Dim wb As Object = Invoke(workbooks, "Open", Filename:=filename, UpdateLinks:=0)

    Invoke(wb, "Activate")

    Return wb.Name

End Function)

Could someone please advise us on how we can modify the Open Workbook code stage in a way that we can use custom parameters but also make use of the timeout functionality?

Many thanks in advance!

[EmersonF]

Eu fiz assim e funciona bem para mim, você pode testá-lo?
Se isso ajuda você a marcá-lo como
útil

name = ExecWithTimeout(Timeout, "Open Workbook",
Function()
    Dim instance = GetInstance(handle)
    Dim workbooks As object = GetProperty(instance, "Workbooks")
    Dim wb As Object = Invoke(workbooks, "Open", filename)
    Invoke(wb, "Activate")
    Return wb.Name
End Function)​

[michaeloneil]

Hi Arthur 

From the look of your code it sounds like what you are wanting to do is prevent the update links alert message popping up when you open a file is that right? Usually the open workbook will timeout if this appears unless you select continue in the window. You dont set the updatelinks to be no on this but rather turn off alert messages when opening the file, the best way to do this is to create a new action in the excel vbo and set it as a reference page immediately after the open workbook code stage runs. In this new action your code will be GetInstance(handle).DisplayAlerts = alerts_enabled and your open workbook will look like this 

Alert action

If you do need to update the links the code would actually be
wb.UpdateLink(Name:=wb.LinkSources)
or if you need to refresh links it would be
wb.RefreshAll

Hope this helps 🙂