Write text to a spreadsheet

[bryann]

Hi i am trying to write the contents of textboxes to a spreadsheet. The code i have does work but at the moment i have to specify what cells to write to. What i would like is for the text to get written to a different row each time but i do not know how to increment the row each time if there is text already in a cell. Any help would be greatly appreciated.

        Dim xlApp As Microsoft.Office.Interop.Excel.Application
        Dim xlBook As Microsoft.Office.Interop.Excel.Workbook
        Dim xlSheet As Microsoft.Office.Interop.Excel.Worksheet


        xlApp = GetObject("", "Excel.Application")

        xlBook = xlApp.Workbooks.Open("D:\VBtest\test.xlsx")

        xlSheet = xlBook.Worksheets("Sheet1")

        xlApp.Visible = True


        xlSheet.Cells(1, 1).Value = TextBox1.Text
        xlSheet.Cells(1, 2).Value = TextBox2.Text


        xlBook.Save()


        xlBook.Close(False)


        xlApp.Quit()
    End Sub

[adam_k]

If all your textboxes are named TextBox and a number, you can try:

dim last_row as int = 1 
for i = 1 to 10 'place here the maximum number of txtboxes
while (xlSheet.Cells(1,last_row).text <> "" and last_row < 65536 ) 'I 
    last_row = last_row + 1 
end while 
if last_row < 65536 then 
xlSheet.Cells(1,last_row).text = cobj("Textbox" & i ).text 
end if 

next

If your textboxes are not all numbered sequentially, you can loop through the objects on your form and check their type. I'm sure there are a couple of examples in VB.NET sub-forum.

Please note that I haven't tested the above code and might contain errors.

[Reverend Jim]

When you write code to use the Excel application object it is not unusual to end up with multiple Excel.exe instances running. You should check with Task Manager for this.

[bryann]

Adam K thank you for your help your code helped me a lot i wouldnt have got it working without that. It was placing the text in a new column though instead of a new row so i altered it slightly and got it working now.
Reverend Jim thanks for the heads up. I checked and i had many instances running. How do i stop this?

My code now for anyone that is interested is

        Dim xlApp As Microsoft.Office.Interop.Excel.Application
        Dim xlBook As Microsoft.Office.Interop.Excel.Workbook
        Dim xlSheet As Microsoft.Office.Interop.Excel.Worksheet


        xlApp = GetObject("", "Excel.Application")

        xlBook = xlApp.Workbooks.Open("D:\VBtest\test.xlsx")

        xlSheet = xlBook.Worksheets("Sheet1")

        xlApp.Visible = True

        Dim last_column As Integer = 1
        Dim last_row As Integer = 1


        While (xlSheet.Cells(last_row, last_column).text <> "" And last_column < 65536) 'I
            last_row = last_row + 1
        End While
        If last_column < 65536 Then
            xlSheet.Cells(last_row, last_column).value = TextBox1.Text
            last_row = last_row + 1
            xlSheet.Cells(last_row, last_column).value = TextBox2.Text
            last_row = last_row + 1
            xlSheet.Cells(last_row, last_column).value = TextBox3.Text


        End If




        xlBook.Save()


        xlBook.Close(False)


        xlApp.Quit()
    End Sub

[Reverend Jim]

I've been doing this although I don't know that it is guaranteed to work. I have never been able to find a definitive answer as to how to properly handle the Excel objects when it comes time to clean up. I suppose if it were possible to get a process id associated with the underlying Excel.exe it would be possible to do a process kill on form exit but that would be a rather ham-handed way of handling it.

    Dim xls As New Excel.Application
    Dim sheet As Excel.Worksheet

    'create a workbook and get reference to first worksheet

    xls.Workbooks.Add()
    sheet = xls.ActiveWorkbook.ActiveSheet
    .
    .
    .
    ReleaseObject(sheet)
    ReleaseObject(xls)
    .
    .
    .

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

[TnTinMN]

Iterating through a column as shown above will find the first empty cell, but not necessarily the last used cell in the column. If your user happens to leave one of the textboxes empty, you risk overwritting previous stored information. This could be handled by making sure each textbox is not empty but what happpens if someone edits the spreadsheet and accidently clears a cell?

A technique like shown below will find the last used cell in a column.

Imports xl = Microsoft.Office.Interop.Excel

