Access and Excel Interoperability Woes

[alex....@gmail.com]

I am trying to dump data from an access query into excel so I can
pretify it. I found sample code from an old access 97 book (The
version of access in use)and tinkered it into working.

Well sort of. The error I get is usually a "Object Required" error,
and it highlights the bolded section below. I have been through this
thing backwards and forth and cannot figure it out. I am new to this
whole thing, so be easy on me.

Thanks!

Alex

----------code pasted below------------

Private Const conQuery = "alexpartcountrial"
Private Const conSheetName = "Part Count"

Public Sub CreateExcelChart()

Dim rst As ADODB.Recordset

' Excel object variables
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim xlChart As Excel.Chart

Dim i As Integer

' Create Excel Application object.
Set xlApp = New Excel.Application

' Create a new workbook.
Set xlBook = xlApp.Workbooks.Add

' Get rid of all but one worksheet.
xlApp.DisplayAlerts = False
For i = xlBook.Worksheets.Count To 2 Step -1
xlBook.Worksheets(i).Delete
Next i
xlApp.DisplayAlerts = True

' Capture reference to first worksheet.
Set xlSheet = xlBook.ActiveSheet

' Change the worksheet name.
xlSheet.Name = conSheetName

' Create recordset.
Set rst = New ADODB.Recordset
rst.Open Source:=conQuery, ActiveConnection:=CurrentProject.Connection

With xlSheet
' Copy field names to Excel.
' Bold the column headings.
With .Cells(1, 1)
.value = rst.Fields(0).Name
.Font.Bold = True
End With
With .Cells(1, 2)
.value = rst.Fields(1).Name
.Font.Bold = True
End With

' Copy all the data from the recordset
' into the spreadsheet.
.Range("A2").CopyFromRecordset rst

' Format the data.
.Columns(1).AutoFit
.Columns(2).AutoFit
.Columns(3).AutoFit

End With

' Create the chart.
Set xlChart = xlApp.Charts.Add
With xlChart
.ChartType = xl3DBarClustered
.SetSourceData xlSheet.Cells(1, 1).CurrentRegion
.PlotBy = xlColumns
.Location _
Where:=xlLocationAsObject, _
Name:=conSheetName
End With

' Setting the location loses the reference, so you
' must retrieve a new reference to the chart.
With xlBook.ActiveChart
.HasTitle = True
.HasLegend = False
With .ChartTitle
.Characters.Text = conSheetName & " Chart"
.Font.Size = 16
.Shadow = True
.Border.LineStyle = xlSolid
End With
With .ChartGroups(1)
.GapWidth = 20
.VaryByCategories = True
End With
.Axes(xlCategory).TickLabels.Font.Size = 8
.Axes(xlCategoryScale).TickLabels.Font.Size = 8
End With

' Display the Excel chart.
xlApp.Visible = True

ExitHere:
On Error Resume Next
' Clean up.
rst.Close
Set rst = Nothing
Set xlSheet = Nothing
Set xlBook = Nothing
Set xlApp = Nothing
Exit Sub