topic RE: Excel VBO - Open Workbook with custom parameters in Digital Exchange

Excel VBO - Open Workbook with custom parameters

AP.Philippa — Fri, 14 May 2021 13:07:00 GMT

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 a compiler error..

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 provide custom parameters?

Thanks in advance!

------------------------------
Arthur Philippa
RPA Developer
Port of Rotterdam
Europe/Amsterdam
------------------------------

RE: Excel VBO - Open Workbook with custom parameters

EmersonF — Fri, 14 May 2021 13:34:00 GMT

Hi @AP.Philippa, i hope you're well, i can try it?

This works for me, if this works for you too, please mark it as helpful: D

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, True})
    Invoke(wb, "Activate")
    Return wb.Name
End Function)

I did this using the expression syntax as parameter:

expression.Open (FileName, UpdateLinks, ReadOnly, Format, Password, WriteResPassword, IgnoreReadOnlyRecommended, Origin, Delimiter, Editable, Notify, Converter, AddToMru, Local, CorruptLoad)

Where UpdateLink comes after the filename, if I wanted to add some other parameter like readOnly = false, I believe it would be just following the logic
{filename, True, False}

This works for me, if this works for you too, please mark it as helpful: D
------------------------------
Emerson Ferreira
Sr Business Analyst
Avanade Brasil
+55 (081) 98886-9544
------------------------------

RE: Excel VBO - Open Workbook with custom parameters

EmersonF — Fri, 14 May 2021 14:04:00 GMT

@AP.Philippa, Let me know if this works for you!

------------------------------
Emerson Ferreira
Sr Business Analyst
Avanade Brasil
+55 (081) 98886-9544
------------------------------

RE: Excel VBO - Open Workbook with custom parameters

AP.Philippa — Fri, 14 May 2021 14:05:00 GMT

Hi Emerson,

Thank you very much for your suggestion. Just tried it and it also works for me!

I do have another question. Which parameters do I need to include in the code stage when I for example want to make use of the parameter "Local"? Do I only have to reference the two params {filename, True} or do I have to reference all the parameters that are between the two (also include: UpdateLinks, ReadOnly, Format, etc.)?). When having to reference all parameters, do you know of workaround as this seems far from efficient.

Thanks in advance!

------------------------------
Arthur Philippa
RPA Developer
Port of Rotterdam
Europe/Amsterdam
------------------------------

RE: Excel VBO - Open Workbook with custom parameters

EmersonF — Fri, 14 May 2021 14:24:00 GMT

I believe that you will have to set the other parameters to false, I don't have an alternative solution at the moment 😕
I'm sorry

------------------------------
Emerson Ferreira
Sr Business Analyst
Avanade Brasil
+55 (081) 98886-9544
------------------------------