Using C# to access TextToColumns in Excel Interop Returns Range Error

Question

 Hi,

First, I apologize if this post is in the wrong place, please direct me to proper forum in if that is the case. 


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 parameter, or is it even needed? I am using Visual Studio 2008 with the 3.5 framework.

I have also already consulted the following article:
http://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.namedrange.texttocolumns.aspx

--- VBA Macro ---
Columns("A:A").Select
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&
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)
   at AdSupportProductivitySuite.POCReport.text2Columns() in C:\Users
\kerick\Documents\Visual Studio 2008\Projects
\AdSupportProductivitySuite\AdSupportProductivitySuite
\POCReport.cs:line 168
   at AdSupportProductivitySuite.Form1.btnPOCGenerate_Click(Object
sender, EventArgs e) in C:\Users\kerick\Documents\Visual Studio
2008\Projects\AdSupportProductivitySuite\AdSupportProductivitySuite
\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)

Answer 1

 whats the source, I think your problem is range

Suppose you want to convert text of cell B1 and place it C1 onwards then

 Microsoft.Office.Interop.Excel.Range destrnge =oSheet3.range("C1");
    Microsoft.Office.Interop.Excel.Range source =oSheet3.range("B1");
   
    source.TextToColumns(destrnge, Excel.XlTextParsingType.xlDelimited, Excel.XlTextQualifier.xlTextQualifierDoubleQuote,missing ,missing , missing,missing , true, missing, missing,
    , missing,missing ,missing );


My delimeter is space, please change it for yours.

---

Arjun Paudel Not Getting Live Alerts at the moment

Answer 2

1	private static void TextToColumns(string fileName)
2	{
3	ApplicationClass app = new ApplicationClass();
4	app.DisplayAlerts = false;
5
6	try
7	{
8	Workbook wb = app.Workbooks.Open(
9	fileName, Type.Missing, Type.Missing, Type.Missing,
10	Type.Missing, Type.Missing, Type.Missing, Type.Missing,
11	Type.Missing, Type.Missing, Type.Missing, Type.Missing,
12	Type.Missing, Type.Missing, Type.Missing
13	);
14
15	if (wb != null && wb.Worksheets != null && wb.Worksheets.Count > 0)
16	{
17	Worksheet ws = wb.Worksheets[1] as Worksheet;
18
19	ws.get_Range("A1", Type.Missing).EntireColumn.TextToColumns(
20	ws.Cells[1, 1], XlTextParsingType.xlDelimited, XlTextQualifier.xlTextQualifierDoubleQuote,
21	false, true, false, false, false, false, Type.Missing, XlColumnDataType.xlTextFormat,
22	Type.Missing, Type.Missing, Type.Missing
23	);
24	}
25
26	wb.Close(true, Type.Missing, Type.Missing);
27	}
28	catch (Exception ex)
29	{
30	MessageBox.Show(ex.ToString());
31	}
32	finally
33	{
34	if (app != null)
35	{
36	app.Quit();
37	app = null;
38	}
39	}
40	}

Hi Arjun,

I am trying to use the shown code to implement TextToColumns, but failed without any alert messages.
Could you please help to check?

Thank you very much.
/Sjn819