ANmarS - Search results

Excel/VBA expert since Sep 1997 (21 years 2 months)

We are moving ...

We are moving to another location, better, faster and more resources .... check it out ANmar.Systems

1 Q & A posts tagged with clock

Fri
Apr 2
2010

How can I put a private sub within a private sub?

I am working with visual basic 2008 and I want to create a timelog within excel but have the clock in and clock out within the same worksheet. I got it to work but the times are in different worksheets or should i say different books. I just need to know how to put them together here is the code:
Thanks

Public Class Form1


Private Sub Timer1_Tick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Timer1.Tick
Label1.Text = TimeOfDay

End Sub


Private Sub ClockIn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ClockIn.Click

Dim Time As String = Now()

Dim Name As String = TextBox1.Text()


'writes time to label on form

Label2.Text = Time

Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object

'Start a new workbook in Excel
oExcel = CreateObject("Excel.Application")
oBook = oExcel.worksheet.Add


'Add data to cells of the first worksheet in the new workbook
oSheet = oBook.Worksheets(1)
oSheet.Range("A1").Value = Name
oSheet.Range("A1").Font.Bold = True
oSheet.Range("B1").Value = "Clock In"
oSheet.Range("B1").Font.Bold = True

'Adding the time
oSheet.Range("B2").Value = Time


End Sub


Private Sub ClockOut_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ClockOut.Click
Dim Time As String = Label2.Text

Dim Time2 As String = Now()

Dim Name As String = TextBox1.Text()

'writes time to label on form

Label3.Text = Time2


Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object

'Start a new workbook in Excel
oExcel = CreateObject("Excel.Application")
oBook = oExcel.Worksheet.Add


'Add data to cells of the first worksheet in the new workbook
oSheet = oBook.Worksheets(1)

oSheet.Range("C1").Value = "Clock Out"
oSheet.Range("C1").Font.Bold = True

'Adding the time
oSheet.Range("C2").Value = Time2
'Save the Workbook and Quit Excel
oBook.SaveAs("C:\Book1.xls")
oExcel.Quit()
End Sub


End Class


Answer

First of all, your question is confusing

You can not put sub inside another
Yes, you can call it from the other one, but not put it there

As far as I understood your question, here is my suggested answer

You need to Open that Excel file first, then modify it, then save it
What you are doing now is creating new workbook every ClockIn/ClockOut

I recommend putting these three in one workbook, call it main.xls for example
And open one workbook (already created) , add new rows for timein/timeout, save it and close, instead of creating new one every time

VBAXLMan

Friday 4/2/2010 5:44:51 PM

Tags

History