How to update a record in excel

**compgeek1979** · Mar 4th, 2015, 01:12 PM

Right now i have a timer program that when you click a stop button it adds the current date and the time from the timer to an excel spread sheet in 2 separate columns. What i would like it to do is check to see if the current date is in column A and if so then add the time from the timer to the time already displayed in column B, but if there is no current date in column A then add the current date to column A and the time from the timer to column B.

Here is my current code for that:

Code:

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnStop.Click
        Timer1.Stop()
        Me.StopWatch.Stop()
        totalElapsedTime += Me.StopWatch.Elapsed
        Dim str(2) As String
        Dim xlApp As Excel.Application
        Dim xlWorkBook As Excel.Workbook
        Dim xlWorkSheet As Excel.Worksheet
        Dim lastRow As Long
        xlApp = New Excel.Application
        xlWorkBook = xlApp.Workbooks.Open(My.Computer.FileSystem.SpecialDirectories.Desktop & "\Time Sheet.xlsx")
        xlWorkSheet = xlWorkBook.Sheets("sheet1")
        xlWorkSheet = xlWorkBook.Sheets("sheet1")
        lastRow = xlWorkSheet.Range("A" & xlApp.Rows.CountLarge).End(Excel.XlDirection.xlUp).Row + 1

        With xlWorkSheet
            .Range("A" & lastRow).Value = DateTime.Today.Date
            .Range("B" & lastRow).Value = lblClock.Text
        End With

        xlApp.DisplayAlerts = False
        xlWorkBook.Save()
        xlWorkBook.Close()
        xlApp.Quit()
        releaseObject(xlApp)
        releaseObject(xlWorkBook)
        releaseObject(xlWorkSheet)
        Me.StopWatch.Reset()
        lblClock.Text = "00:00:00"

**compgeek1979** · Mar 4th, 2015, 10:23 PM

Can anyone shed some light?

**Leary222** · Mar 5th, 2015, 09:20 AM

first you would need to search column a to check if the date is contained within it, this can be done by looping through the cells in row A

Code:

Dim I as integer =1
Dim DateFound as boolean
Do
if xlWorkSheet.Cells(1,i).Value = DateRequired then 
DateFound = True
Exit Do
End if

Loop until ActiveCell.Value =""

then if so you need to add the two times together using the cells method you can find the relevent cell using i

Code:

Cells(2,i).Value += newtime

**jayinthe813** · Mar 5th, 2015, 09:33 AM

So you can scan through column A, see if a date exists, if so then add in the date to an adjacent cell and exit, or else add a new entry:

(code written by memory)

Code:

For each cell as Excel.Range in xlWorkSheet.range("A2:A" & lastrow)

if cell.value = date.today then 'may need convert.ToDateTime or DateTime.TryParse for cell.value
cell.offset(0,1).value = lblClock.Text
exit sub
end if

Next cell


 xlWorkSheet.Range("A" & lastRow + 1).Value = Date.Today.Tostring
 xlWorkSheet.Range("B" & lastRow + 1).Value = lblClock.Text

**compgeek1979** · Mar 5th, 2015, 03:16 PM

I am having some trouble incorporating this code into my existing code. Maybe i am not placing it int he correct order but I have tried both replys to my original post and neither works very well for me.

The column at is formatted as Mar 5, 2015 if that makes a difference. and the time is formatted as H:MM

Here is the code for that button which should execute what i want to do. Please help me out.

