Blue Prism Product

Hi, I have this code : Dim ws, excel,sheet As Object ws = GetWorksheet(Handle, Workbook, Worksheet) ws.Activate() excel = ws.Application sheet = excel.ActiveSheet ws.Range([range]).RemoveDuplicates(Columns :=Array(1,1), Header:=[Header]) input :range - string (ex:"B:B") input :Header- flag(ex:"True") input : Columns - ???   This code implement in Excel vbo RemoveDuplicates function of Excel. But I need help because I do not understand how to pass the argument Array (x, x) in bluePrism!? How should I declare it? Knowing that this argument Array must be dynamic. If I put the function in a vba excel, it runs correctly. Here I have the following error at compile: 'Array' is a type and can not be used as an expression. if I replace with a "string", it does not work   Bonus question (still in Excel VBO): how to do when you have a function that can take optional arguments to pass empty values? for example in the function:         ws.Range([range]).TextToColumns(Destination :=destinationRange, _                                 DataType :=[DataType], _                                 TextQualifier :=[TextQualifier], _                                 ConsecutiveDelimiter :=[ConsecutiveDelimiter], _                                 Tab :=[Tab], _                                 Semicolon :=[Semicolon], _                                 Comma :=[Comma], _                                 Space :=[Space], _                                 Other :=[Other], _                                 OtherChar :=[OtherChar], _                                 FieldInfo :=[FieldInfo], _                                 TrailingMinusNumbers :=[TrailingMinusNumbers]) Can become: .TextToColumns( "A:A", 2, 4, , , , , True, , , , , , ) I dont find the way in bluePrism to pass him the null arguments! An idea ?

BP code stages use .Net code, not VBA. The 2 languages are similar but not identical and you can't paste a macro into BP and expect it to work, you have to write .Net code. The VBA can act as a good skeleton, but it will need to be adjusted. Exactly how you do that is too big a subject for a forum.

Create an integer matrix 

Dim matrix = New Integer(6, 1) {{1, 1}, {2, 1}, {3, 9}, {4, 1}, {5, 1}, {6, 1}, {7, 1}}

Then you could use this as pararemter for FieldInfo

FieldInfo:=matrix

Full code

Dim wb, ws As Object
Dim excel, sheet, range As Object
Dim matrix = New Integer(6, 1) {{1, 1}, {2, 1}, {3, 9}, {4, 1}, {5, 1}, {6, 1}, {7, 1}}

Try

wb = GetWorkbook(Handle, Workbook)
ws = GetWorksheet(Handle, Workbook, Worksheet)

wb.Activate()
ws.Activate()
excel = ws.Application

excel.Selection.TextToColumns (Destination:=excel.Range(Cell_Reference), DataType:=1, _
TextQualifier:=-4142, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:=Delimiter, FieldInfo:=matrix, TrailingMinusNumbers:=True)

Success = True

Catch e As Exception
Success = False
Message = e.Message
Finally
wb = Nothing
ws = Nothing
excel = Nothing
sheet = Nothing
range = Nothing
End Try

------------------------------
Miguel Carrillo
------------------------------

Original Message:
Sent: 05-24-2018 16:19
From: Bordessoulles Vincent
Subject: Param in function vba

Hi,

I have this code :

Dim ws, excel,sheet As Object

ws = GetWorksheet(Handle, Workbook, Worksheet)
ws.Activate()
excel = ws.Application
sheet = excel.ActiveSheet

ws.Range([range]).RemoveDuplicates(Columns :=Array(1,1), Header:=[Header])

input :range - string (ex:"B:B")

input :Header- flag(ex:"True")

input : Columns - ???

 

This code implement in Excel vbo RemoveDuplicates function of Excel.

But I need help because I do not understand how to pass the argument Array (x, x) in bluePrism!? How should I declare it? Knowing that this argument Array must be dynamic. If I put the function in a vba excel, it runs correctly. Here I have the following error at compile: 'Array' is a type and can not be used as an expression. if I replace with a "string", it does not work

 

Bonus question (still in Excel VBO): how to do when you have a function that can take optional arguments to pass empty values? for example in the function:

        ws.Range([range]).TextToColumns(Destination :=destinationRange, _

                                DataType :=[DataType], _
                                TextQualifier :=[TextQualifier], _
                                ConsecutiveDelimiter :=[ConsecutiveDelimiter], _
                                Tab :=[Tab], _
                                Semicolon :=[Semicolon], _
                                Comma :=[Comma], _
                                Space :=[Space], _
                                Other :=[Other], _
                                OtherChar :=[OtherChar], _
                                FieldInfo :=[FieldInfo], _
                                TrailingMinusNumbers :=[TrailingMinusNumbers])

Can become: .TextToColumns( "A:A", 2, 4, , , , , True, , , , , , )

I dont find the way in bluePrism to pass him the null arguments! An idea ?

That would be Type.Missing in .Net, although better use named arguments like Miguel suggested.

sourcerange.TextToColumns(Type.Missing,1,1,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,{1, 1},Type.Missing,Type.Missing)

------------------------------
Andrey Kudinov
Project Manager
MobileTelesystems PJSC
Europe/Moscow
------------------------------

Original Message:
Sent: 05-24-2018 16:19
From: Bordessoulles Vincent
Subject: Param in function vba

Hi,

I have this code :

Dim ws, excel,sheet As Object

ws = GetWorksheet(Handle, Workbook, Worksheet)
ws.Activate()
excel = ws.Application
sheet = excel.ActiveSheet

ws.Range([range]).RemoveDuplicates(Columns :=Array(1,1), Header:=[Header])

input :range - string (ex:"B:B")

input :Header- flag(ex:"True")

input : Columns - ???

 

This code implement in Excel vbo RemoveDuplicates function of Excel.

But I need help because I do not understand how to pass the argument Array (x, x) in bluePrism!? How should I declare it? Knowing that this argument Array must be dynamic. If I put the function in a vba excel, it runs correctly. Here I have the following error at compile: 'Array' is a type and can not be used as an expression. if I replace with a "string", it does not work

 

Bonus question (still in Excel VBO): how to do when you have a function that can take optional arguments to pass empty values? for example in the function:

        ws.Range([range]).TextToColumns(Destination :=destinationRange, _

                                DataType :=[DataType], _
                                TextQualifier :=[TextQualifier], _
                                ConsecutiveDelimiter :=[ConsecutiveDelimiter], _
                                Tab :=[Tab], _
                                Semicolon :=[Semicolon], _
                                Comma :=[Comma], _
                                Space :=[Space], _
                                Other :=[Other], _
                                OtherChar :=[OtherChar], _
                                FieldInfo :=[FieldInfo], _
                                TrailingMinusNumbers :=[TrailingMinusNumbers])

Can become: .TextToColumns( "A:A", 2, 4, , , , , True, , , , , , )

I dont find the way in bluePrism to pass him the null arguments! An idea ?