Public Class Form1

   Private app As xl.Application ' need this common
   Private Const MaxRows As Int32 = 1048576 ' Max rows in Excel 2007 - adjust for your version

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

      ' start Excel
      app = CType(GetObject("", "Excel.Application"), Microsoft.Office.Interop.Excel.Application)

      ' open the workbook
      Dim wb As xl.Workbook = app.Workbooks.Open("D:\My Documents\Spreadsheets\TestRangeInterop.xlsx")

      ' get Sheet1
      Dim worksheet As xl.Worksheet = CType(wb.Worksheets("Sheet1"), Microsoft.Office.Interop.Excel.Worksheet)

      ' get the last used cell in column A

      Dim LastCell As xl.Range = LastUsedCellInColumn(worksheet, "A")

      Dim NextEmptyCell As xl.Range

      Try
         If LastCell Is Nothing Then
            ' nothing used in column A
            NextEmptyCell = worksheet.Range("A1")
         Else

            If LastCell.Row = MaxRows Then
               Throw New Exception("Column is full")
            End If
            ' move down one row
            NextEmptyCell = LastCell.Offset(RowOffset:=1, ColumnOffset:=0)
         End If

         LastCell = Nothing

         ' do what you need to do with the cell address

         MsgBox(NextEmptyCell.Address)


      Catch ex As Exception

         MsgBox(ex.Message)

      Finally

         ' clean up

         NextEmptyCell = Nothing
         worksheet = Nothing
         wb.Close()
         wb = Nothing
         app.Quit()
         app = Nothing

         GC.Collect() ' delete all released objects so that Excel closes
      End Try

   End Sub

   ' My Excel Macro to do this. Will recreate in VB.Net

   'Function LastUsedCellInColumn(ByVal col As String) As Range
   '    LastUsedCellInColumn = Nothing

   '    Dim rng As Range
   '    rng = Intersect(ActiveSheet.UsedRange, Columns(col))
   '    If Not rng Is Nothing Then
   '        Dim LastCell As Range
   '        LastCell = rng.Cells(rng.Cells.Count, 1)
   '        If LastCell.Value = vbNullString Then
   '            LastUsedCellInColumn = rng.Find(What:="*", After:=LastCell, SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
   '        Else
   '            LastUsedCellInColumn = LastCell
   '        End If
   '    End If
   'End Function


   'note the byref on ws
   Private Function LastUsedCellInColumn(ByRef worksheet As xl.Worksheet, ByVal column As String) As xl.Range

      Dim col As String = column & ":" & column

      ' get the intersection of the column and the sheet's usedrange 
      ' it will be either nothing or a range in the column
      Dim intersect As xl.Range = app.Intersect(worksheet.Range(col), worksheet.UsedRange)

      LastUsedCellInColumn = Nothing

      If intersect IsNot Nothing Then
         Dim LastCell As xl.Range = CType(intersect.Cells(intersect.Cells.Count, 1), Microsoft.Office.Interop.Excel.Range)
         If LastCell.Value Is Nothing Then
            ' work our way back up
            LastUsedCellInColumn = intersect.Find(What:="*", _
                                                  After:=LastCell, _
                                                  SearchOrder:=xl.XlSearchOrder.xlByRows, _
                                                  SearchDirection:=xl.XlSearchDirection.xlPrevious)

         Else
            LastUsedCellInColumn = LastCell
         End If

         LastCell = Nothing ' dealing with interfaces, so setting to nothing will not change LastUsedCellInColumn
         intersect = Nothing
      End If

   End Function

End Class

[TnTinMN]

@Jim,

I see that you been over on the MSDN forum and found KevinInstructor's (I think that's his tag) ReleaseObject method that he promotes.

That method can still fail if do not follow the One-Dot rule when delcaring a reference to a ComObject.

This code follows the One-Dot rule.

      ' start Excel
      app = CType(GetObject("", "Excel.Application"), Microsoft.Office.Interop.Excel.Application)

      ' open the workbook
      Dim wb As xl.Workbook = app.Workbooks.Open("D:\My Documents\Spreadsheets\TestRangeInterop.xlsx")

      ' get Sheet1
      Dim worksheet As xl.Worksheet = CType(wb.Worksheets("Sheet1"), Microsoft.Office.Interop.Excel.Worksheet)

This does not.

      ' start Excel
      app = CType(GetObject("", "Excel.Application"), Microsoft.Office.Interop.Excel.Application)

      ' get Sheet1
      Dim worksheet As xl.Worksheet = CType(app.Workbooks.Open("D:\My Documents\Spreadsheets\TestRangeInterop.xlsx").Worksheets("Sheet1"), Microsoft.Office.Interop.Excel.Worksheet)

Both get a reference to the worksheet, but the second one causes a non-referenced ComObject (the workbook) to be created. This unreferenced object is tied to Excel and excel is tied to it. So, if you application, ends this reference can not be disposed of and hangs around keeping Excel open.

