Close Excel from Access
 

Hello,
from Access make use of sheet of Excel for generate a graph.
Beacuse Excel remain in backgroud execution?
I use this code for close the Workbook without save:

myXLwb.Application.ActiveWorkbook.Close False

and this for close the Excel application:

xlApp.Application.Quit

where mistake?
Thx.

Close Excel from Access
 

Assuming myXLwb is a reference to a workbook
myXLwb.close false
set myXLwb=nothing

And assuming XLApp is a reference to your instance of Excel
xlApp.Quit
set xlapp=nothing

Also assumes that myXLwb is the only WB that you are working on.

NickHK

"Guriuz" wrote in message
...
Hello,
from Access make use of sheet of Excel for generate a graph.
Beacuse Excel remain in backgroud execution?
I use this code for close the Workbook without save:

myXLwb.Application.ActiveWorkbook.Close False

and this for close the Excel application:

xlApp.Application.Quit

where mistake?
Thx.

Close Excel from Access
 

NickHK il giorno 07/06/2007 alle ore 12:53:54 ha scritto:
Assuming myXLwb is a reference to a workbook
myXLwb.close false
set myXLwb=nothing

And assuming XLApp is a reference to your instance of Excel
xlApp.Quit
set xlapp=nothing

Also assumes that myXLwb is the only WB that you are working on.

This is my code:

Dim oRst As New ADODB.Recordset

Set oRst = New ADODB.Recordset
oRst.CursorLocation = adUseClient
oRst.Open "Select Campo1, Campo6 From Tabella1;",
CurrentProject.Connection

Dim oApp As Excel.Application, oWks As Excel.Workbook

Set oApp = CreateObject("Excel.Application")
Set oWks = oApp.Workbooks.Add()

oApp.Visible = True

oWks.Sheets(1).Name = "Raccolta"

With
oWks.Sheets("Raccolta").QueryTables.Add(oRst,oWks. ActiveSheet.Range("A1"))
.Name = "Query1"
.FieldNames = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SaveData = True
.AdjustColumnWidth = True
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With


oWks.Charts.Add().Name = "my graph"
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=oWks.Sheets("Foglio3").Range("A1"),
PlotBy:= _
xlColumns
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues = "=Raccolta!R2C1:R8C1"
ActiveChart.SeriesCollection(1).Values = "=Raccolta!R2C2:R8C2"
ActiveChart.Location Whe=xlLocationAsObject, Name:="Foglio2"
With ActiveChart
.HasTitle = False
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With

oWks.ActiveChart.ChartArea.Select
oWks.ActiveChart.ChartArea.Copy

DoCmd.OpenForm "Maschera2"
Forms!Maschera2!AssociatoOLE0.OLETypeAllowed = acOLELinked
Forms!Maschera2!AssociatoOLE0.Action = acOLEPaste
Forms!Maschera2!AssociatoOLE0.UpdateOptions = acOLEUpdateManual
Forms!Maschera2!AssociatoOLE0.SizeMode = acOLESizeStretch
Forms!Maschera2!AssociatoOLE0.ControlTipText = "Il nostro grafico!"

oWks.Close False
Set oWks = Nothing

oApp.Quit
Set oApp = Nothing

Set oRst = Nothing

End Sub

But:

http://img249.imageshack.us/img249/4904/snap1ny7.jpg

Misunderstand... uff!
Thx.

Close Excel from Access
 

You will have problems because you are using unqualified references. e.g.
ActiveChart.ChartType = xlLineMarkers

Always use the object hierarchy from an object that you a reference to. This
is better
oWks.ActiveChart.ChartArea.Select

but it seldom necessary to .Select object before using them (although Chart
stuff maybe one of the situations ??).
Give yourself a variable to work with:
e.g.
Dim NewChart As Chart
set newchart=oWks.Charts.Add()
With newchart
'Everything you need to do to the chart

NickHK

"Guriuz" ...
NickHK il giorno 07/06/2007 alle ore 12:53:54 ha scritto:
Assuming myXLwb is a reference to a workbook
myXLwb.close false
set myXLwb=nothing

And assuming XLApp is a reference to your instance of Excel
xlApp.Quit
set xlapp=nothing

Also assumes that myXLwb is the only WB that you are working on.

This is my code:

Dim oRst As New ADODB.Recordset

Set oRst = New ADODB.Recordset
oRst.CursorLocation = adUseClient
oRst.Open "Select Campo1, Campo6 From Tabella1;",
CurrentProject.Connection

Dim oApp As Excel.Application, oWks As Excel.Workbook

Set oApp = CreateObject("Excel.Application")
Set oWks = oApp.Workbooks.Add()

oApp.Visible = True

oWks.Sheets(1).Name = "Raccolta"

