Code Stage - PageSetup complier error

[YunaLee]

Hi all!

Following is an excel macro code which is recorded in excel, placed in code stage.
and there are compiler error when we check code.
I'm very new to VB code so if anyone could get me clue to get closer to the resolution, I would really appreciate it.
(Is there possibility that PageSetup related dll is neccesary?) 


Inputs


Outputs



Code
--------------

Dim excel as Object

Dim ws as Object = GetWorksheet(handle, workbookname, worksheetname)

Dim wb as Object = GetWorkbook(handle, workbookname)

Dim xlPrintSheetEnd = 1

Dim xlPaperA4 = 9

ws.Activate()

wb.Activate()

excel = wb.Application

    excel.PrintCommunication = False

    With ws.PageSetup

        .PrintTitleRows = ""

        .PrintTitleColumns = ""

    End With

    excel.PrintCommunication = True

    ws.PageSetup.PrintArea = ""

    excel.PrintCommunication = False

    With ws.PageSetup

        .LeftHeader = ""

        .CenterHeader = ""

        .RightHeader = ""

        .LeftFooter = ""

        .CenterFooter = ""

        .RightFooter = ""

        .LeftMargin = excel.InchesToPoints(0.7)

        .RightMargin = excel.InchesToPoints(0.7)

        .TopMargin = excel.InchesToPoints(0.75)

        .BottomMargin = excel.InchesToPoints(0.75)

        .HeaderMargin = excel.InchesToPoints(0.3)

        .FooterMargin = excel.InchesToPoints(0.3)

        .PrintHeadings = False

        .PrintGridlines = False

        .PrintComments = xlPrintSheetEnd

        .PrintQuality = 600

        .CenterHorizontally = False

        .CenterVertically = False

        .Orientation = xlPortrait

        .Draft = False

        .PaperSize = xlPaperA4

        .FirstPageNumber = xlAutomatic

        .Order = xlDownThenOver

        .BlackAndWhite = False

        .Zoom = False

        .FitToPagesWide = 2

        .FitToPagesTall = 1

        .PrintErrors = xlPrintErrorsDisplayed

        .OddAndEvenPagesHeaderFooter = False

        .DifferentFirstPageHeaderFooter = False

        .ScaleWithDocHeaderFooter = True

        .AlignMarginsHeaderFooter = True

        .EvenPage.LeftHeader.Text = ""

        .EvenPage.CenterHeader.Text = ""

        .EvenPage.RightHeader.Text = ""

        .EvenPage.LeftFooter.Text = ""

        .EvenPage.CenterFooter.Text = ""

        .EvenPage.RightFooter.Text = ""

        .FirstPage.LeftHeader.Text = ""

        .FirstPage.CenterHeader.Text = ""

        .FirstPage.RightHeader.Text = ""

        .FirstPage.LeftFooter.Text = ""

        .FirstPage.CenterFooter.Text = ""

        .FirstPage.RightFooter.Text = ""

    End With 

-

Validation


Warm Regards
Yuna

------------------------------
Yuna Lee
Solution Consultant
Blue Prism Korea
------------------------------

[Mukeshh_k]

Hi @YunaLee - You are missing the Dll and Namespaces - while recording the macro - The Macro has used few of Excel libraries - you would need to find those dll & references and get it added in your Initialise stage of the code, you can download these dlls externally and add it into "C:\Program Files\Blue Prism Limited\Blue Prism Automate\" along with other dlls  : 

For more on these two dll and references which your code is using- check Microsoft documentation below :
Microsoft.Office.Interop.Excel Namespace

Microsoft.Office.Tools.Excel Namespace

Download link for Microsoft.Office.Interop.Excel : https://www.dll-files.com/microsoft.office.interop.excel.dll.html
Download link for Microsoft.Office.Tools.Excel : https://www.dllme.com/dll/files/microsoft_office_tools_excel

Adding it to Initialise Stage:


------------------------------
Kindly up vote this as "Best Answer" if it adds value or resolves your query in anyway possible, happy to help.

Regards,

