topic RE: VBA Macro in Code stage in Product Forum

VBA Macro in Code stage

AsitabhaDeb — Thu, 08 Oct 2020 15:36:00 GMT

Hello,

I want to apply the macro in one excel file (.xlsx) without saving the macro because client does not want file extension with.xlsm.
Can I write VBA code directly in BP code stage?
Will it be possible if I copy VBA code after run the macro and paste in the BP code stage. will that work? but I dont want to save the macro.
Or any other way which will be easy.

My Requirement:
I need to compare two excel (File A & File B)
Now need to Copy record from File A and search that record in File B then filter that record and update certain % against that record.
Again move to File A copy data and search that in File B, filter the data and update the Certain % against the data and so on.

IS it possible without coding.

Please help asap.

BR/Deb

------------------------------
Asitabha Deb
------------------------------

RE: VBA Macro in Code stage

NicholasZejdlik — Fri, 09 Oct 2020 17:22:00 GMT

Can I write VBA code directly in BP code stage?

Mostly, sans some syntax differences. The MS Excel VBO object makes use of the Excel interop, which allows you to perform the same actions that you would set up within a macro. You could take those macro actions and have them run via a Blue Prism code stage.

I see a couple of different approaches you could follow. If all you're doing is searching for a record and updating a cell, the existing actions in the MS Excel VBO would enable you to perform the steps necessary for that. You could also save the resulting Excel file as an XLSX instead of XLSM, which would remove the macro from the code as your end client is requesting. Alternatively, you can code the macro in a Blue Prism code stage.

Personally, what I typically do with Excel-related automations is to use the existing actions as much as possible. If I need functionality that doesn't already exist in the VBO, I add new actions, keeping it as generic as possible so that I can reuse it later. In other words, instead of coding your entire macro in a single code stage, code the specific bits of functionality that don't exist otherwise. Then you'll be able to run the whole thing from Blue Prism without having to worry about the macro in Excel.

------------------------------
Nicholas Zejdlik
RPA Developer
------------------------------

RE: VBA Macro in Code stage

AsitabhaDeb — Sat, 10 Oct 2020 02:00:00 GMT

Thanks Nicolas for your suggestion.

My question is over here "Can I copy the Macro in excel and paste in BP code stage without saving the Macro in Excel"?. If yes, then can you please help me with a reference Macro code for "Opening a Workbook which in saved in Desktop".
Also please guide me with "Which .DLL file is require to enter in initialize stage for run the Macro.". I am already running SAP Scripting in the process and I would like to add VBA Script in that process as well.

Thanks,
BR/Deb

------------------------------
Asitabha Deb
------------------------------

RE: VBA Macro in Code stage

NileshJadhavBP — Mon, 12 Oct 2020 20:06:00 GMT

Hi Asitabha,

You cannot copy and paste direct macro code in blue prism code stage.
1.In VBA you can use "let" Or "set" to set any object but if you do copy paste blue prism never permit use let or set.
2.Set is very important in VBA.using this word you can set as file,range,sheet,application or object
3.Blue prism is never recognize very basic vba words and commands like "createobject".
4.You have to know about dll and same dll you should use in vba so you can just mention that dll name along with path ( use path if required )
5.you have to use namespace in Blueprism which you can find on google.

I hope it help.

Thanks
Nilesh

------------------------------
Nilesh Jadhav
Senior RPA Specialist
------------------------------