Access.2007.VBA.Bibl..

Dim strPrompt As String

Dim strDataRange As String

Dim strRange As String

Dim strSaveName As String

Dim strSheetName As String

Dim strStartAddress As String

Dim strTemplate As String

Dim strTemplateFile As String

Dim strTitle As String

Dim wkb As Excel.Workbook

Dim wks As Excel.Worksheet

Create a new worksheet from the template and export data to it:

strDBPath = Application.CurrentProject.Path & “\”

Debug.Print “Current database path: “ & strDBPath

strTemplate = “Northwind Orders.xltx”

strTemplateFile = strDBPath & strTemplate

If TestFileExists(strTemplateFile) = False Then

Put up a message and exit if the template is not found:

strTitle = “Template not found”

strPrompt = “Excel template ‘Northwind Orders.xlt’” _

& “ not found in “ & strDBPath & “;” & 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

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

Set dbs = CurrentDb

Create a recordset based on the Access query:

Set rst = dbs.OpenRecordset(“qryOrdersAndDetails”)

Create a new worksheet based on the template:

Set wkb = appExcel.Workbooks.Add(strTemplateFile)

Set wks = wkb.Sheets(1)

wks.Activate

appExcel.Visible = True

Go to the first data cell in the worksheet:

Set rngStart = wks.Range(“A4”)

Set rng = wks.Range(“A4”)

Analyzing Data with Excel 3