[m_wylie85]

Hi all have a problem working out how to loop through the rows in excel. I have rich text box that will contain serval line of text i then want the break the text into single lines, that i can do by use richtextbox.line and then use the split method. but the problem when i split the lines how can i put the first line in A1 the second into A2 etc there is no set amount of lines that will be in the richtextboxes so i would need to use some type of while loop i think but i can't figure out how to add a row for each new line any help would be great

my code:

    Public Sub ExcelAutomationBtn1()

        'Create a new workbook in Excel.
        Dim oExcel As Object
        Dim oBook As Object
        Dim oSheet As Object
        oExcel = CreateObject("Excel.Application")
        oBook = oExcel.Workbooks.Add
        oSheet = oBook.Worksheets(1)
        Dim lines
        'Transfer the field names to Row 1 of the worksheet:
        'Note: CopyFromRecordset copies only the data and not the field
        '      names, so you can transfer the fieldnames by traversing the
        '      fields collection.

        For Each line In RTBUnformatedData.Lines
            lines = Split(RTBUnformatedData.Text, ",") ' split the line of text in the rich text box

            oSheet.Cells(1, 5).Value = lines
        Next
        'Transfer the data to Excel.

        'Save the workbook and quit Excel.
        oBook.SaveAs("C:\Users\michael\Desktop\test.xls")
        oSheet = Nothing
        oBook = Nothing
        oExcel.Quit()
        oExcel = Nothing
    End Sub

[_HAWK_]

Why create the variables as objects when you can add the Excel reference you need for the version you are working with and declare them directly - Excel.Application, Excel.Workbook, Excel.Worksheet etc...

Also explicitly state the variable type:

Dim row As Integer = 5
For Each line In RTBUnformatedData.Lines
  Dim lines() As String = line.Split(","c)
  'now we need a loop for the values
  For col As Integer = 0 To (lines.Count - 1)
    oSheet.Cells(row, col).Value = lines(col)
  Next
 'move the row down
 row += 1
Next

Then don't forget to dispose the Com object correctly:

Marshal.FinalReleaseComObject(<excel object>)

[Ionut]

I don't recommand writing to an Excel file through Cells property. It is very slow. If you write an array to a range, than the performance is improved.

//Get lines array ready to be written
Dim DestinationRange as Excel.Range
DestinationRange = objSheet.Range[(Excel.Range)objSheet.Cells[1, 1], (Excel.Range)objSheet.Cells[1, lines.Length]];
DestinationRange.Value2 = lines;

[_HAWK_]

Ionut, on 08 September 2011 - 01:12 AM, said:

I don't recommand writing to an Excel file through Cells property. It is very slow. If you write an array to a range, than the performance is improved.

//Get lines array ready to be written
Dim DestinationRange as Excel.Range
DestinationRange = objSheet.Range[(Excel.Range)objSheet.Cells[1, 1], (Excel.Range)objSheet.Cells[1, lines.Length]];
DestinationRange.Value2 = lines;

Good to know!

[m_wylie85]

Hi Hawk I tried what you said and i get two errors.

firstly i have add ref 12.0 as i am working with excel 2007

secondly i declared Excel.Application etc directly as you said This reason i hadn't declare them directly before is because i was using a Microsoft tutorial and that is what it said to do ( i have never use excel with vb.net before.)

as for my two errors the first is on line 18 the error is: Error 1 Option Strict On disallows implicit conversions from 'Object' to 'Microsoft.Office.Interop.Excel.Worksheet'.

the second error is Error 2 Option Strict On disallows late binding.

this is on line 37

Option Strict On
Imports System.IO
Imports Excel = Microsoft.Office.Interop.Excel