Code:

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnStop.Click
        Timer1.Stop()
        Me.StopWatch.Stop()
        totalElapsedTime += Me.StopWatch.Elapsed
        Dim str(2) As String
        Dim xlApp As Excel.Application
        Dim xlWorkBook As Excel.Workbook
        Dim xlWorkSheet As Excel.Worksheet
        Dim lastRow As Long
        xlApp = New Excel.Application
        xlWorkBook = xlApp.Workbooks.Open(My.Computer.FileSystem.SpecialDirectories.Desktop & "\Time Sheet.xlsx")
        xlWorkSheet = xlWorkBook.Sheets("sheet1")
        xlWorkSheet = xlWorkBook.Sheets("sheet1")
        lastRow = xlWorkSheet.Range("A" & xlApp.Rows.CountLarge).End(Excel.XlDirection.xlUp).Row + 1
        'For Each cell As Excel.Range In xlWorkSheet.Range("A14:A" & lastRow)

        'If cell.Value = DateTime.Today Then 'may need convert.ToDateTime or DateTime.TryParse for cell.value
        'cell.Offset(0, 1).Value += lblClock.Text
        'Exit Sub
        ' End If

        ' Next cell


        ' xlWorkSheet.Range("A" & lastRow + 1).Value = Date.Today.ToString
        ' xlWorkSheet.Range("B" & lastRow + 1).Value = lblClock.Text
        With xlWorkSheet
            .Range("A" & lastRow).Value = DateTime.Today.Date
            .Range("B" & lastRow).Value = lblClock.Text
        End With
        Dim I As Integer = 1
        Dim DateFound As Boolean
        Do
            If xlWorkSheet.Cells(1, I).Value = DateRequired Then
                DateFound = True
                Exit Do
            End If

        Loop Until ActiveCell.Value = ""

        xlApp.DisplayAlerts = False
        xlWorkBook.Save()
        xlWorkBook.Close()
        xlApp.Quit()
        releaseObject(xlApp)
        releaseObject(xlWorkBook)
        releaseObject(xlWorkSheet)
        Me.StopWatch.Reset()
        lblClock.Text = "00:00:00"


    End Sub

**Leary222** · Mar 5th, 2015, 03:30 PM

neither of the solutions provided are as simple as copy and paste to integrate into your solution

heres an intergrated version of my solution

Code:

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnStop.Click

        timer1.Stop()
        Me.stopwatch.Stop()
        totalElapsedTime += Me.stopwatch.Elapsed
        Dim str(2) As String
        Dim xlApp As Excel.Application
        Dim xlWorkBook As Excel.Workbook
        Dim xlWorkSheet As Excel.Worksheet
        Dim lastRow As Long
        xlApp = New Excel.Application
        xlWorkBook = xlApp.Workbooks.Open(My.Computer.FileSystem.SpecialDirectories.Desktop & "\Time Sheet.xlsx")
        xlWorkSheet = xlWorkBook.Sheets("sheet1")
        xlWorkSheet = xlWorkBook.Sheets("sheet1")
        lastRow = xlWorkSheet.Range("A" & xlApp.Rows.CountLarge).End(Excel.XlDirection.xlUp).Row + 1
        Dim I As Integer = 1
        Dim DateFound As Boolean
        Dim DateRequired As Date = Today.Date

        Do
            If CDate(xlWorkSheet.Cells(I, 1).Value) = DateRequired Then


                DateFound = True
                Exit Do
            End If
        Loop Until xlWorkSheet.Cells(I, 1).Value = ""
        If DateFound = True Then
            xlWorkSheet.Cells(I, 2).value += CDate(lblclock.Text)

        Else
            xlWorkSheet.Range("A" & lastRow + 1).Value = Date.Today.ToString
            xlWorkSheet.Range("B" & lastRow + 1).Value = lblClock.Text
        End If

        xlApp.DisplayAlerts = False
        xlWorkBook.Save()
        xlWorkBook.Close()
        xlApp.Quit()
        releaseObject(xlApp)
        releaseObject(xlWorkBook)
        releaseObject(xlWorkSheet)
        Me.stopwatch.Reset()
        lblClock.Text = "00:00:00"


    End Sub

**compgeek1979** · Mar 5th, 2015, 03:45 PM

