Chart Automation leaves hidden instance of Excel running

[Roland]

Hi, hoping someone has some insight into the chart method. Using an existing
workbook which is not protected I want to create a chart. For some reason the
code works fine when I start with a fresh instance of Access and creates the
chart perfectly but after that I get a number of error messages with the same
piece of code. I'm sure I'm not calling some method properly but I am at my
wits end. Another thing is that this code leaves an instance of Excel running
hidden but even when I close the hidden instance of Excel I still get the
error messages. I get following errors on either the Charts.Add or
SetDataSource methods :

"Application defined or object defined error"
"Method "Sheets" of object Global failed"
"Method chart of object global failed"
"The remote server machine does not exist or is unavailable."

I should also note that when I remove this code everything runs fine and a
hidden instance of Excel is not left running in the background so I know the
problem lies in the piece of code below.

If I close Access the hidden instance of Excel dissapears. I'm not sure if
I'm not being explicit enough but everything I've tried doesn't help.

I'm using Office 2003 SP2 and am running the db on my local computer.

Thank you in advance for any help.

Cheers

Roland

'-------The code

Private Sub ChartCreate()
Dim xApp As New Excel.Application
Dim oBook As Excel.Workbook
Dim oSheet As Excel.Worksheet
Dim sRangeString As Variant
Dim oChart As Excel.Chart



On Error GoTo Error_Control:

xApp.ScreenUpdating = False

'SET THE WORKBOOK TO THE DIRECTORY AND EXCEL WORKBOOK NAME
Set oBook = xApp.Workbooks.Open(mOutput)


'SET THE WORKSHEET NAME
Set oSheet = oBook.Sheets(sReportMonthBookingCurve)

Set oChart = oBook.Charts.Add 'errors occur here

'SET THE RANGE TO PASS TO SETSOURCEDATA METHOD
sRangeString = "B4" & sRangeMonthLastRowBookingCurve + 4


'ADD A LINE CHART TO AN EXISTING WORKSHEET, SET THE DATA RANGE...

oChart.ChartType = xlLineMarkers
oChart.SetSourceData Source:=Sheets(oSheet.Name).Range(sRangeString),
PlotBy:=xlColumns 'or errors occur here
oChart.Location where:=xlLocationAsObject, Name:=oSheet.Name

ActiveChart.ChartArea.Select

With ActiveChart
..HasTitle = True
..ChartTitle.Characters.Text = "Insert Title Here"
..Axes(xlCategory, xlPrimary).HasTitle = True
..Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Booking Curve Day
Ranges" 'add X-Axis Label
..Axes(xlValue, xlPrimary).HasTitle = True
..Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "% of Total Guest
Count" ' add Y-Axis Label
End With

ActiveChart.Deselect


Set oSheet = Nothing
DoEvents
oBook.Close (True)
DoEvents
Set oBook = Nothing
DoEvents
xApp.Quit
Set xApp = Nothing
sRangeString = Empty



Exit Sub


Error_Control:
' ' Display error information.
MsgBox "Error number " & Err.Number & ": " & Err.Description
Set oSheet = Nothing
DoEvents
oBook.Close (True)
DoEvents
Set oBook = Nothing
DoEvents
xApp.Quit
Set xApp = Nothing
sRangeString = Empty

Exit Sub
End Sub

 

[Ralph]

Take a look at the link below:

http://www.tushar-mehta.com/excel/vba/xl_doesnt_quit/

You can't use oChart to refer to the chart object and then refer to it as
ActiveChart. It is the same with Workbook, Worksheet, and Range variables.

 

[RoyVidar]

Hi, hoping someone has some insight into the chart method. Using an
existing workbook which is not protected I want to create a chart.
For some reason the code works fine when I start with a fresh
instance of Access and creates the chart perfectly but after that I
get a number of error messages with the same piece of code. I'm sure
I'm not calling some method properly but I am at my wits end.
Another thing is that this code leaves an instance of Excel running
hidden but even when I close the hidden instance of Excel I still get
the error messages. I get following errors on either the Charts.Add
or SetDataSource methods :

