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
Loop
'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
xlapp.Quit
'quit Excel
Set xlsheet = Nothing
Set xlbook = Nothing
Set axlapp = Nothing
'clean up
UserForm1.Hide
'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
Else
ThisDrawing.Save
'if Yes save the drawing
End If
End If
ThisDrawing.Application.Quit
'close the drawing and exit AutoCAD
End Sub
Private Sub CommandButton2_Click()
End
'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
UserForm1.TextBox1.SetFocus
'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 ()
userform1.show
End Sub
|