Access.2007.VBA.Bibl..

Get the template path that was selected on the main menu:

strTemplate = _

“Weekly time sheet by client and project.xlt”

strTemplatePath = GetWorksheetTemplatesPath()

strTemplateFile = strTemplatePath & strTemplate

If TestFileExists(strTemplateFile) = False Then

strTitle = “Template not found”

strPrompt = “Excel template “ _

& “‘Weekly time sheet by client and project.xlt’” _

& “ not found in “ & strTemplatePath & “;” _

& vbCrLf _

& “please put template in this folder and try again”

MsgBox strPrompt, vbCritical + vbOKOnly, strTitle

GoTo ErrorHandlerExit

Else

Debug.Print “Excel template used: “ _

& strTemplateFile

End If

Get the path for saving workbooks:

strDocsPath = GetWorksheetsPath()

Set a reference to the Excel Application object for use in creating workbooks:

Set appExcel = GetObject(, “Excel.Application”)

Do While Not rstAll.EOF

Create a recordset of hours for this employee:

lngEmployeeID = rstAll![EmployeeID]

strEmployeeName = rstAll![EmployeeName]

dteWeekEnding = CDate(rstAll![WeekEnding])

strQuery = “qfltHours”

strSQL = “SELECT * FROM qryCurrentTimesheetInfo “ _

& “WHERE [EmployeeID] = “ & lngEmployeeID & “;”

Debug.Print “SQL for “ & strQuery & “: “ & strSQL

lngCount = CreateAndTestQuery(strQuery, strSQL)

Debug.Print “No. of items found: “ & lngCount

If lngCount = 0 Then

MsgBox “No items found; canceling”

GoTo ErrorHandlerExit

End If

Set rstOne = dbs.OpenRecordset(strQuery, _

dbOpenDynaset)

With rstOne

Part II Writing VBA Code to Exchange Data between Office Components