[AccessD] Access to Excel Automation works first time

You nailed it Stuart.  I revised that line of code to oxlApp.Sheets and it works as intended.

Ciao 

Jürgen Welz 

Edmonton, Alberta 

jwelz at hotmail.com


 
> From: stuart at lexacorp.com.pg
> To: accessd at databaseadvisors.com
> Date: Mon, 13 Jul 2009 16:18:05 +1000
> Subject: Re: [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
> > 
> 
> 
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com

_________________________________________________________________
Create a cool, new character for your Windows Live™ Messenger. 
http://go.microsoft.com/?linkid=9656621