[AccessD] Access to Excel Automation works first time

The old "unqualified reference" strikes again :-)
See http://support.microsoft.com/kb/319832

Here's one I've spotted:
oxlCht.SetSourceData Source:=Sheets("Data").Range("B3:C28"), PlotBy:= _
        xlColumns

Sheets need to be qualified.  Off the top of my head, try:

oxlCht.SetSourceData Source:=oxlWkb.Sheets("Data").Range("B3:C28"), PlotBy:= _
        xlColumns

-- 
Stuart


On 12 Jul 2009 at 23:55, Jurgen Welz wrote:

> 
> 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 axi
>  
> 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
>