Access.2007.VBA.Bibl..

Dim sht As Excel.Worksheet

Dim wkb As Excel.Workbook

Dim rng As Excel.Range

Dim strTable As String

Dim strRange As String

Dim strSaveName As String

Dim strPrompt As String

Dim strTitle As String

Dim strDefault As String

On Error GoTo ErrorHandler

Re-create table for export:

strTable = “tmakAccountSummary”

DoCmd.SetWarnings False

DoCmd.OpenQuery “qmakAccountSummary”

Create worksheet save name:

strWorksheetPath = GetWorksheetsPath()

strWorksheet = “Account Summary”

strSaveName = strWorksheetPath & strWorksheet & “.xls”

Debug.Print “Worksheet save name” & strSaveName

On Error Resume Next

Delete existing worksheet (if there is one):

Kill strSaveName

On Error GoTo ErrorHandler

Export query data to a new worksheet in Excel 9 format:

DoCmd.TransferSpreadsheet transfertype:=acExport, _

spreadsheettype:=acSpreadsheetTypeExcel9, _

TableName:=strTable, FileName:=strSaveName, _

hasfieldnames:=True

Open the newly created worksheet and insert title material:

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

appExcel.Workbooks.Open (strSaveName)

Set wkb = appExcel.ActiveWorkbook

Set sht = appExcel.ActiveSheet

sht.Activate

With sht

Part II Writing VBA Code to Exchange Data between Office Components