Access & Excel Interoperabilit y woes

[AlexNunley]

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
 
End Sub