@leary thanks for you quick response. After trying your solution... what is happening is that it is not seeing that there is a record for today's date so it creates a new entry but not only that, it is skipping a row before creating the entry. so now i have a blank row between every entry.

**Leary222** · Mar 5th, 2015, 04:01 PM

in honesty I didn't test it, you will need to do some debugging and tweaking to finish it.

you need to find out why the following is returning false which is what is triggering a new record to be created

Code:

If CDate(xlWorkSheet.Cells(I, 1).Value) = DateRequired Then

to solve the gap problem change

Code:

            xlWorkSheet.Range("A" & lastRow + 1).Value = Date.Today.ToString
            xlWorkSheet.Range("B" & lastRow + 1).Value = lblClock.Text

to

Code:

            xlWorkSheet.Range("A" & lastRow).Value = Date.Today.ToString
            xlWorkSheet.Range("B" & lastRow).Value = lblClock.Text

**compgeek1979** · Mar 5th, 2015, 07:48 PM

@ Leary, I have stepped through the code and nothing is coming up indicating an error, however I dont see the physical excel form when i run my program. it always stays hidden. Should i have it displayed, and if so how can i do that?

The extra row of empty cells has been removed. Im just dealing with the first problem. Could it be the way the excel cells are formatted? "mmm dd yyyy"and my code in vb is not understading it?

**compgeek1979** · Mar 5th, 2015, 10:41 PM

So I have messed with the code as much as my knowledge will allow me to do and i am still not having any luck adding the lblClock value to the column 2 value if the column A value is today's date for that particular row.

Code:

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnStop.Click
        Timer1.Stop()
        Me.StopWatch.Stop()
        totalElapsedTime += Me.StopWatch.Elapsed
        Dim str(2) As String
        Dim xlApp As Excel.Application
        Dim xlWorkBook As Excel.Workbook
        Dim xlWorkSheet As Excel.Worksheet
        Dim lastRow As Long
        xlApp = New Excel.Application
        xlWorkBook = xlApp.Workbooks.Open(My.Computer.FileSystem.SpecialDirectories.Desktop & "\Time Sheet.xlsx")
        xlWorkSheet = xlWorkBook.Sheets("sheet1")
        xlWorkSheet = xlWorkBook.Sheets("sheet1")
        lastRow = xlWorkSheet.Range("A" & xlApp.Rows.CountLarge).End(Excel.XlDirection.xlUp).Row + 1

        Dim I As Integer = 1
        Dim DateFound As Boolean
        Dim DateRequired As Date = Date.Today

        Do
            If CDate(xlWorkSheet.Cells(I, 1).Value) = DateRequired Then
                DateFound = True
                Exit Do
            End If
        Loop Until xlWorkSheet.Cells(I, 1).Value = ""
        If DateFound = True Then
            xlWorkSheet.Cells(I, 2) = CDate(lblClock.Text)

        Else
            xlWorkSheet.Range("A" & lastRow).Value = Date.Today.ToString
            xlWorkSheet.Range("B" & lastRow).Value = lblClock.Text
        End If

        xlApp.DisplayAlerts = False
        xlWorkBook.Save()
        xlWorkBook.Close()
        xlApp.Quit()
        releaseObject(xlApp)
        releaseObject(xlWorkBook)
        releaseObject(xlWorkSheet)
        Me.StopWatch.Reset()
        lblClock.Text = "00:00:00"


    End Sub

**Leary222** · Mar 6th, 2015, 04:16 AM

you need to break on the line that says and work out why the two values that are being compared are not equal, you can also do this using a messagebox to show the values that are being compared

Code:

 If CDate(xlWorkSheet.Cells(I, 1).Value) = DateRequired Then

**TnTinMN** · Mar 6th, 2015, 08:36 AM

Just an observation. Here you indicate that the data starts on Row 14.

Originally Posted by compgeek1979

Code:

        'For Each cell As Excel.Range In xlWorkSheet.Range("A14:A" & lastRow)

