[AccessD] Access to Excel Automation works first time

Just a thought have you tried this:
ExitRoutine:
   On Error Resume Next
   r.Close
   Set r = Nothing
   oxlApp.Visible = True
   Set oxlCht = Nothing
   oxlWkb.Close
   Set oxlWkb = Nothing
   oxlApp.Quit
   Set oxlApp = Nothing
   Exit Sub

Paul Hartland
2009/7/13 Jurgen Welz <jwelz at hotmail.com>

>
> 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
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>



-- 
Paul Hartland
paul.hartland at googlemail.com