Error 1004: Method "ChartWizard' of object '_Chart' failed

[tranchemontaigne]

Error 1004: Method "ChartWizard' of object '_Chart' failed

I get this error when I try to run a code snippet for creating a graph in MS Excel. Not surprisingly, Using the MS Access VBA Object Browser, I find that the method Chart Wizard is present within the Excel library Chart Class.

I also find that there are 6 Visual Basic for Applications libraries on my Windows XP SP3 computer.

c:\windows\system32\msvbvm60.dll
c:\windows\system32\vbaen32.olb
c:\windows\system32\vbaend32.olb
c:\windows\system32\msvbvm50.dll
c:\windows\system32\ven2232.olb

The library that is in use may be found along the following path:

C:\program files\common files\microsoft shared\vba\vba6\(file name begins with VB, but is truncated)

Any thoughts on how to configure MS Access to use a different VBA library? Alternately, does anyone have thoughts on how to fix this error?

For reference, I'm attaching a copy of the the code I am reviewing exactly as it was published in Alison Balter's book "Mastering Microsoft Access 2000 Development" (ISBN: 0-0672-31484-3). I have already searched the web for an errata on this book to see if there is a known fix and have found none.

Private Sub cmdCreateGraph_Click()
    On Error GoTo cmdCreateGraph_Err
    Dim rstData As ADODB.Recordset
    Dim rstCount As ADODB.Recordset
    Dim fld As ADODB.Field
    Dim rng As Excel.Range
    Dim objWS As Excel.Worksheet
    Dim intRowCount As Integer
    Dim intColCount As Integer
 
    'Display Hourglass
    DoCmd.Hourglass True
    Set rstData = New ADODB.Recordset
    rstData.ActiveConnection = CurrentProject.Connection
    Set rstCount = New ADODB.Recordset
    rstCount.ActiveConnection = CurrentProject.Connection
 
    'Attempt to create Recordset and launch Excel
    If CreateRecordset(rstData, rstCount, "qrySalesByCountry") Then
        If CreateExcelObj() Then
            gobjExcel.Workbooks.Add
            Set objWS = gobjExcel.ActiveSheet
            intRowCount = 1
            intColCount = 1
 
            'Loop though Fields collection using field names
            'as column headings
            For Each fld In rstData.Fields
                If fld.Type <> adLongVarBinary Then
                    objWS.Cells(1, intColCount).Value = fld.Name
                    intColCount = intColCount + 1
                End If
            Next fld
 
            'Send Recordset to Excel
            objWS.Range("A1").CopyFromRecordset rstData, 500
 
 
            'Format Data
            With gobjExcel
                .Columns("A:B").Select
                .Columns("A:B").EntireColumn.AutoFit
                .Range("A1").Select
                .ActiveCell.CurrentRegion.Select
                Set rng = .Selection
                .Selection.NumberFormat = "$#,##0.00"
 
                'Add a Chart Object
                .ActiveSheet.ChartObjects.Add(135.75, 14.25, 607.75, 301).Select
 
                'Run the Chart Wizard
                .ActiveChart.ChartWizard Source:=Range(rng.Address), _
                    Gallery:=xlColumn, _
                    Format:=6, PlotBy:=xlColumns, CategoryLabels:=1, SeriesLabels _
                    :=1, HasLegend:=1, Title:="Sales By Country", CategoryTitle _
                    :="", ValueTitle:="", ExtraTitle:=""
 
                'Make Excel Visible
                .Visible = True
            End With
        Else
            MsgBox "Excel Not Successfully Launched"
        End If
    Else
        MsgBox "Too Many Records to Send to Excel"
    End If
    DoCmd.Hourglass False
 
cmdCreateGraph_Exit:
    Set rstData = Nothing
    Set rstCount = Nothing
    Set fld = Nothing
    Set rng = Nothing
    Set objWS = Nothing
    DoCmd.Hourglass False
    Exit Sub
 
cmdCreateGraph_Err:
    MsgBox "Error # " & Err.Number & ": " & Err.Description
    Resume cmdCreateGraph_Exit
End Sub

________
V12 Engine