write data to excel

[monkeymafia]

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:



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

[George1111]

This is just a guess (Works in VB6)


SheetRow = 2
SheetCol = 10

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

[monkeymafia]

hi

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

[George1111]

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

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

3) Insert data into the first free slot and beyond

[monkeymafia]

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:



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

[dglienna]

Pick one, and use that:

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

Sub LastCellBeforeBlankInColumn()

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

End Sub



Find the very last used cell in a Column:

Sub LastCellInColumn()

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

End Sub



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

Sub LastCellBeforeBlankInRow()

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

End Sub



Find the very last used cell in a Row:

Sub LastCellInRow()

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

End Sub



Find the very last used cell on a Worksheet:

Sub Demo()

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

End Sub

[monkeymafia]

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

[dglienna]

You can always search for vb.net excel xlDown

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"