run-time error 1004 creating excel chart from Access 2010 1

[TimPen]

The following is a section of code that I use to edit a chart in Excel. The code runs perfectly the first time. If I then close Excel and re-run the code, I get a run-time error 1004 - Method 'Active Chart' of object '_global' failed - on the last line of code below.
Why is this?

Public Function CreateChartExcel(ReportName As String, ChartObjectName As String, ChartDataSource As String)

Dim lngTwisp As Long
Dim NumberOfSeries As Integer
Dim NumberOfRecords As Integer
Dim db As DAO.Database
Dim rstSeries As DAO.Recordset
Dim rstFormatSeries As DAO.Recordset
Dim oExcel As Excel.Application
Dim oChart As Excel.Chart
Dim ColumAlpha As String
Dim ExcelRange As String
Dim intShowGridlines As Integer
Dim ExcelDataFile As String

'On Error GoTo ColumnChart_Err

Dbfolder
ExcelDataFile = CurrentFolder & "Data.xls"

If IsFileOpen(ExcelDataFile) = True Then
MsgBox "Please close the file " & ExcelDataFile, vbCritical, "Close File"
Exit Function
End If

'lngTwisp = 1440 'Inch
lngTwisp = 567 'cm

Set db = CurrentDb()
Set rstSeries = db.OpenRecordset("Select * from " & ChartDataSource)
If rstSeries.RecordCount > 0 Then
ExportToExcel ChartDataSource, "Data", ExcelDataFile
Else
MsgBox "No data avialable to chart", vbExclamation, "No Data"
Exit Function
End If

Set oExcel = New Excel.Application
oExcel.Visible = True
oExcel.Workbooks.Open (CurrentFolder & "Data.xls")

Set oChart = oExcel.Charts.Add()

With rstSeries
.MoveLast
.MoveFirst
NumberOfRecords = rstSeries.RecordCount
End With

NumberOfSeries = NumberOfFields(ChartDataSource)
ColumAlpha = ChrW((NumberOfSeries - 1) + 65) 'use 97 for lowercase
ExcelRange = "B1:" & ColumAlpha & "" & NumberOfRecords

With oExcel
ActiveChart.ChartArea.Select

 

[combo]

You have already the chart assigned to oChart, so:
oChart.ChartArea.Select
In most cases in excel there is no need to select objects for processing.


combo

 

[TimPen]

hi Combo
That fixed that!

Now I get a run time error 91 - object variable or with block variable not set:

oChart.ChartArea.Select
oChart.Axes(xlCategory).Select
With Selection
.TickLabelPosition = pubXAxisLabelPosition <=== run time error 91
End With

 

[PHV]

In fact you should have some ghost Excel in the task manager's process list as you don't use full qualified object:
With oExcel
[!].[/!]ActiveChart.ChartArea.Select
...
With [!].[/!]Selection
...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886