Access & Excel Interoperability 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------------

Expand|Select|Wrap|Line Numbers

 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

Access & Excel Interoperability woes

Post your reply

Similar topics