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

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

    Saturday, November 29, 2008 8:33 PM

Answers

  •  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
    • Marked as answer by Guo Surfer Wednesday, December 3, 2008 7:18 AM
    Sunday, November 30, 2008 12:23 PM

All replies

  •  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
    • Marked as answer by Guo Surfer Wednesday, December 3, 2008 7:18 AM
    Sunday, November 30, 2008 12:23 PM

  • 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                         falsetruefalsefalsefalsefalse, 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

    Tuesday, March 10, 2009 1:36 PM