As a quick way to find out the syntax for various Excel commands, open an Excel work-
sheet, turn on the macro recorder, perform the actions, and then save the macro. Open
the saved macro and copy the code to your Access procedure; with a little trimming of redundant
arguments and editing to insert your variable names, it should work fine.Timesheets ........................................................................................................................
Almost any type of business (other than a one-person operation) needs a form for recording
employees’ work hours and a way to print or electronically distribute the timesheet data. Often a
company has used a paper form to record work hours for many years, and the electronic form
needs to replicate the paper form. In some cases, there are specific government or industry stan-
dard formats that must be used, or the data must be produced in a format that can be imported by
a mainframe computer. You can use a preformatted Excel worksheet template to produce
timesheets in the exact format you need and fill them with data from Access.One example of using timesheets in such a fashion is an engineering firm whose employees work
on various projects for the company’s clients. Because the employees’ work hours (except for those
assigned to internal projects) will be billed back to the clients, in this case a separate worksheet is
needed for each employee’s work on a specific project per week, so a single employee might have
several timesheets in a week. In the case of (for example) a scientific research establishment, where
hours are not billed out to clients, one timesheet per employee, listing multiple projects in a week,
would be more appropriate.The form frmWeeklyTimesheet (shown in Figure 7.9) is an Access front end for entering timesheet
data that will be exported to Excel timesheets. This form lets you select an employee, client, and
project, and fill in a timesheet for that employee. The assumption is that a separate timesheet is done
for each client/project combination, so an employee can have multiple timesheets for a given week.The cboEmployeeID combo box’s row source is a union query that combines data from two
queries: qryThisWeeksTimesheets, which lists the timesheets that have been filled in so far
this week, and qryNeedTimesheets, which lists the employees who have not yet filled out a
timesheet for this week. The resulting list displays all the employees, showing the timesheets that
have been filled out so far, as illustrated in Figure 7.10.TIPTIP
Part II Writing VBA Code to Exchange Data between Office Components