"Application defined or object defined error"
"Method "Sheets" of object Global failed"
"Method chart of object global failed"
"The remote server machine does not exist or is unavailable."

I should also note that when I remove this code everything runs fine
and a hidden instance of Excel is not left running in the background
so I know the problem lies in the piece of code below.

If I close Access the hidden instance of Excel dissapears. I'm not
sure if I'm not being explicit enough but everything I've tried
doesn't help.

I'm using Office 2003 SP2 and am running the db on my local computer.

Thank you in advance for any help.

Cheers

Roland

'-------The code

Private Sub ChartCreate()
Dim xApp As New Excel.Application
Dim oBook As Excel.Workbook
Dim oSheet As Excel.Worksheet
Dim sRangeString As Variant
Dim oChart As Excel.Chart



On Error GoTo Error_Control:

xApp.ScreenUpdating = False

'SET THE WORKBOOK TO THE DIRECTORY AND EXCEL WORKBOOK NAME
Set oBook = xApp.Workbooks.Open(mOutput)


'SET THE WORKSHEET NAME
Set oSheet = oBook.Sheets(sReportMonthBookingCurve)

Set oChart = oBook.Charts.Add 'errors occur here

'SET THE RANGE TO PASS TO SETSOURCEDATA METHOD
sRangeString = "B4" & sRangeMonthLastRowBookingCurve + 4


'ADD A LINE CHART TO AN EXISTING WORKSHEET, SET THE DATA RANGE...

oChart.ChartType = xlLineMarkers
oChart.SetSourceData Source:=Sheets(oSheet.Name).Range(sRangeString),
PlotBy:=xlColumns 'or errors occur here
oChart.Location where:=xlLocationAsObject, Name:=oSheet.Name

ActiveChart.ChartArea.Select

With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Insert Title Here"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Booking
Curve Day Ranges" 'add X-Axis Label
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "% of Total
Guest Count" ' add Y-Axis Label
End With

ActiveChart.Deselect


Set oSheet = Nothing
DoEvents
oBook.Close (True)
DoEvents
Set oBook = Nothing
DoEvents
xApp.Quit
Set xApp = Nothing
sRangeString = Empty



Exit Sub


Error_Control:
' ' Display error information.
MsgBox "Error number " & Err.Number & ": " & Err.Description
Set oSheet = Nothing
DoEvents
oBook.Close (True)
DoEvents
Set oBook = Nothing
DoEvents
xApp.Quit
Set xApp = Nothing
sRangeString = Empty

Exit Sub
End Sub

The first error, or implicit referencing, is this

oChart.SetSourceData Source:=Sheets(oSheet.Name).Range(sRangeString),
PlotBy:=xlColumns 'or errors occur here
oChart.Location where:=xlLocationAsObject, Name:=oSheet.Name

Reason - source, there's an unqualified reference to the excel Sheet
object.

without any testing, I think probably this should work:

oChart.SetSourceData _
Source:=oBook.Sheets(oSheet.Name).Range(sRangeString), _
PlotBy:=xlColumns 'or errors occur here
oChart.Location where:=xlLocationAsObject, Name:=oSheet.Name

Then, as commented by Ralph, the usage of unqualified referencing such
as Activechart, will continue to give similar experiences.

See http://support.microsoft.com/default.aspx?kbid=178510 for more
information.

Note - I've practically never done any chart automation, so I'm not
100% sure about this, but you've created an object variable that's
supposed to hold a reference to the chart, wouldn't

oChart.ChartArea.Select

With oChart
....

work, in stead of

ActiveChart.ChartArea.Select

With ActiveChart
....

Point when doing automation, is that every method, property and
object belonging to the object model of the automated application,
needs to be "anhored" to it's parent object.

Any Active<Thingie>, Range, Sheet, Selection... and other unqualified
referencing, will create the problems you experience.