Question
Staidlogic LLC
US
Last activity: 13 Sep 2019 10:12 EDT
Pega Robotics Excel Text to Column not working
Using Excel 2016 Connector, I am trying to use basic text-to-column function of a selected range in excel file. But the function TextToColumns() gives error. Not sure where the problem is. It is simple single column excel with data separated with ;
1) Exception Information
*********************************************
Exception Type: OpenSpan.Automation.AutomationException
EventLink: OpenSpan.Automation.EventLink
ThrowingLink: OpenSpan.Automation.EventLink
CreatedDateTime: 9/11/2019 3:52:38 PM
AppDomainName: "OpenSpan.Runtime.exe"
ThreadIdentityName: ""
AdditionalInformation: NameValueCollection with 0 items
Message: "Error executing link in Automation: Automation1 - From: Automation1.variable1.Properties To: Automation1.rangeProxy4.TextToColumns()
TextToColumns method of Range class failed"
Data: IDictionary (System.Collections.ListDictionaryInternal) with 0 items
TargetSite: Void Execute(System.Object, OpenSpan.Automation.ExecutionEventArgs)
HelpLink: NULL
Source: "OpenSpan.Automation"
HResult: -2146232832
StackTrace Information
*********************************************
at OpenSpan.Automation.EventLink.Execute(Object sender, ExecutionEventArgs e)
at OpenSpan.Automation.EventLink.OnReceiveEvent(Object sender, ExecutionEventArgs e)
at OpenSpan.Automation.ConnectableWorker.DoWork(Object sender, ExecutionEventArgs executionArgs)
Using Excel 2016 Connector, I am trying to use basic text-to-column function of a selected range in excel file. But the function TextToColumns() gives error. Not sure where the problem is. It is simple single column excel with data separated with ;
1) Exception Information
*********************************************
Exception Type: OpenSpan.Automation.AutomationException
EventLink: OpenSpan.Automation.EventLink
ThrowingLink: OpenSpan.Automation.EventLink
CreatedDateTime: 9/11/2019 3:52:38 PM
AppDomainName: "OpenSpan.Runtime.exe"
ThreadIdentityName: ""
AdditionalInformation: NameValueCollection with 0 items
Message: "Error executing link in Automation: Automation1 - From: Automation1.variable1.Properties To: Automation1.rangeProxy4.TextToColumns()
TextToColumns method of Range class failed"
Data: IDictionary (System.Collections.ListDictionaryInternal) with 0 items
TargetSite: Void Execute(System.Object, OpenSpan.Automation.ExecutionEventArgs)
HelpLink: NULL
Source: "OpenSpan.Automation"
HResult: -2146232832
StackTrace Information
*********************************************
at OpenSpan.Automation.EventLink.Execute(Object sender, ExecutionEventArgs e)
at OpenSpan.Automation.EventLink.OnReceiveEvent(Object sender, ExecutionEventArgs e)
at OpenSpan.Automation.ConnectableWorker.DoWork(Object sender, ExecutionEventArgs executionArgs)
2) Exception Information
*********************************************
Exception Type: System.Runtime.InteropServices.COMException
ErrorCode: -2146827284
Message: "TextToColumns method of Range class failed"
Data: IDictionary (System.Collections.ListDictionaryInternal) with 0 items
TargetSite: System.Object ForwardCallToInvokeMember(System.String, System.Reflection.BindingFlags, System.Object, Int32[], System.Runtime.Remoting.Proxies.MessageData ByRef)
HelpLink: "xlmain11.chm"
Source: "Microsoft Excel"
HResult: -2146827284
StackTrace Information
*********************************************
at System.RuntimeType.ForwardCallToInvokeMember(String memberName, BindingFlags flags, Object target, Int32[] aWrapperTypes, MessageData& msgData)
at Microsoft.Office.Interop.Excel.Range.TextToColumns(Object Destination, XlTextParsingType DataType, XlTextQualifier TextQualifier, Object ConsecutiveDelimiter, Object Tab, Object Semicolon, Object Comma, Object Space, Object Other, Object OtherChar, Object FieldInfo, Object DecimalSeparator, Object ThousandsSeparator, Object TrailingMinusNumbers)
Pegasystems Inc.
US
This method seems to require several parameters to be passed objects, so it is likely that you might have to call it from a C# script. I would write your code in a C# script and have it take the Worksheet as an input so you could generate the input and output range. Here are some articles that should help you write your code.
https://docs.microsoft.com/en-us/office/vba/api/excel.range.texttocolumns
Pegasystems Inc.
US
Here is an automation like yours with a couple of minor tweaks to fix it. The highlights - the destination is a range so you need to pass it a range and for values on the TextToColumns method that are optional you must set them to Missing value. The connector has a property with that value that you can use.