If you create a reference to it as in the first code block, you can set that reference to Nothing thereby allowing the Garbage collection mechanism can get rid of it.

At least that is my understanding of it and it works for me.

These types of headaches are why I've become an advocate of the open XML document format and the use of Open XML SDK 2.0 for Microsoft Office.

[Reverend Jim]

Fortunately I no longer have to worry about this. Although it would have been a good thing to know in pre-retirement when I had to do this sort of stuff (a euphemism) for a living. One more clarification if you please. Why

Dim worksheet As xl.Worksheet = CType(wb.Worksheets("Sheet1"), Microsoft.Office.Interop.Excel.Worksheet)

instead of

Dim worksheet as Excel.Worksheet = wb.Worksheets("Sheet1")

[TnTinMN]

That is a shorthand notation that VB allows you to do. In my Import statement I defined xl to be equal to the full declaration.

Imports xl = Microsoft.Office.Interop.Excel

The Ctype is required in my code because I program with

Option Strict On     'no implicit narrowing conversions
Option Explicit On   'all variable must be defined in a Dim statement
Option Infer Off     ' no type inference,  no Dim fred without an "As Something"

and the wb.Worksheets("Sheet1") returns a Worksheet boxed in an Object. A direct assignment would involve an implict narrowing conversion to Excel.Worksheet. Option Strict disallows implicit narrowing conversions.

I prefer to have everything explicit in regards to type definitions.

It's a bit extra typing, but the payback is less chance errors by using the wrong type. The IDE flags its, I take a second to think about what I really meant and 90% of the time I can select a correction from those offered by the IDE; so it eliminates some of the extra typing with two mouse clicks.

Some will also disagree with my choice of Option Infer Off, but to me infer = compiler make a guess about what I mean. Guessing my intent is something that makes me ill when it comes to my code.

Its bad enough that I occasionally type in absolute garbage that is perfectly valid as far as the compiler is concerned. ;)

[Reverend Jim]

Got it. Thanks.

[bryann]

TnTinMN you are right i tried it out and if a textbox is left blank then other cells are overwritten. I tried out your code but i am getting an error when i press my button to write the contents. I am certainly no expert and cannot work out how to correct this.
"Object reference not set to an instance of an object."
On this line of code in the function

Dim intersect As xl.Range = app.Intersect(worksheet.Range(col), worksheet.UsedRange)

[bryann]

Sorry i had the code wrong. My actual error is 'NullReferenceSection was unhandled" Object reference not set to an instance of an object.
For this line of code

 Dim wb As xl.Workbook = app.Workbooks.Open("D:\VBtest\test.xlsx")

[TnTinMN]

The only thing that could through a nul exception in that statement is "app".

Make sure that app has a value. Add a "Stop" statement likes this, and then inspect app.

      ' start Excel
      app = CType(GetObject("", "Excel.Application"), Microsoft.Office.Interop.Excel.Application)

      Stop

Another option is to modify the app declaration to be like this (add "New" to it):

Private app As New xl.Application ' need this common

and then delete:

      ' start Excel
      app = CType(GetObject("", "Excel.Application"), Microsoft.Office.Interop.Excel.Application)

This will lock you into the particular version of Excel that you imported though.

[Begginnerdev]

Kind of an OCD thing - but You can make your code a little cleaner with something like this:

Dim iCount as Integer = 1
For Each t As TextBox In Me.Controls
    xlSheet.Cells(1,i).Text = t.Text
    i+=1
Next

[adam_k]

If finding the last used cell is required - which wasn't stated in OP - then my I suggest avoiding all the code and using this:

dim last_row as int = 1 
for i = 1 to 10 'place here the maximum number of txtboxes
xlSheet.Range("A65536").end(xlUp).offset(1,0).value = cobj("Textbox" & i ).text 
end if 

next

or can be used with Begginerdev's suggestion of looping through the objects of the form:

For Each t as TextBox in Me.Controls 
   xlSheet.xlSheet.Range("A65536").end(xlUp).offset(1,0).value = t.Text
Next 

This is guaranteed to write after the last row in a specified column (I'm using "A" ) - if the last cell is before row 65536. I'm checking for 65536 as that's the last row Office 2003 supported. If this is planned to be used in Office 2007 or later the row in range can be 1048576.

@OP: I was checking that the last row didn't exceed 65536 to avoid errors. Last column in Office 2007 can't be more than 16384. I don't have Office 2003 available now to see the limit for that.

[bryann]

Thank you all for the help. I have now have it working