Question Import Tab Delimited File into Excel

[Sinikal]

I need help taking a tab-delimited text file and dumping it into a new worksheet in Excel. I get a ton of errors when I attempt to name the WorkSheet or add it to the WorkBook. Any suggestions? I want to be able to control the name of the new worksheet.

Private Sub btnMerge_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnMerge.Click
        Dim err As Boolean = False 
        Try
            If Not File.Exists(tbTextFile.Text) Then
                MsgBox("Text File doesn't exist")
                err = True
            End If
            If Not File.Exists(tbExcelFile.Text) Then
                Dim xlApp As Excel.Application = New Excel.Application
                xlApp.Workbooks.Add()
                xlApp.Workbooks(1).SaveAs(tbExcelFile.Text)
                xlApp.Workbooks(1).Close()
                xlApp.Quit()
            End If 
            If Not err Then
                Dim fs As FileStream = New FileStream(tbTextFile.Text.Trim, FileMode.Open, FileAccess.Read)
                Dim sr As StreamReader = New StreamReader(fs)
                Dim values As String()
                Dim xlApp As Excel.Application = New Excel.Application
                Dim xlWorkBook As Excel.Workbook = xlApp.Workbooks.Open(tbExcelFile.Text.Trim)
                Dim xlWorkSheet As Excel.Worksheet = New Excel.Worksheet 
                Do While sr.Peek() >= 0
                    values = Split(sr.ReadLine, ControlChars.Tab)
                    'Console.WriteLine(values(1)) 
                    For i = 0 To values.GetUpperBound(0)
                        Console.WriteLine(values(i)) 
                        'xlWorkSheet.Name = "testme"
                        'xlWorkSheet.Cells(1, i + 1) = values(i)
                    Next
                Loop 
                xlWorkBook.Worksheets.Add(xlWorkSheet)
                xlWorkBook.Close()
                xlApp.Quit()
            End If 
 
        Catch ex As Exception
            Throw ex
        End Try
    End Sub

 

[MattP]

Renaming an existing sheet.

xlWS = CType(xlWB.Sheets(1), Excel.Worksheet)
xlWS.Name = "Example"

Inserting a worksheet after the "Example" sheet

xlWB.Sheets.Add(After:=xlWB.Sheets("Example"))

 

[Sinikal]

I want to add the new worksheet to the very end or very beginning of the WorkBook. I shouldn't need to know the worksheet name.

When I try to copy the values into the worksheet I get this error:

"Unable to cast COM object of type 'Excel.WorksheetClass' to interface type 'Excel._Worksheet'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{000208D8-0000-0000-C000-000000000046}' failed due to the following error: No such interface supported (Exception from HRESULT: 0x80004002 (E_NOINTERFACE))."

The code snippet is:

Do While sr.Peek() >= 0
                    values = Split(sr.ReadLine, ControlChars.Tab)
                    'Console.WriteLine(values(1))

                    For i = 0 To values.GetUpperBound(0)
                        xlWorkSheet.Cells(count, i + 1) = values(i) ' ERROR OCCURING
                    Next

                    count = count + 1
                Loop

 

[Tom]

If you load your tab delimited file into a dataset/datatable you can output it to an excel file using oledb without the need of automating excel.