AfraLisp Blog

The AutoLisp/Visual Lisp/VBA Resource Website

AfraLisp Blog

Home Newsletter Utter Rubbish Coding Tips AutoCAD Tips Contact Downloads WAUN

Timesheet Project (28 Kb)


This project demonstrates a method of extracting system variable data from AutoCAD and importing it into Microsoft Excel.
The application calculates the length of time a user has spent in a drawing session and extracts this data to a timesheet created in Excel. It then totals the daily drawing hours along with with the date, the draughtsman's login name, the drawing number and the job number.

To use this VBA application, simply extract all of the files into your working
directory then load and run Timesheet.Lsp.

(If you have problems when writing or running this project, ensure that
the Microsoft Excel 8.0 Object Library is selected in your VBA References.)

After running the application, open the Excel file Timesheet.xls. You should have something looking like this :


Here is the source coding for this application. 

Public ElapseTime As String
Public Dwgname As String
Public Username As String
'declare global variables

Private Sub CommandButton1_Click()

Dim xlapp As Excel.Application
Dim xlbook As Excel.Workbook
Dim xlsheet As Excel.Worksheet
Dim Colnum As Integer
Dim Rownum As Integer
Dim Currcell As Range
'declare local variables

Set xlbook = GetObject("Timesheet.xls")
'set reference to Excel file

Set xlapp = xlbook.Parent
'set reference to workbook

xlapp.Visible = True
'make Excel visible

xlapp.Windows("TIMESHEET.XLS").Visible = True
'make the workbook visible
'the 2 preceeding lines can be commented out if you
'do not want to see Excel in action

Set xlsheet = xlbook.Sheets("SHEET1")
'set reference to the worksheet Sheet1

Rownum = 10
'set initial row number

Colnum = 3
'set initial column number

Set Currcell = xlsheet.Cells(Rownum, Colnum)
'position the current cell

Do While Currcell <> ""
'while the current cell is NOT empty

    Rownum = Rownum + 1
    'increase the row number by one
    Set Currcell = xlsheet.Cells(Rownum, Colnum)
    'reset the current cell
'carry on looping

xlsheet.Cells(Rownum, Colnum) = Format(ElapseTime, "h:m:s")
'enter the elapsed time

xlsheet.Cells(Rownum, (Colnum - 1)) = UCase(Dwgname)
'enter the drawing number

xlsheet.Cells(Rownum, (Colnum - 2)) = UCase(UserForm1.TextBox1.Text)
'enter the job number

xlsheet.Cells(5, 2) = UCase(Username)
'enter Login Name

xlsheet.Cells(3, 2) = Date
'enter the date

xlbook.Close savechanges:=True
'save the changes in Excel

'quit Excel

Set xlsheet = Nothing
Set xlbook = Nothing
Set axlapp = Nothing
'clean up

'hide the dialog box

If Not ThisDrawing.Saved Then
'if the current drawing is not saved

    If MsgBox("    OK to Save Drawing?", 4) = vbNo Then
    'do nothing
        'if Yes save the drawing
    End If
End If

'close the drawing and exit AutoCAD

End Sub

Private Sub CommandButton2_Click()

'end the application

End Sub

Private Sub UserForm_Initialize()

ElapseTime = ThisDrawing.GetVariable("TDUSRTIMER")
'get the elapsed time

Dwgname = ThisDrawing.GetVariable("DWGNAME")
'get the drawing number

Username = ThisDrawing.GetVariable("LOGINNAME")
'get the login name

UserForm1.TextBox2.Text = UCase(Dwgname)
'display the drawing number

UserForm1.TextBox3.Text = Format(ElapseTime, "h:m:s")
'display the elapsed time

UserForm1.TextBox4.Text = UCase(Username)
'display the login name

UserForm1.TextBox5.Text = Date
'display the date

UserForm1.TextBox1.Text = "Job No"
'display a default Job No value

'set the focus to the Job No text box

UserForm1.TextBox1.SelStart = 0
'select the start of the text

UserForm1.TextBox1.SelLength = Len(UserForm1.TextBox1.Text)
'highlight the text

End Sub 

You must also create a new module: 
Sub timesheet ()
End Sub
The AutoLisp/Visual Lisp/VBA Resource Website

Copyright 1999-Perpetuity by AfraLisp

All rights reserved.
Information in this document is subject to change without notice.
Site created and maintained by Kenny Ramage

The AutoLisp/Visual Lisp/VBA Resource Website