With
oWks.Sheets("Raccolta").QueryTables.Add(oRst,oWks. ActiveSheet.Range("A1"))
.Name = "Query1"
.FieldNames = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SaveData = True
.AdjustColumnWidth = True
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With


oWks.Charts.Add().Name = "my graph"
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=oWks.Sheets("Foglio3").Range("A1"),
PlotBy:= _
xlColumns
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues = "=Raccolta!R2C1:R8C1"
ActiveChart.SeriesCollection(1).Values = "=Raccolta!R2C2:R8C2"
ActiveChart.Location Whe=xlLocationAsObject, Name:="Foglio2"
With ActiveChart
.HasTitle = False
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With

oWks.ActiveChart.ChartArea.Select
oWks.ActiveChart.ChartArea.Copy

DoCmd.OpenForm "Maschera2"
Forms!Maschera2!AssociatoOLE0.OLETypeAllowed = acOLELinked
Forms!Maschera2!AssociatoOLE0.Action = acOLEPaste
Forms!Maschera2!AssociatoOLE0.UpdateOptions = acOLEUpdateManual
Forms!Maschera2!AssociatoOLE0.SizeMode = acOLESizeStretch
Forms!Maschera2!AssociatoOLE0.ControlTipText = "Il nostro grafico!"

oWks.Close False
Set oWks = Nothing

oApp.Quit
Set oApp = Nothing

Set oRst = Nothing

End Sub

But:

http://img249.imageshack.us/img249/4904/snap1ny7.jpg

Misunderstand... uff!
Thx.

Close Excel from Access
 

NickHK il giorno 07/06/2007 alle ore 15:04:17 ha scritto:
You will have problems because you are using unqualified references

Ok NickHK, I will try your solution...
Thx for your availability.

Close Excel from Access
 

NickHK il giorno 07/06/2007 alle ore 15:04:17 ha scritto:
You will have problems because you are using unqualified references. e.g.
ActiveChart.ChartType = xlLineMarkers

Always use the object hierarchy from an object that you a reference to. This
is better
oWks.ActiveChart.ChartArea.Select

but it seldom necessary to .Select object before using them (although Chart
stuff maybe one of the situations ??).
Give yourself a variable to work with:
e.g.
Dim NewChart As Chart
set newchart=oWks.Charts.Add()
With newchart
'Everything you need to do to the chart

New code part, but it's no correct (Run-time error -2147221080 in
..HasTitle):

Dim NewChart As Chart
Set NewChart = oWks.Charts.Add()

NewChart.Activate

With ActiveChart
.Name = "mio grafico" '<-- crea un foglio grafico
.ChartType = xlLineMarkers
.SetSourceData Source:=oWks.Sheets("Foglio3").Range("A1"),
PlotBy:=xlColumns
.SeriesCollection.NewSeries
.SeriesCollection(1).XValues = "=Raccolta!R2C1:R8C1"
.SeriesCollection(1).Values = "=Raccolta!R2C2:R8C2"
.Location Whe=xlLocationAsObject, Name:="Foglio2"
.HasTitle = False
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With

Thx.

Close Excel from Access
 

I don't do much charting, so not sure.
There is the ChartObject also, which seems involved in some things
<guesswork...

However, in terms of objects, you are still using the unqualified reference
of ActiveChart. This often means that you will not be able to release all
references to Excel and allow it to close.
Therefore, use the object reference that you have already, NewChart or
oWks.ActiveChart .

NickHK

"Guriuz" wrote in message
...
NickHK il giorno 07/06/2007 alle ore 15:04:17 ha scritto:
You will have problems because you are using unqualified references.
e.g.
ActiveChart.ChartType = xlLineMarkers

Always use the object hierarchy from an object that you a reference to.
This
is better
oWks.ActiveChart.ChartArea.Select

but it seldom necessary to .Select object before using them (although
Chart
stuff maybe one of the situations ??).
Give yourself a variable to work with:
e.g.
Dim NewChart As Chart
set newchart=oWks.Charts.Add()
With newchart
'Everything you need to do to the chart

New code part, but it's no correct (Run-time error -2147221080 in
.HasTitle):

Dim NewChart As Chart
Set NewChart = oWks.Charts.Add()

NewChart.Activate

With ActiveChart
.Name = "mio grafico" '<-- crea un foglio grafico
.ChartType = xlLineMarkers
.SetSourceData Source:=oWks.Sheets("Foglio3").Range("A1"),
PlotBy:=xlColumns
.SeriesCollection.NewSeries
.SeriesCollection(1).XValues = "=Raccolta!R2C1:R8C1"
.SeriesCollection(1).Values = "=Raccolta!R2C2:R8C2"
.Location Whe=xlLocationAsObject, Name:="Foglio2"
.HasTitle = False
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With

Thx.