Using C# to access TextToColumns in Excel Interop




I am trying to convert an Excel VBA macro over to C#, the problem is
that I get an error when running the code I have come up with. Below
is a copy of the VBA macro, C# code, and the unhandled exception. If
anyone has ideas on how to resolve this unhandled exception I would be
grateful. Any help that you can provide would be great. Lastly, is
there a way to include the Array information in the FieldInfo

--- VBA Macro ---
Selection.TextToColumns Destination:=Range("A1"),
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=True, _
Semicolon:=False, Comma:=True, Space:=False, Other:=False,
FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1),
Array(5, 1), Array(6, 1), _
Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11,
1), Array(12, 1), Array(13, 1 _
), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1),
Array(18, 1), Array(19, 1), Array _
(20, 1), Array(21, 1)), TrailingMinusNumbers:=True

--- C# Conversion ---
oRange = oSheet3.get_Range("A:A", Missing.Value);
oRange.TextToColumns("A1", Excel.XlTextParsingType.xlDelimited,
Excel.XlTextQualifier.xlTextQualifierDoubleQuote, false, true, false,
true, false, false, false, Excel.XlColumnDataType.xlGeneralFormat,
Missing.Value, Missing.Value, true);

--- Unhandled Exception ---
just-in-time (JIT) debugging instead of this dialog box.

************** Exception Text **************
System.Runtime.InteropServices.COMException (0x800A03EC):
TextToColumns method of Range class failed
at System.RuntimeType.ForwardCallToInvokeMember(String memberName,
BindingFlags flags, Object target, Int32[] aWrapperTypes, MessageData&
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)
at AdSupportProductivitySuite.POCReport.text2Columns() in C:\Users
\kerick\Documents\Visual Studio 2008\Projects
\POCReport.cs:line 168
at AdSupportProductivitySuite.Form1.btnPOCGenerate_Click(Object
sender, EventArgs e) in C:\Users\kerick\Documents\Visual Studio
\Form1.cs:line 130
at System.Windows.Forms.Control.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons
button, Int32 clicks)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ButtonBase.WndProc(Message& m)
at System.Windows.Forms.Button.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage
(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc
(Message& m)
at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32
msg, IntPtr wparam, IntPtr lparam)

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question