topic RE: Convert Excel macro to Blue Prism code stage in Product Forum

Convert Excel macro to Blue Prism code stage

GAVIN.RUDLING — Tue, 28 Sep 2021 06:47:00 GMT

Good day team.
I'm wondering if it is possible to take code from an Excel macro and convert it within a code stage within Blue Prism. If possible, I would like to learn how to go about this.

Kind Regards
Gavin

------------------------------
Gavin Rudling
Digital Consultant & Developer
Cog3nt
Africa/Johannesburg
0813906789
------------------------------

RE: Convert Excel macro to Blue Prism code stage

sumire — Tue, 28 Sep 2021 09:43:00 GMT

Hi,

In other words, you want to "convert VBA to VB.NET" ?

https://docs.microsoft.com/en-us/previous-versions/office/developer/office-2003/aa192490(v=office.11)

------------------------------
Mitsuko
Asia/Tokyo
------------------------------

RE: Convert Excel macro to Blue Prism code stage

John__Carter — Wed, 29 Sep 2021 11:12:00 GMT

Hi Gavin as Mitsuko points out, it's essentially a case of rewriting the VBA macro code as equivalent .Net code. VBA and VB.Net are similar but not the same, so unfortunately it's not just a case of cut&paste.

A good way to start is to record your macro and study the resulting VBA code. The take a look at code stages in the Excel VBO and compare the two. And if you record a macro that does a similar action to one of the VBO pages, eg read from a cell, activate a sheet etc, then you'll be able to make a direct comparison.

------------------------------
John Carter
Professional Services
Blue Prism
------------------------------

RE: Convert Excel macro to Blue Prism code stage

GAVIN.RUDLING — Wed, 29 Sep 2021 14:52:00 GMT

Hi John / Mitsuko

Thanks for the speedy response.
Yeah, understood. I got the basic idea. I wrote a VBA module to do some web scraping and data extraction from a secure web Portal.
This required importing libraries do do this. In .Net I have similar code. This requires different but similar libraries.
However when trying to break this down and write code stages, Blue Prism throws errors on these libraries and reports that these are not recognized objects. The error is thrown on the 'webBrowser' object in this case.

I'm guessing it's more likely a syntax issue
I had to import an HTML library for the Excel module to work which I don't see in the .Net, but as you say the code is very similar otherwise.

Regards
Gavin
Example:

Imports System.IO
Imports System.Net

Public Class Form1

    Dim WithEvents myWebBrowser As New WebBrowser
    Dim WithEvents myWebBrowser2 As New WebBrowser

------------------------------
Gavin Rudling
Digital Consultant & Developer
Cog3nt
Africa/Johannesburg
0813906789
------------------------------

RE: Convert Excel macro to Blue Prism code stage

John__Carter — Thu, 30 Sep 2021 09:05:00 GMT

Hi Gavin - it's not quite as easy as that. Instead of writing an Imports statement in the code, you need to make an entry in the Code Options of the VBO. Go to the Initialise page and double click the 'nameplate' element to open the object properties. In there select the Code Options tab and see that there is a section for references and a section for imports. Take a look in the Utility - JSON object to get the idea:

Note also that there is a Global Code section where you can classes and functions etc.

------------------------------
John Carter
Professional Services
Blue Prism
------------------------------

RE: Convert Excel macro to Blue Prism code stage

GAVIN.RUDLING — Thu, 30 Sep 2021 09:25:00 GMT

Thanks John

That is one of the reasons for this query.
I have added the namespace Imports, as I thought, but my lack of knowledge / experience with this functionality of Blue Prism, seems to be catching up with this old brain.
This is a copy out of Visual Studio, where the code seems to be working.

------------------------------
Gavin Rudling
Digital Consultant & Developer
Cog3nt
Africa/Johannesburg
0813906789
------------------------------

RE: Convert Excel macro to Blue Prism code stage

John__Carter — Thu, 30 Sep 2021 15:19:00 GMT

Hi you first need to have your library referenced in the upper section, then you can add a namespace import, or alternatively use a fully qualified class name in your code. Not sure which library you want but there is WebBrowser class in System.Windows.Forms

------------------------------
John Carter
Professional Services
Blue Prism
------------------------------

RE: Convert Excel macro to Blue Prism code stage

GAVIN.RUDLING — Fri, 01 Oct 2021 06:05:00 GMT

Thanks John, I think I found the correct Dll file "System.Windows.Forms.dll".
At least I can move on to the next step.
Much appreciated.

Regards
Gavin

------------------------------
Gavin Rudling
Digital Consultant & Developer
Cog3nt
Africa/Johannesburg
0813906789
------------------------------