Access.2007.VBA.Bibl..

Get the save name from workbook’s Title property:

Set prps = _

appExcel.ActiveWorkbook.BuiltinDocumentProperties

strSaveName = strWorksheetPath & prps(“Title”) _

& “ - “ & Format(Date, “d-mmm-yyyy”)

Debug.Print “Worksheet save name: “ & strSaveName

On Error Resume Next

If there already is a saved worksheet with this name, delete it:

Kill strSaveName

On Error GoTo ErrorHandler

strPrompt = “Enter file name and path for saving worksheet”

strTitle = “File name”

strDefault = strSaveName

strSaveName = InputBox(prompt:=strPrompt, _

Title:=strTitle, Default:=strDefault)

wkb.SaveAs FileName:=strSaveName, _

FileFormat:=xlWorkbookDefault

appExcel.Visible = True

ErrorHandlerExit:

Exit Sub

ErrorHandler:

If Err = 429 Then

Excel is not running; open Excel with CreateObject:

Set appExcel = CreateObject(“Excel.Application”)

Resume Next

Else

MsgBox “Error No: “ & Err.Number _

& “; Description: “ & Err.Description

Resume ErrorHandlerExit

End If

End Sub

The procedure first picks up the worksheet template path from the main menu, checks that the

template is to be found in that location, and then creates a new workbook from the template. It

then sets up a recordset based on an Access query, goes to the first data cell in the worksheet, and

starts iterating through the records in the recordset, using the Offsetmethod of the active cell to

place data from each field in the correct column.

Part II Writing VBA Code to Exchange Data between Office Components