Public Class Form1
    'Keep the application object and the workbook object global, so you can  
    'retrieve the data in Button2_Click that was set in Button1_Click.

    Dim str
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

    End Sub
    Public Sub ExcelAutomationBtn1()

        'Create a new workbook in Excel.
        Dim oExcel As Excel.Application
        Dim oBook As Excel.Workbook
        Dim oSheet As Excel.Worksheet

        oExcel = New Excel.Application
        oBook = oExcel.Workbooks.Add
        oSheet = oBook.Worksheets.Add

        Dim lines() As String
        Dim counter = 0

        'Transfer the field names to Row 1 of the worksheet:
        'Note: CopyFromRecordset copies only the data and not the field
        '      names, so you can transfer the fieldnames by traversing the
        '      fields collection.
        Dim row As Integer = 5
        For Each line In RTBUnformatedData.Lines
            lines = Split(RTBUnformatedData.Text, ",") ' split the line of text in the rich text box

            'now we need a loop for the values
            For col As Integer = 0 To (lines.Count - 1)
                oSheet.Cells(row, col).Value = lines(col)
            Next
            'move the row down
            row += 1
        Next

        'Transfer the data to Excel.

        'Save the workbook and quit Excel.
        oBook.SaveAs("C:\Users\michael\Desktop\test.xls")
        oSheet = Nothing
        oBook = Nothing
        oExcel.Quit()
        oExcel = Nothing

    End Sub

This post has been edited by m_wylie85: 08 September 2011 - 02:16 PM

[_HAWK_]

Did you notice how I put the declaration of lines inside the loop?

[m_wylie85]

No i hadn't to be honest I changed it there like yours but i still get the two same errors:

        Dim row As Integer = 5
        For Each line In RTBUnformatedData.Lines
            Dim lines() As String = line.Split(","c)
            ' split the line of text in the rich text box

            'now we need a loop for the values
            For col As Integer = 0 To (lines.Count - 1)
                oSheet.Cells(row, col).Value = lines(col)
            Next
            'move the row down
            row += 1
        Next

[_HAWK_]

That's funny cause:

Dim excel As Object
excel = CreateObject("Excel.Application")
'is late binding.

I would import like this:

Imports Microsoft.Office.Interop

This post has been edited by hawkvalley1: 08 September 2011 - 03:36 PM

[m_wylie85]

sorry do you mean you would take out this import and use yours

Imports Excel = Microsoft.Office.Interop.Excel

[Psyguy]

If you are interested in working with Excel through VB.NET, take a look at my blog on DIC. I wrote up a document about my experience learning to write to Excel and a few things I learned along the way. It's not all inclusive, but it has some decent tidbits of useful information you may be able to use. I wish you luck. Working with Excel in .NET isn't the easiest thing to do.

EDIT: http://www.dreaminco...net-excel-hell/

Side Note: I also was unaware that adding a range was faster than cell by cell. thanks for the info!

This post has been edited by Psyguy: 08 September 2011 - 03:54 PM

[m_wylie85]

Hi had a quick look at your blog there hopefully it will help i am wrecked now staring at this for 5 hours confused need to take a break

[Psyguy]

I feel your pain brother, lol.

[m_wylie85]

hey all still having problems just can't figure this out. I can get the text that i want into the cells i want(sort of). so it goes like this in my while loop i can get the str values in to the cells A,B,C on row 1 and it does loop through all of the line of text in the dat file (i use a break point to see that it was reading all the lines. the problem is that it sets the A,B,C cells in row 1 to the last line of text in the dat file ( i know it doing that cause it is rewriting over the previously took in line of text until is comes to the end of the dat file. i can't figure out how to increment the row each time there's a new line in the dat file i have tried a lot of different ways but i keep get errors.

