cancel
Showing results for 
Search instead for 
Did you mean: 

Convert Excel macro to Blue Prism code stage

GAVIN.RUDLING
Level 7
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
------------------------------
7 REPLIES 7

sumire
Level 9

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
------------------------------
------------------------------
Mitsuko
Asia/Tokyo
------------------------------

John__Carter
Staff
Staff
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
------------------------------

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
------------------------------

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:

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

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

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.


25763.png
25764.png

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

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

25766.png

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

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
------------------------------