write data to excel

Hi guys,

I have a simple stop watch application. with start, stop and reset buttons. The timer is displayed in a text box.

When I stop the timer I want to be able to write the timer figure to an excel spreadsheet. I need to have the following columns in the spreadsheet, No, Date, Time (Timer figure from app). obviously writing the data onto the next available row each time.

I have worked out how to write the data to excel successfully with the following code:

Code:

---


Imports Microsoft.Office.Interop
Imports System.IO
Imports Excel

Public Partial Class MainForm
        Dim counter As Integer
        'Dim wb As Excel.Workbook
        Dim oExcel As Object
        Dim oBook As Object
        Dim oSheet As Object
        Const sSaveDirectory = "C:"

        'Start a new workbook in Excel.
       
        Public Sub New()
                ' The Me.InitializeComponent call is required for Windows Forms designer support.
                Me.InitializeComponent()
        End Sub
       
        Sub MainFormLoad(sender As Object, e As EventArgs)
                timer1.enabled = false
        End Sub
       
        Sub BtnstartClick(sender As Object, e As EventArgs)
        timer1.Enabled = True       
               
        End Sub
       
        Sub Timer1Tick(sender As Object, e As EventArgs)
                counter = counter + 1  'we set the counter to count here
                  Textbox1.Text = counter    'write the counter value out as text
                 
        End Sub
       
        Sub BtnstopClick(sender As Object, e As EventArgs)
                timer1.Enabled = false
        End Sub
       
        Sub Button1Click(sender As Object, e As EventArgs)
                oExcel = CreateObject("Excel.Application")
        oBook = oExcel.Workbooks.Add
       
                'Add data to cells of the first worksheet in the new workbook.
        oSheet = oBook.Worksheets(1)
        oSheet.Range("A1").Value = "Date"
        oSheet.Range("B1").Value = "Time"
        oSheet.Range("A1:B1").Font.Bold = True
        oSheet.Range("A2").Value = today()
        oSheet.Range("B2").Value = textbox1.Text
               
        'Save the Workbook and quit Excel.
        oBook.SaveAs(sSaveDirectory & "Book1.xls")
        oSheet = Nothing
        oBook = Nothing
        oExcel.Quit()
        oExcel = Nothing
        GC.Collect()
     
        End Sub
End Class

---

How would I go about adding the data onto a new row each time instead of overwriting the first row?

thanks

This is just a guess (Works in VB6)

Code:

---

SheetRow = 2
SheetCol = 10

oSheet.Cells(SheetRow, SheetCol) = "aaaaaaaaaaaaaaa"

---

hi

thanks for the reply. but that will not append to the excel file each time will it?

Thats right - it does not append - it simply puts data into the cells you want

But you can use it to append simply by doing the following

1) Open the Excel Sheet

2) Cycle down a column looking for the first blank slot

Code:

---

FindFreeRow:
if trim(oSheet.Cells(SheetRow, SheetCol)) <> "" then
    SheetRow = SheetRow + 1
    goto FindFreeRow
endif

---

3) Insert data into the first free slot and beyond

yes thats exactly what I need to be able to do. still having trouble getting it work though.

this is what i have for my save button click event:

Code:

---


 Dim result As DialogResult = Windows.Forms.DialogResult.Yes
    Dim oRow As Object
    Dim oColumn As Object

        'Displays a MessageBox using the Question icon and specifying the No button as the default.
        result = MsgBox("Save result to C:\TimerResult.xls?", _
                      MsgBoxStyle.YesNo Or MsgBoxStyle.Question, _
                      "Export Result to Excel")

        If result = Windows.Forms.DialogResult.Yes Then
            oExcel = CreateObject("Excel.Application")
            oBook = oExcel.Workbooks.Add


          If Trim(oSheet.Cells(oRow, oColumn)) <> "" Then
                oRow = oRow + 1
                GoTo FindFreeRow
            End If


            'Add data to cells of the first worksheet in the new workbook.

            oSheet = oBook.Worksheets(1)
            oSheet.Range("A1").Value = "Date"
            oSheet.Range("B1").Value = "Time"
            oSheet.Range("A1:B1").Font.Bold = True

            'Save the Workbook and quit Excel.

            oBook.SaveAs(sSaveDirectory & "TimerResults.xls")
            oSheet = Nothing
            oBook = Nothing
            oExcel.Quit()
            oExcel = Nothing
            GC.Collect()

        End If
    End Sub

---

i'm not sure what the findfreerow needs to be declared as.. any guidance appreciated. thanks

Pick one, and use that:

Find the last used cell, before a blank in a Column:

Code:

---

Sub LastCellBeforeBlankInColumn()

  Range("A1").End(xldown).Select

End Sub

---

Find the very last used cell in a Column:

Code:

---

Sub LastCellInColumn()

  Range("A65536").End(xlup).Select

End Sub

---

Find the last cell, before a blank in a Row:

Code:

---

Sub LastCellBeforeBlankInRow()

  Range("A1").End(xlToRight).Select

End Sub

---

Find the very last used cell in a Row:

Code:

---

Sub LastCellInRow()

  Range("IV1").End(xlToLeft).Select

End Sub

---

Find the very last used cell on a Worksheet:

Code:

---

Sub Demo()

Cells.Find(What:="*", After:=[A1], SearchDirection:=xlPrevious).Select

End Sub

---

thanks for the samples. however "xldown", "xlup" etc need to be declared, what do these need to be declared as?

You can always search for vb.net excel xlDown

Code:

---

Dim iNextRow As Integer = ObjWS.UsedRange.End(Excel.XlDirection.xlDown).Row + 1
  ObjWS.Cells(iNextRow, 1) = "new row data, first column"
  ObjWS.Cells(iNextRow, 2) = "new row data, second column"

---