my code: (i know this needs tidied up but will do after i figure this out)

    Private Sub btnOpenFile_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnOpenFile.Click
        Call conn()
    End Sub

    Private Sub conn()

        Dim xlApp As New Excel.Application
        Dim xlWorkBook As Excel.Workbook
        Dim xlWorkSheet As Excel.Worksheet
        Dim misValue As Object = System.Reflection.Missing.Value
        xlWorkBook = xlApp.Workbooks.Add(misValue)


        Dim openFileDialog1 As New OpenFileDialog()
        openFileDialog1.InitialDirectory = "C:\Users\michael\Desktop"
        openFileDialog1.Filter = "DAT files (*.DAT)|*.DAT|All files (*.*)|*.*"
        openFileDialog1.FilterIndex = 1
        openFileDialog1.RestoreDirectory = True

        ' mock up of the data i am looping through
        '128/AH675671/524365/th546372/John/White/7464554
        '129/AH675672/524365/th546372/Jack/White/7464554
        '130/AH675673/524365/th546372/Steven/White/7464554
        '131/AH675674/524365/th546372/Michael/White/7464554
        '132/AH675675/524365/th546372/Mark/White/7464554
        If openFileDialog1.ShowDialog() = System.Windows.Forms.DialogResult.OK Then
            Dim myFile = openFileDialog1.FileName
            Dim retrys = 0
            Try
                Using objReader = New StreamReader(myFile)
                    Try

                        While Not objReader.EndOfStream
JumpBackIn:

                            Dim str = objReader.ReadLine.Split("/"c)
                            ' output to rich text box
                            xlWorkSheet = CType(xlWorkBook.Sheets("sheet1"), Excel.Worksheet)

                            xlWorkSheet.Cells(1, 1) = str(1)
                            xlWorkSheet.Cells(1, 2) = str(4)
                            xlWorkSheet.Cells(1, 3) = str(5)


                        End While

                    Catch ex_OutOfMem As OutOfMemoryException When retrys > 0
                        ' Try again upto 3 times.
                        If retrys < 3 Then
                            objReader.DiscardBufferedData()
                            retrys += 1
                            ' Jump back to before read.
                            GoTo JumpBackIn
                        Else
                            ' otherwise throw the exception
                            Throw ex_OutOfMem
                        End If
                    Catch ex_OutOfMem As OutOfMemoryException
                        ' Discard data and try again
                        objReader.DiscardBufferedData()
                        retrys = 1
                        GoTo JumpBackIn
                    Catch ex_IO As IOException
                        Throw ex_IO
                    Catch ex As Exception
                        Throw ex
                    Finally
                        objReader.Close()
                    End Try
                End Using
            Catch Ex As Exception
                MessageBox.Show("Cannot read file " & Ex.Message.ToString)
            End Try
        End If
        xlWorkBook.SaveAs("C:\Users\michael\Desktop\vbexcel.xls")
                        xlWorkBook.Close()
                        xlApp.Quit()

                        releaseObject(xlApp)
                        releaseObject(xlWorkBook)


        MsgBox("Excel file created , you can find the file c:\Users\michael\Desktop\vbexcel.xls")
    End Sub

    Private Sub releaseObject(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try
    End Sub

I have also tried to loop like this:

        If openFileDialog1.ShowDialog() = System.Windows.Forms.DialogResult.OK Then
            Dim myFile = openFileDialog1.FileName
            Dim retrys = 0
            Try
                Using objReader = New StreamReader(myFile)
                    Try

                        While Not objReader.EndOfStream
JumpBackIn:
                            Dim line As Integer
                            Dim str = objReader.ReadLine.Split("/"c)
                            ' output to rich text box
                            xlWorkSheet = CType(xlWorkBook.Sheets("sheet1"), Excel.Worksheet)
                            For line = 0 To 5
                                xlWorkSheet.Cells(line, 1) = str(1)
                                xlWorkSheet.Cells(line, 2) = str(4)
                                xlWorkSheet.Cells(line, 3) = str(5)
                                line += 1
                            Next
                        End While

so above i was try to make the row increment each time it goes through the loop but i get the error: can not read file exception from HRESULT:Ox800A03ec

This post has been edited by m_wylie85: 11 September 2011 - 01:54 PM

[m_wylie85]

so is there any way i can set a variable to the excel row so i can just increment the variable each time

[_HAWK_]

Of course, an integer type is fine. Just remember it is a 1 based stay instead of 0 based.