And here you start your data scan on Row 1.

Originally Posted by compgeek1979

Code:

        Dim I As Integer = 1
        Dim DateFound As Boolean
        Dim DateRequired As Date = Date.Today

        Do
            If CDate(xlWorkSheet.Cells(I, 1).Value) = DateRequired Then

**compgeek1979** · Mar 6th, 2015, 11:17 AM

@TnTinMN - I tried to change that to no avail
@Leary - I put in a msg box as you will see in the code but it is not finding an entry

Code:

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnStop.Click
        Timer1.Stop()
        Me.StopWatch.Stop()
        totalElapsedTime += Me.StopWatch.Elapsed
        Dim str(2) As String
        Dim xlApp As Excel.Application
        Dim xlWorkBook As Excel.Workbook
        Dim xlWorkSheet As Excel.Worksheet
        Dim lastRow As Long
        xlApp = New Excel.Application
        xlWorkBook = xlApp.Workbooks.Open(My.Computer.FileSystem.SpecialDirectories.Desktop & "\Time Sheet.xlsx")
        xlWorkSheet = xlWorkBook.Sheets("sheet1")
        xlWorkSheet = xlWorkBook.Sheets("sheet1")
        lastRow = xlWorkSheet.Range("A" & xlApp.Rows.CountLarge).End(Excel.XlDirection.xlUp).Row + 1

        Dim I As Integer = 1
        Dim DateFound As Boolean
        Dim DateRequired As Date = Date.Today

        Do
            If CDate(xlWorkSheet.Cells(I, 14).Value) = DateRequired Then
                DateFound = True
                MsgBox("found entry for today")
                Exit Do
            Else
                MsgBox("no entry for today")
            End If
        Loop Until xlWorkSheet.Cells(I, 1).Value = ""
        If DateFound = True Then
            xlWorkSheet.Cells(I, 2) = CDate(lblClock.Text)

        Else
            xlWorkSheet.Range("A" & lastRow).Value = Date.Today.ToString
            xlWorkSheet.Range("B" & lastRow).Value = lblClock.Text
        End If

        xlApp.DisplayAlerts = False
        xlWorkBook.Save()
        xlWorkBook.Close()
        xlApp.Quit()
        releaseObject(xlApp)
        releaseObject(xlWorkBook)
        releaseObject(xlWorkSheet)
        Me.StopWatch.Reset()
        lblClock.Text = "00:00:00"

**TnTinMN** · Mar 6th, 2015, 01:43 PM

It would help if you could upload a sample workbook so that we can see the worksheet format. Make sure that you put it in a zip file first before trying to upload. The attach file option is available if you click on the "Go Advanced" button.

**compgeek1979** · Mar 6th, 2015, 01:59 PM

OK here it isTime Sheet - Copy.zip

**TnTinMN** · Mar 6th, 2015, 02:18 PM

Try changing:

Code:

Dim I As Integer = 1

To:

Code:

Dim I As Integer = 14

and

Code:

If CDate(xlWorkSheet.Cells(I, 14).Value) = DateRequired Then

To:

Code:

If CDate(xlWorkSheet.Cells(I, 1).Value) = DateRequired Then

**compgeek1979** · Mar 6th, 2015, 02:28 PM

It still does not see a current record in the date column and is writing a new record.

**TnTinMN** · Mar 6th, 2015, 03:08 PM

I did not look close enough at your code; there where several issues with it. The biggest is that you were not incrementing the row value ("I").
Without going into everything, give this a try.

Code:

Do
   If CDate(ws.Cells(I, 1).Value) = DateRequired Then
       DateFound = True
       Exit Do
   End If
   I += 1
   If I = lastRow Then
       Exit Do
   End If
Loop Until ws.Cells(I, 1).Value Is Nothing

If DateFound = True Then
   MsgBox("found entry for today")
   ws.Cells(I, 2) = CDate(lblClock.Text)
