Pasting data from text file to excel using VB 2010

Question

This may sound very easy but I am a newbie in vb programming. I have a text file with 11 rows, 2 columns separated by 39 spaces between them. Now I'm trying to read this file, copy it & paste into an excel sheet. Here is the code that I have so far:

Imports System.IO

Public Class Form1

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim oExcel As Object
        Dim oBook As Object
        Dim oRow As Int16 = 0

        oExcel = CreateObject("Excel.Application")
        oBook = oExcel.Workbooks.Add

        'Read input .txt file line-by-line, Copy to Clipboard & Paste to Excel

        Using rdr As New System.IO.StreamReader("C:\Temp\ONI.txt")
            Do While rdr.Peek() >= 0
                Dim InputLine As String = rdr.ReadLine
                oRow = oRow + 1
                System.Windows.Forms.Clipboard.SetDataObject (InputLine)
                oBook.Worksheets(1).Range("A" + oRow.ToString).Select()
                oBook.Worksheets(1).Paste()
            Loop
            rdr.Close()
        End Using

        oExcel.Visible = True
        'oExcel.SaveAs("C\Temp\test.xls")
        oBook = Nothing
        oExcel.Quit()
        oExcel = Nothing
    End Sub

End Class

This is working to the extent that an excel workbook is opened & the data is getting pasted in A1 to A11 i.e the rows are 11 (which is ok) but the column is only 1 (it should be in columns A & B). I know this is very easy, please guide me.

Also the code stops at the "Save as" line (which I have commented). When run it shows error Public member 'SaveAs' on type 'ApplicationClass' not found.

Answer 1

You should be doing oBook.SaveAs(...), you are not saving the Excel.Application, just the workbook.

For importing text, why not just use the Workbooks.OpenText method? With some tweaking this should allow you to open the delimited files in the desired format. This saves yout he hassle of trying to also do text-to-columns after importing it, and the nasty hassle of having to work with the clipboard.

E.g., this opens a text file in an Excel workbook, treats spaces as the delimiter, consecutive delimiters as one, so it should split your columns correctly.

Sub OpenTextFile()
Dim wbText As Workbook
Dim sFName As String ' file name & path'

sFName = Application.GetOpenFilename()
Workbooks.OpenText Filename:=sFName _
    , Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
    xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, Semicolon:=False _
    , Comma:=False, Space:=True, Other:=False, FieldInfo _
    :=Array(1, 1), TrailingMinusNumbers:=True

Exit Sub
End Sub

Here is the test file I used:

And here is the output, properly in 2 columns: