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"