Else
   MsgBox("no entry for today")
   ws.Range("A" & lastRow).Value = Date.Today.ToString
   ws.Range("B" & lastRow).Value = CDate(lblClock.Text)
End If

**compgeek1979** · Mar 6th, 2015, 03:50 PM

Ok that worked HOWEVER, it overwrote the entry in column B for today's date with the current lblClock.text instead of adding lblClock.text to the value already in the cell

**TnTinMN** · Mar 6th, 2015, 03:55 PM

Originally Posted by compgeek1979

Ok that worked HOWEVER, it overwrote the entry in column B for today's date with the current lblClock.text instead of adding lblClock.text to the value already in the cell

So, retrieve that value first, perform your addition and write it out to Excel.

**compgeek1979** · Mar 7th, 2015, 02:39 AM

Originally Posted by TnTinMN

So, retrieve that value first, perform your addition and write it out to Excel.

So would I have to store the value into a db or something then do the addition our can I just do:
ws.cells(I,2)=ws.cells(I,2)+lblClock.text

**compgeek1979** · Mar 7th, 2015, 01:54 PM

I did try this based on some things i found online but it is not working. it just adding at the end of the cell like this "0:0012:00:01 AM12:00:01 AM" instead of like this 0:00:02

Code:

xlWorkSheet.Cells(I, 2) = xlWorkSheet.Cells(I, 2).text + CDate(lblClock.Text)

Ive tried
xlWorkSheet.Cells(I, 2) = xlWorkSheet.Cells(I, 2).value + CDate(lblClock.Text) with the same result. So i guess my question is how can i fix this issue. how can i get the value of the cell then add the lblCLock.text to that value then paste it back into the excel sheet at that location>

**TnTinMN** · Mar 7th, 2015, 02:49 PM

I am not going to give working code, because you appear to only be interested in getting your current problem solved without understanding why it works. You have been asking questions on this forum for over two years, yet you are still trying to perform numeric operations on strings. This is something most people learn not to do right at the beginning of learning to code. Take the time to learn the basics. There are plenty of online tutorials available.

