Excel Text To Columns From VB.net


EDIT

I have stopped updating this blog. This link can also be found in my Website.

http://www.siddharthrout.com/2012/06/29/excel-text-to-columns-from-vb-net/

 

There are many occasions where we need to open a text file in Excel and then arrange them in separate columns. If we directly open the file in Excel then we notice that everything is filled in Col A. In such a scenario what  can we do?

Excel has an inbuilt feature which is called Text To Columns which parses a column of cells that contain text into several columns. In Excel 2010 you can find that feature in the Data Tab.

image

So how do we call this functionality from VB.net? Let’s take an example. Copy the below text and paste it in a Notepad and save it as “C:\Sample.Txt”

Name, Age, Sex
Frank, 21, M
Matha, 22, F
Jack, 23, M
William, 35, M

image

Now if we open the file directly in Excel this is what we get.

image

Now let’s try and automate the entire process in VB.Net so that the data is distributed in separate columns.

Create a form and put a button on the form. Set the reference to Microsoft Excel Library. If you do not know how to do that then see this.

Imports Excel = Microsoft.Office.Interop.Excel

Public Class Form1

    '~~> Define your Excel Objects
    Dim xlApp As New Excel.Application
    Dim xlWorkBook As Excel.Workbook
    Dim xlWorkSheet As Excel.Worksheet

    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) _
    Handles Button1.Click
        '~~> Open the File
        xlWorkBook = xlApp.Workbooks.Open("C:\Sample.txt")

        '~~> Set reference to the 1st Sheet
        xlWorkSheet = xlWorkBook.Sheets(1)

        '~~> Display Excel
        xlApp.Visible = True

        With xlWorkSheet
            .Columns(1).TextToColumns( _
            Destination:=.Cells(1, 1), _
            DataType:=Excel.XlTextParsingType.xlDelimited, _
            TextQualifier:=Excel.XlTextQualifier.xlTextQualifierDoubleQuote, _
            ConsecutiveDelimiter:=False, _
            TAB:=False, _
            Semicolon:=False, _
            Comma:=True, _
            Space:=False, _
            Other:=False, _
            TrailingMinusNumbers:=False)
        End With
    End Sub
End Class
When you run the code this is the output that you get

image

If you are looking for a VBA version then you might want to look at this MSDN Site.

HTH

About these ads

One response to “Excel Text To Columns From VB.net

  1. Nitin July 4, 2012 at 1:23 pm

    Thanks Sid.. it works the way it should have.. it helped me.
    Thanks again..

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 31 other followers

%d bloggers like this: