Problem creating Excel Chart from Access

[bonopips]

Hello to everyone.
I'm developing an application that works on MS Jet database, Access Forms and interact with MS Excel to create some dynamic
charts. Exporting data from Access to Excel is going well. I found some problems when creating the chart.
From an Access Form I set a button witch opens Excel, transfert data to it and try to create a chart with the CHARTWIZARD
method. Things are going well if I create a chart using a specified and static range witch I refer to (i.e. Range("A15")).
When I try to set the things to calculate the Range dynamically, I get some serious problem:

I created a string LastCell setted like this:
LastCellAddress = ActiveCell.SpecialCells(xlLastCell).Address

This should contain the last cell filled in the worksheet, and it works OK (in my case it is setted as "$D$5")

So I create the chart using this dynamic reference:
WS2.ChartObjects("Chart 1").Chart.ChartWizard WS1.Range("A1:" & LastCellAddress), , 1, xlColumns, 1, 0, 1, "", "Categorie",

"", ""

It works ok, it creates the chart and so on.

BUT, in this case, when I close the Excel Workbook (manually or via code), in the TASK MANAGER [/color red]still remain an istance of Excel: i.e. Excel has not been closed. So, when I try to create another chart, an error occurs.

This error doesn't happen when I set the Range statically in the code.
Obviously this is not what I was expecting to do.

How can I solve this problem?

I'll not be on-line for some days, so if you'll not get my replies don't you think I'm no more interested in your answers.
I will be on-line in a few days. So I expect to find a few suggestion. Thanks a lot, Filippo.

This is part of the code that works:

Private Create_Chart()

Dim XL As Object
Dim xlWB As Excel.Workbook
Dim WS1 As Object
Dim WS2 As Object

...

' Create an Excel Application object:
Set XL = CreateObject("Excel.Application")

' Add a Workbook and set Worksheet objects to the first two Sheets:
Set xlWB = XL.Workbooks.Add
'XL.Workbooks.Add
Set WS1 = XL.Worksheets(1)
Set WS2 = XL.Worksheets(2)

' Fill in Cells of first Worksheet with data for Charts:

LastCellAddress = ActiveCell.SpecialCells(xlLastCell).Address 'To use or not to use???
...


WS2.Select

WS2.ChartObjects.Add(0, 0, XL.InchesToPoints(10), XL.InchesToPoints(6)).Select


' Use the ChartWizard method to fill in the Chart:
THIS WORKS WELL
WS2.ChartObjects("Chart 1").Chart.ChartWizard WS1.Range("A15"), , 1, xlColumns, 1, 0, 1, "", "Categorie", "", ""
'WS2.ChartObjects("Chart 1").Chart.ChartWizard WS1.Range("A1:" & LastCellAddress), , 1, xlColumns, 1, 0, 1, "", "Categorie", "", ""

' Chart Modify ----------------------------------------
xlWB.ActiveChart.ChartType = xlColumnStacked100
...

 

[thecreator]

Try releasing all of your objects before you exit your function - eg set XL = nothing. This is good coding practice to clean up after yourself.

Also, if you are in a module and testing for a true value somewhere back on a form, don't use the syntax -

if form!objectname then
....

but rather

if form!objectname = true then
....

as without explicitly testing for "true" access (97 version anyway) can get itself in a knot .... there is something in the MS knowledge base about that one (I know, it caused me no end of grief for ages).

Hope this helps

 

[km2x]

After releasing your objects include END .



'release the object and quit
'objExcel.Quit
Set objExcel = Nothing
End ' this one is what does it.





End Sub

It was a headache for me. Hope this helps.

Km2x