Mukesh Kumar - Senior Automation Developer

NHS, England, United Kingdom, GB
------------------------------

[michaeloneil · Accepted solution]

Hi @YunaLee 

I dont think you are missing any dll's as I'm assuming you have created this new action within the existing excel vbo or you have copied over all the main code from the blue prism vbo if you have created a new one. The errors are mainly due to enumeration being required for vb.net, where vba uses xl vb.net uses enums e.g.  .PrintComments = xlPrintSheetEnd  should be changed to  .PrintComments = 1

Also the workbook should be declared above the worksheet, the changes are noted below.

Dim wb as Object = GetWorkbook(handle, workbookname)
Dim ws as Object = GetWorksheet(handle, workbookname, worksheetname)
Existing	Change to
excel = wb.Application	excel = ws.Application
xlPrintSheetEnd	1
xlPortrait	1
xlPaperA4	9
xlAutomatic	-4105
xlDownThenOver	1
xlPrintErrorsDisplayed	0

------------------------------
Michael ONeil
Technical Lead developer
NTTData
Europe/London
------------------------------

[presales-support]

Hi Michael.

Thank you for the reply! xl* related errors are gone.
I have changed the xl*s to numbers as your guide.

But I still get the following erros
Could you advise me on this error?

Best Regards
Yuna

------------------------------
Presales Support
Presales Support
Blue Prism
Europe/London
------------------------------

[michaeloneil]

Hi

This looks like you could be missing some of the global code for excel actions, I'm assuming you have created a new object for this action and not added an action to the existing excel out the box object? If it is a new object then its likely you could resolve this by adding in all required global code. Open the blueprism standard excel object and on the initialise page double click the  name and comments section (shown in the screenshot below). In the Global code tab select all the code and copy it to the global code on your object. After you have done that go to the code options tab and make sure your object contains all the same dll names as the blue prism object. If there are any missing just add a row and add the one you are missing. Hope this helps 🙂

------------------------------
Michael ONeil
Technical Lead developer
NTTData
Europe/London
------------------------------

[YunaLee]

Hi Michael,

Oops, my bad, global code was not defined.
It works fine now on excel vbo. Thank you for your help!

+ Would that be any way to import certain namespace that defines all the excel-related emuneration? 
(Our customer is recording macro on excel and wants to apply the code in code stage but if they have to assign all missing enumeration whenever they create new code, it may be a hustle for them.

I have added office.interop.excel and following error occurs.. 

'DataTable' is an ambiguous reference between 'System.Data.DataTable' and 'Microsoft.Office.Interop.Excel.DataTable'

Warm Regards
Yuna

------------------------------
Yuna Lee
Solution Consultant
Blue Prism Korea
------------------------------

[Mukeshh_k]

Hi - you need to add System.Data.DataTable as the reference in initialise as both of the libraries Interop and System.Data have Datatable - you need to specify the compiler to use System.Data.DataTable.

------------------------------
Kindly up vote this as "Best Answer" if it adds value or resolves your query in anyway possible, happy to help.

Regards,

Mukesh Kumar - Senior Automation Developer

NHS, England, United Kingdom, GB
------------------------------

[michaeloneil]

Hi @Yuna Lee

unfortunately I dont think there is anything that will convert the values to enumeration for you in the vbo. You could create a document with a list of reference data but this would likely be time consuming to do. Its usually fairly easy to find the enum for anything you need and not all vba code will require it, its only the values that begin with xl that need changed and if you just type it into google (e.g. xlPrintSheetEnd enumeration) it usually comes up straight away with the direct link to microsofts website showing the value you need. I've copied the link below you might find usual for future reference if you need them. I use this regularly to find the enums you just need to select the one you need and the link with direct to the page with the required info. Hope this helps 🙂

Enumerations (Excel) | Microsoft Learn

------------------------------
Michael ONeil
Technical Lead developer
NTTData
Europe/London
------------------------------

[YunaLee]

Thank you for your help, Mukesh 🙂

------------------------------
Yuna Lee
Solution Consultant
Blue Prism Korea
------------------------------