Do yourself a favor and enable Option Strict for your code (see: http://www.vbforums.com/showthread.p...1#post4755291) . This will allow VS to help you use the proper data types.

Hint: The Excel Range.Value property returns an Object type; you need to convert it to the proper .Net type before you can perform reliable operations with it.

**compgeek1979** · Mar 7th, 2015, 03:17 PM

I agree that i am interested in resolving my current problem, BUT I DO want to know why it works. I am not a programmer and I do not do this for a living. I learn from my projects best. Meaning i cant read a how to out of a generic book and understand how to relate it very well to my current issue. That's why i am having such a hard time. I have been trying to understand what i find but cant relate it. I have some medical issue that prevent me from learning/understanding things the same way most others do. I do thank you for the time that you have taken to respond to my questions. I will try to get some additional help in understanding why my code is not working and some direction on how to make it work so that I will be in a position to be able to take from this project and apply said learned concepts to any future projects I may have.

Also as you can tell there are several months between posts meaning don't do this all the time. If i did, i would obviously know more.

**compgeek1979** · Mar 8th, 2015, 03:30 AM

Ok after searching most of the day, I have come to the conclusion that I am no better off than when I started. Most tutorials are on using ado or odbc connections. Do I really need to do this? Or can I just do new value =cellcurrentvalue.tostring + lblclock.text. then write it. Do I need to add a dataset to my project? It sucks because this is the last piece of my project and I can't figure it out. Please help!

**Leary222** · Mar 8th, 2015, 03:41 PM

all the information you require is above all you need to do is add two times together google how to do this, even though it is above, and your problem should be solved

**compgeek1979** · Mar 8th, 2015, 04:08 PM

ok well how to i convert the range to string?
heres what i have so far.

Code:

 If DateFound = True Then
            Dim ts1 As TimeSpan = TimeSpan.Parse(xlWorkSheet.Cells(I, 2))
            Dim ts2 As TimeSpan = TimeSpan.Parse(lblClock.Text)
            Dim tsSum As TimeSpan = ts1 + ts2
            MessageBox.Show(tsSum.ToString)

**Leary222** · Mar 8th, 2015, 04:12 PM

so does the messagebox show the value you want to be in the cell?

**compgeek1979** · Mar 8th, 2015, 04:17 PM

no i get an error on line

Code:

Dim ts1 As TimeSpan = TimeSpan.Parse(xlWorkSheet.Cells(I, 2))

Conversion from type 'Range' to type 'String' is not valid.

**Leary222** · Mar 8th, 2015, 04:25 PM

what Type do you think 'xlWorkSheet.Cells(I, 2)' returns because it certainly isn't a string

try 'cStr(xlWorkSheet.Cells(I, 2).Value)'

**compgeek1979** · Mar 8th, 2015, 04:29 PM

so that line is cStr is creating the string from the xlWorkSheet.Cells(I, 2).Value?
if i understand that properly then i replaced

Code:

Dim ts1 As TimeSpan = TimeSpan.Parse(xlWorkSheet.Cells(I, 2))

with

Code:

Dim ts1 As TimeSpan = TimeSpan.Parse(CStr(xlWorkSheet.Cells(I, 2).Value))

which is incorrect because im getting an error
"The TimeSpan could not be parsed because at least one of the numeric components is out of range or contains too many digits"

**Leary222** · Mar 8th, 2015, 05:00 PM

what is the value in cell I,2 ?

**compgeek1979** · Mar 9th, 2015, 12:00 AM

Well right not that column is formatted as h:mm but clicking on that cell shows "4:00:00 AM" which i havent figured out how to get rid of the am yet.

**Leary222** · Mar 10th, 2015, 04:09 PM

Right I have had a play with this myself and here's what I've come up with

Code:

Dim I as Integer = 1 'or two depending if your excel sheet has headers
Dim Datefound As Boolean = False
Dim DateRequired as date = today.date
Do
If cdate (xx worksheet.Cells (I, 1).Text) = DateRequired then
Datefound = true
Xlworksheet.cells (I, 2).Value = date.parse (Xlworksheet.cells (I, 2).text).addticks (yourstopwatch.elapsedticks).tostring ("hh:mm:ss")
Exit do
End If
I +=1
Loop until Xlworksheet.cells(I, 1).Text = "" 'blank cells
If datefound = true 
Xlworksheet.cells(I, 1).value = date.today.tostring("dd/MM/yyyy")
Xlworksheet.cells(I, 2).value = date.today.date.addticks(your stopwatch.elapsedticks).tostring ("hh:mm:ss tt")
End If

Thread: How to update a record in excel

Thread Tools

Display

How to update a record in excel

Re: How to update a record in excel

Re: How to update a record in excel

Re: How to update a record in excel

Re: How to update a record in excel

Re: How to update a record in excel

Re: How to update a record in excel

Re: How to update a record in excel

Re: How to update a record in excel

Re: How to update a record in excel

Re: How to update a record in excel

Re: How to update a record in excel

Re: How to update a record in excel

Re: How to update a record in excel

Re: How to update a record in excel

Re: How to update a record in excel

Re: How to update a record in excel

Re: How to update a record in excel

Re: How to update a record in excel

Re: How to update a record in excel

Re: How to update a record in excel

Re: How to update a record in excel

Re: How to update a record in excel

Re: How to update a record in excel

Re: How to update a record in excel

Re: How to update a record in excel

Re: How to update a record in excel

Re: How to update a record in excel

Re: How to update a record in excel

Re: How to update a record in excel

Re: How to update a record in excel

Re: How to update a record in excel

Re: How to update a record in excel

Re: How to update a record in excel

Posting Permissions