Company Report
A company like your desktop publishing company needs to keep a lot of records and it generates a lot of paperwork. It's a good idea to automate as many of the paperwork chores as possible so that you can get to the actual desktop publishing (the fun stuff) as quickly and efficiently as possible. There are many Office Automation tools to help with this task, but Word itself can handle some of them. In this project you will use Word to keep track of who is on the job each day, when they came and left, which computer they used, and what they were up to.
Instructions
Since we all have access to the same kind of computer with the same software installed, we will begin the project as a "let's all click together" exercise. The example Company Report shows data being collected for each of four different employees. We will do at least one employee together and then you can do the remaining three with progressively less help. In case you need written instructions for reference, here are some of the key steps:
Create a new Word document and before it is given some strange name, save it as CompanyReport. It may be either a .doc or .docx (or .docm) file now that your classmates and coworkers are able to read both kinds. You will probably want to have the Forms toolbar handy for many of the following steps. You may have to turn off Protect Form in order to manipulate fields.
Make a title that includes your company name and then center it. Under that add a text form field with Type of Date. Add value by specifying a Date format of "dddd, M/d/yyyy" so that the day of the week appears next to the numbers when they are entered. This helps you verify that the date was entered correctly.
Note: Word 2007 and 2010 both have a bug which causes them to forget the format. So, we'll have to remind it (one of those particular Word versions) in a way that won't be forgotten. Leave the Format blank and instead add a macro to run on Exit: UpdateDate. Add this code to the Visual Basic code that you download in a subsequent step:
Public Sub UpdateDate()
With ActiveDocument.FormFields("Date")
.Result = Format(.Result, "dddd, M/d/yyyy")
End With
End Sub
Insert a new table of six columns and nine rows. In the top row insert, bold, and center the headings Name, Present, Laptop, In, Out, and Duration.
In the leftmost column under Name, insert a Dropdown Form Field. Right click the field and choose Properties. Add the names of all your coworkers and also leave some option for nobody, such as a dash or blank.
In the Present column insert two Check Box Form Fields. Add Yes and No texts so that users know what the boxes mean.
The Laptop column gets another Dropdown Form Field. This time add items 1-24 plus something to indicate none.
In and Out are both Text Form Fields. They are both of type Date and have a Date format of "h:mm am/pm" Give these fields a Bookmark of TimeIn1 and TimeOut1. Spell them exactly as written, since the program needs to access them by name. An additional option will need to be specified when you are ready to add the code for the duration calculations.
Note: Again, Word 2007 and 2010 have difficulties remembering the format. Skip specifying a format for these newer version of Word and instead modify the code that you use in a subsequent step. To the beginning of UpdateTimeDif right after the three Dim statements, add the following code:
With ActiveDocument.FormFields(inName)
Result = Format(.Result, "h:mm am/pm")
End With
With ActiveDocument.FormFields(outName)
.Result = Format(.Result, "h:mm am/pm")
End With
There is no Form Field in the Duration column. It is instead a DocVariable field that is found near where the MailMerge fields are located. In Word 2003 you would Insert a Field. Choose DocVariable and give it a name of DurTime1. The program will use this variable, so spell it exactly as written here.
In the following line of the table enter the text "Report: " and then another Text Form Field. This one should have the Type of Regular text and include a Maximum length so that employees are forced to be concise.
The report row should be able to extend across the entire row of the table, so select all six of the cells in the row and merge them. In Word 2003 there is a Merge Cells menu item in the Table menu for this.
Repeat the insertion of fields and labels for your other three employees. Most parts can be exactly the same and some copy and past may work. However, the Bookmarks and DocVariables must be numbered from 1 as above to 4. You will have, for instance, DurTime1, DurTime2, DurTime3, and DurTime4 in the end. Remember to save your work periodically.
Press the Protect Form button on the toolbar and test the controls. You should see coworker names, Xs in checkboxes, laptop numbers, dates, times, and reports. When that is all working, you are ready to add the program that performs the calculation.
Most of the code has been written for you. When you click on the link, the text may show directly in your browser from which you can copy it. Otherwise, download it and open it with Notepad and copy it from there. It needs to be pasted into the Visual Basic Editor. There is a menu item for the editor in the Tools menu under Macro in Word 2003. In the window that opens there should be a tree in the upper left corner. A Project exists with the name of your file. Right click on it and Insert a Module. The (Name) of the module is listed in the lower left and a new window appears. Replace the name Module1 with UpdateDuration and paste the code into the new window.
Note: On 12/5/2010 the code ActiveDocument.Fields.Update was added as the last line (just before End Sub) of UpdateTimeDif. Add this line or download the revised file if yours doesn't include this code.
Notice the bottom Sub(routine) called UpdateDuration1. This will update your DocVariable called DurTime1 when it is run. To make that happen, you need to modify your form fields. Right click on the Text Form Field that you gave the bookmark TimeIn1 to and open the Properties (in Word 2003). For "Run macro on Exit" specify the UpdateDuration1 macro which should appear in the drop-down list. You will want to protect the form and do some testing. The duration column should be filled in when you exit the time out and time in fields, which usually happens when the user presses the tab key.
When the calculation works for the first employee, return to the Microsoft Visual Basic window and duplicate (via copy and paste) UpdateDuration1 to UpdateDuration2, UpdateDuration3, and UpdateDuration4. You will have to modify some of the information inside the subroutines to use the names of your other form fields.
After the new macros are in place, you can return to the time in and time out columns for the other three employees and configure the new macros to run on exit. Be sure to test them all.
When you are finished, congratulate yourself. You have just automated the office. Feel free to customize the document. Add your company logo to the form, change colors, use special fonts, etc.
Note: Word has a feature (bug) that might show itself in the Duration column. If you see there the message "Error! No document variable supplied!" even though your DocVariable is named, then open up the Visual Basic Editor and View the Immediate Window. There enter these two commands using the DocVariable name for the rows where the error message appears (in this case row 2):
ActiveDocument.Variables("DurTime2").Value="Hi"
ActiveDocument.Fields.Update
Delivery
When you are all finished, read the checklist below and verify that all items are present and functional. Make sure that your form is not protected with a password. Such files will not be graded and you will lose a day finding that out. Email the file to the instructor or transfer it to the designated computer via a USB drive.
Grading
Be certain to include all of the items below in your work. Also feel free to customize the form. Colors, backgrounds, special fonts, additional fields, and more are encouraged.
Title with company name
Date with day of week
Table with room for four entries
6 headers (or more)
Special header style (such as centered and bold)
Enough rows to contain data for four employees
Name field with four company worker names and one empty/blank
Yes and No checkboxes
Laptop field with room for 24 laptops and unspecified
In field, properly typed, formatted, and bookmarked
Out field, properly typed, formatted, and bookmarked
Duration field, a DocVariable, with proper name
Report field with limited length
Merged table cells
Macro configured to run on exit
Module UpdateDuration with code
Additional macros added for other three document variables
Tested functionality
+Customization for extra credit
CompanyReport.doc or CompanyReport.docx or CompanyReport.docm submitted