Question Import Tab Delimited File into Excel

Sinikal · #1 (**permalink**) 12-18-2009, 10:11 AM

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.

Code:

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 · #2 (**permalink**) 12-18-2009, 11:09 AM

Renaming an existing sheet.

Code:

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

Inserting a worksheet after the "Example" sheet

Code:

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

Sinikal · #3 (**permalink**) 12-18-2009, 12:43 PM

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:

Code:

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 · #4 (**permalink**) 12-20-2009, 10:57 AM

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.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)