1

I have the following code but i'd like my data to be added to the excel file each time i input information instead of overwriting the same cell. (Find the next empty cell and add textbox1.text data to it)

Imports Excel = Microsoft.Office.Interop.Excel
Public Class Form1
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    Dim xlApp As New Excel.Application
    Dim xlWorkBook As Excel.Workbook
    Dim xlWorkSheet As Excel.Worksheet

    Try
        xlApp.DisplayAlerts = False
        xlWorkBook = xlApp.Workbooks.Add
        xlWorkSheet = DirectCast(xlWorkBook.Sheets("Sheet1"), Excel.Worksheet)

        xlApp.Visible = False 'Don't show Excel; we can and we don't have too

        xlWorkSheet.Cells(1, 1) = TextBox1.Text
        xlWorkBook.SaveAs("C:\Temp\Book1.xlsx", FileFormat:=56) 'Save the workbook
        xlWorkBook.Close() 'Close workbook
        xlApp.Quit() 'Quit the application

        'Release all of our excel objects we used...
        ReleaseObject(xlWorkSheet)
        ReleaseObject(xlWorkBook)
        ReleaseObject(xlApp)

    Catch ex As Exception
    End Try
End Sub
Public Shared Sub ReleaseObject(ByVal obj As Object)
    Try
        System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
    Catch ex As Exception
        obj = Nothing
    Finally
        GC.Collect()
    End Try
End Sub
End Class

Please help!

Visual Vincent
  • 18,045
  • 5
  • 28
  • 75
Area51plustax
  • 11
  • 1
  • How about http://stackoverflow.com/questions/14957994/select-first-empty-cell-in-column-f-starting-from-row-1-without-using-offset – Andrew Mortimer Mar 05 '17 at 11:24
  • You shouldn't call `GC.Collect()` manually. The Garbage Collector runs on its own. – Visual Vincent Mar 05 '17 at 11:52
  • Is your intention to keep re-opening some workbook e.g. `C:\book1.xlsx` every time and add a value in the next row ? At the moment you are using `xlApp.Workbooks.Add` and opening a new workbook. – Robin Mackenzie Mar 05 '17 at 13:13

0 Answers0