Runtime Error 1004; Method 'location of object' Chart failed.

[pedie]

Hi, This error happens everytime i run the code for more then once. Is creating excel Chart from access not possible?

Thanks in advance.
Please correct the below code.

[/FONT]
[FONT=Courier New]Option Compare Database
Option Explicit[/FONT]
[FONT=Courier New]Sub test1()
'-------------------------------
Dim wsSheet As Worksheet, wbBook As Workbook
Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
Dim xlWs As Excel.Worksheet
Dim cnt    As ADODB.Connection
Dim rst    As ADODB.Recordset
Dim fld    As ADODB.Field[/FONT]
[FONT=Courier New]Set cnt = CurrentProject.Connection
Set rst = New ADODB.Recordset
rst.ActiveConnection = cnt
Dim MySQL As String[/FONT]
[FONT=Courier New]MySQL = "SELECT Table1.DLs, Table1.Qtr, Table1.Expense1 FROM Table1;"[/FONT]
[FONT=Courier New]rst.Open MySQL
If Not (rst.BOF And rst.EOF) Then
 Else
 MsgBox "Data not available, please try different critiria....", vbInformation, "No Info."
 GoTo closeme
End If[/FONT]
[FONT=Courier New]Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
Set xlWB = xlApp.Workbooks.Add
On Error GoTo 0[/FONT]
[FONT=Courier New]Dim x As Long[/FONT]
[FONT=Courier New]With xlApp
    x = 0
    For Each fld In rst.Fields
    .Cells(1, x + 1).Value = fld.Name
    x = x + 1
Next fld
End With[/FONT]
[FONT=Courier New]With xlApp
.Cells(2, 1).CopyFromRecordset rst
.Cells.EntireColumn.AutoFit
End With[/FONT]
[FONT=Courier New][/FONT] 
[FONT=Courier New]With xlWB.Worksheets("Sheet1")
.Range("A1:C10").Select
.Shapes.AddChart.Select[/FONT]
[FONT=Courier New]With ActiveChart
.SetSourceData Source:=Range("Sheet1!$A$1:$C$10")
.ChartType = xlBarClustered
.PlotArea.Select
.ChartTitle.Text = "My Chart"
End With
End With[/FONT]
[FONT=Courier New][/FONT] 
[FONT=Courier New]Set xlWB = Nothing
Set xlApp = Nothing
closeme:
If CBool(rst.State And adStateOpen) = True Then rst.Close
Set rst = Nothing
Set cnt = Nothing[/FONT]
[FONT=Courier New]End Sub

 

[xenou]

Excel charting has changed a lot in 2007/2010. If you are using a code example makes sure it's a recent one that isn't for Excel 2003. I don't create charts from scratch in code - typically I'd use a workbook (let's call it a template but it's really a regular workbook) - have the chart in there already. Then you only need to add your data and it's done (save the template as your new Excel file). That said, if you need to use code then get an up to date example, write and test the code in Excel, and then move it to access. Try to avoid selection when automating Excel from Access.

 

[pedie]

Thanks Xen!!! Yes that leads to another question...let me start new thread. That actually is great idea... I'll use template..
Thanks again.