[AccessD] Access to Excel Automation works first time

I've run into an Excel automation problem.  It seems like one of those nagging failure to release an object reference problems.

What happens is, the first time I run the code, it works perfectly.  If I call the procedure a second time, it errors.  If I then close the Access application, reopen it and then try the code again, it triggers the error.  If I close the access application by running End and then Quit from the immediate window, the code again works the first time I try it after reopening the Access application.

The code worked perfectly until I added some cleaned up Excel macro recorder code to the Access code that inserted a chart.  If I comment out the chart code, the automation code runs correctly every time.  The code uses a preformatted Excel template and writes a table of information to sheet 2 and then diplays a table based on that data on the first sheet.

Every object that uses a set statement is set to nothing in the ExitRoutine portion of the error handler in the code.  I can only assume there is some implicit instantiation of an Excel object that I'm not seeing.  Excerpted below, skipping the part that writes the data, is the part of the code that blows up on the 4 th line below where the Source of chart data is set by calling the SetSourceData method of the Chart object variable.  I tried it with a workbook chart object as well as the application chart object and it errors at the same point.  I also replaces all the oxlApp.ActiveChart with:  With oxlCht and with:  With oxlapp.ActiveChart versions of the code.  Same story every way I try it.  The code works the first time and then there is an error message:  Method 'SetSourceData' of object'_Chart' failed.  By the way, I needed to decrease the margins in code because if I don't, a portion of the x axis labeling is cut off.  If I insert the chart with the wider margins and decrease them in the code, the axis numbers are correctly visible.  What am I missing to get this to run without forcing a code end/quit?
 
Private Sub cmdCashFlowSummary_Click()
    On Error GoTo ErrorHandler
 
    Dim oxlApp As Excel.Application
    Dim oxlWkb As Excel.Workbook
    Dim oxlCht As Excel.Chart

    oxlApp.Sheets("Forecast").Activate
    Set oxlCht = oxlApp.Charts.Add
    oxlCht.ChartType = xlLineMarkers
      'Need C3 to C28 but will delete SeriesCollection(1)
    oxlCht.SetSourceData Source:=Sheets("Data").Range("B3:C28"), PlotBy:= _
    xlColumns
      'get rid of the first series that sets the scale
    oxlCht.SeriesCollection(1).Delete
    oxlCht.SeriesCollection(1).XValues = "={""Weeks""}"
    oxlCht.SeriesCollection(1).Name = _
      "=""Historical Percent Paid by Week After Invoice"""
    oxlCht.Location Where:=xlLocationAsObject, Name:="Forecast"
    oxlApp.ActiveChart.HasTitle = True
    oxlApp.ActiveChart.ChartTitle.Characters.Text = "Percent Paid by Week After Invoice"
    oxlApp.ActiveChart.Axes(xlCategory, xlPrimary).HasTitle = False
    oxlApp.ActiveChart.Axes(xlValue, xlPrimary).HasTitle = False
    oxlApp.ActiveChart.Legend.Select
    oxlApp.Selection.Delete
      'Shape object is the chart.  Shapes(1) is logo
    oxlApp.ActiveSheet.Shapes(2).Top = 250 
    oxlApp.ActiveSheet.Shapes(2).Left = 0
    oxlApp.ActiveSheet.Shapes(2).Width = 516
    oxlApp.ActiveChart.SeriesCollection(1).XValues = "=Data!R3C2:R28C2"
    oxlApp.Range("A1").Select
    oxlApp.ActiveSheet.PageSetup.TopMargin = oxlApp.InchesToPoints(0.5)
    oxlApp.ActiveSheet.PageSetup.BottomMargin = oxlApp.InchesToPoints(0.5)
ExitRoutine:
    On Error Resume Next
    r.Close
    Set r = Nothing
    oxlApp.Visible = True
    Set oxlCht = Nothing
    Set oxlWkb = Nothing
    Set oxlApp = Nothing
    Exit Sub
ErrorHandler:
    With Err
        Select Case .Number
            Case Else
                MsgBox .Number & vbCrLf & .Description, vbInformation, "Error - " & _
                  "frmCashFlow.cmdCashFlowSummary_Click"
        End Select
    End With
    Resume 0
    Resume ExitRoutine
End Sub

Ciao 
Jürgen Welz 
Edmonton, Alberta 
jwelz at hotmail.com
_________________________________________________________________
Attention all humans. We are your photos. Free us.
http://go.microsoft.com/?linkid=9666046