topic RE: How to use Field Info in Text to Columns Code stage in Product Forum

How to use Field Info in Text to Columns Code stage

SasikanthMachav — Wed, 02 Mar 2022 15:32:00 GMT

Hi Everyone,

My issue : Field Info(Array)
I need to convert text or custom column to date column using text to columns code stage.

I am unable to declare the array properly and use it because of which; it is throwing error.

Error : parameter incorrect(invalid_arg)

I am Using the below Code :

____________________________________________________________________________________
Dim wb, ws, sheet, excel, range As Object
Dim arr1 = New Object(0, 1) {{1, 4}}

Try

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

wb.Activate()
ws.Activate()
excel = ws.Application
sheet = excel.ActiveSheet
range = sheet.Range(Reference)

excel.DisplayAlerts=false

range.Select()

range.TextToColumns(Destination:=Range(DestinationCell), DataType:=1, _
TextQualifier:=1, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=arr1, TrailingMinusNumbers:=True

Success = True

Catch e As Exception
Success = False
Message = e.Message

Finally
wb = Nothing
ws = Nothing
excel = Nothing
End Try

__________________________________________________________________________________

Can anyone please help me fix it!!!!

------------------------------
Sasikanth Machavarapu
------------------------------

RE: How to use Field Info in Text to Columns Code stage

ewilson — Wed, 02 Mar 2022 20:58:00 GMT

@SasikanthMachav,

So you have a single column of data, correct? If so, change your array declaration to something like this:

Dim arr1(,) As Integer = {{1, 4}}

Cheers,

------------------------------
Eric Wilson
Director, Integrations and Enablement
Blue Prism Digital Exchange
------------------------------

RE: How to use Field Info in Text to Columns Code stage

michaeloneil — Wed, 02 Mar 2022 22:52:00 GMT

Hi

I think Eric is correct, I built a similar action but I made teh declaration Dim arr1 = New Integer (3, 1) {{1, 1}, {2, 1}, {3, 1}, {4, 1}}

Hope this helps

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

RE: How to use Field Info in Text to Columns Code stage

SasikanthMachav — Fri, 04 Mar 2022 13:06:00 GMT

Hi Eric,

I tried the way you suggested and declared the array, but seems its not working . Its throwing the same error :Parameter incorrect(INVALID_ARG)

Can you think of anything else?????

------------------------------
Sasikanth Machavarapu
------------------------------

RE: How to use Field Info in Text to Columns Code stage

SasikanthMachav — Fri, 04 Mar 2022 13:11:00 GMT

Hi Michael,

Didn't get your array declaration actually.

The array value that is needed is Array(1, 4), if you see the below macro recording of the manual

operation.

I am trying to put the below thing in BP editor , where the array declaration is going wrong or not sure what's going wrong, the code stage is throwing the

error : Parameter incorrect(INVALID_ARG)

Let me know if this is clear of my ask and could you help me on this

------------------------------
Sasikanth Machavarapu
------------------------------

RE: How to use Field Info in Text to Columns Code stage

michaeloneil — Fri, 04 Mar 2022 13:24:00 GMT

Hi

Sasikanth, below I've shared the code I used for my text to columns action. I built this a while ago and its been working fine on several developments so hopefully you find use of it. The set range sets the range if the endcell is not provided e.g. "A1" would then be set as "A1:A1"

Inputs - handle, wb name, ws name, startcell (this is the first row of the values you want to split A1), endcell (last row of values to be split A1000), split char (the character you want to delimit with such as | )
Outputs - Success, Message
----------------------------------------------------------------------

Dim wb, ws As Object

Dim excel, sheet, range As Object

Dim matrix = New Integer (3, 1) {{1, 1}, {2, 1}, {3, 1}, {4, 1}}

Try

wb = GetWorkbook(handle, Source_Workbook)

ws = GetWorksheet(handle, Source_Workbook, Source_Worksheet)

wb.Activate()

ws.Activate()

excel = ws.Application

sheet = excel.ActiveSheet

range = sheet.Range(CellRange)

range.Select()

excel.Selection.TextToColumns (Destination:=sheet.Range(Startcell), DataType:=1, _

TextQualifier:=-4142, ConsecutiveDelimiter:=False, Tab:=False, _

Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _

:=Split_Char, FieldInfo:=matrix, _

TrailingMinusNumbers:=True)

Success = True

Catch e As Exception

Success = False

Message = e.Message

Finally

wb = Nothing

ws = Nothing

End Try

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

RE: How to use Field Info in Text to Columns Code stage

ewilson — Fri, 04 Mar 2022 13:30:00 GMT

@SasikanthMachav,

That last example implies you're running this directly within your Excel worksheet as a macro. Is that the case, or are you running this in a Code stage within Blue Prism? There are subtle differences.

As an example, if you're running this in a Code stage you need to enclose the parameters in parentheses.

Ex:

range.TextToColumns(Destination:=Range(DestinationCell),DataType:=1, TextQualifier:=1,ConsecutiveDelimiter:=False,Tab:=True,Semicolon:=False,Comma:=False,Space:=False,Other:=False, FieldInfo:=arr1,TrailingMinusNumbers:=True)

Cheers,

------------------------------
Eric Wilson
Director, Integrations and Enablement
Blue Prism Digital Exchange
------------------------------