How to use Field Info in Text to Columns Code stage

[SasikanthMachav]

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

[ewilson]

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

[michaeloneil]

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

[SasikanthMachav]

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

[SasikanthMachav]

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

[michaeloneil]

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

[ewilson]

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