Code Problem

[Troy]

Hi...

I posted this problem in another Access news group and got no reply.. Still
not sure if this question belongs in an Excel group, or here, but here's my
problem:

In a nutshell, Access 2000 module exports form data from a query, in xls
format, using OutputTo... then creates an Excel object, opens the previously
created xls file, formats data and graphs it. The last code line executed
sets excel object equal to nothing. The form is left open to be saved as
some file name by user.

My problem is, after the excel workbook is closed by the user, it remains in
the task manager list of running processes until the Access database itself
is closed. This causes a problem if the module is run again with different
parameters.

Anyone run into this? Am I mistaken in thinking that setting the excel
object to nothing is freeing it from the access code that created it?

Any help would be appreciated!

 

[Matthias Klaey]

Hi...

I posted this problem in another Access news group and got no reply.. Still
not sure if this question belongs in an Excel group, or here, but here's my
problem:

In a nutshell, Access 2000 module exports form data from a query, in xls
format, using OutputTo... then creates an Excel object, opens the previously
created xls file, formats data and graphs it. The last code line executed
sets excel object equal to nothing. The form is left open to be saved as
some file name by user.

My problem is, after the excel workbook is closed by the user, it remains in
the task manager list of running processes until the Access database itself
is closed. This causes a problem if the module is run again with different
parameters.

Anyone run into this? Am I mistaken in thinking that setting the excel
object to nothing is freeing it from the access code that created it?

Any help would be appreciated!

Troy

first thing to check: are you up-to-date with all service packs for
your Access and Excel installation?
I assume that you are using both Access 2000 and Excel 2000.

Is there a possibility to upgrade, say, to Acess/Ecel 2003? Ok Office
2007 is just around the corner, but this probably will use quite some
work to upgrade, and little and nothing ist known about the bugs in
*this* system

Perhaps there is something else in your Excel code that prohibits
closing Excel. Could you post the original code?

HTH
Matthias Kläy

 

[Troy]

Hi Matthias,

You are correct... I am using Access and Excel 2000, with the latest service
packs. Intersestingly though, I do get a message indicating the Excel
Workbook is in an earlier format when I save it after the code has run. Not
sure why, since my system never had previous versions installed. Here is my
code. As stated before, this is executed from an Access form which contents
are the result of a query:

Option Compare Database

Option Explicit

Dim ObjXL As Excel.Application

Dim sName As String, iWB As Integer

Dim x As Integer, y As Integer, z As Integer Dim sWO As String, sCellID As
String

Private Sub CmdExcel_Click()

Dim sDocName As String

'Output form to Excel format

sDocName = "C:\ChartOutput.xls"

DoCmd.OutputTo acOutputForm, , acFormatXLS, sDocName

'Start Excel

Set ObjXL = CreateObject("Excel.Application")

ObjXL.Workbooks.Open (sDocName)

ObjXL.Application.Visible = True

sName = ObjXL.ActiveWorkbook.Name

iWB = ObjXL.Workbooks.Count


'Find last datarow (x)

x = 1

Do Until ObjXL.Cells(x, 7).Value = ""

DoEvents

x = x + 1

Loop


sWO = CStr(ObjXL.Cells(2, 1).Value)

sCellID = UCase(CStr(ObjXL.Cells(2, 2).Value))



'Select Existing Data and create chart

ObjXL.Range("C1:G" + CStr(x)).Select

ObjXL.Workbooks(iWB).Charts.Add

ObjXL.Workbooks(iWB).Charts(1).Activate


ObjXL.Workbooks(iWB).ActiveChart.chartType = xlXYScatterLines

ObjXL.Workbooks(iWB).ActiveChart.SetSourceData
Source:=Sheets("Discharge_Graph").Range("C1:G" + CStr(x)), _

PlotBy:=xlColumns

ObjXL.Workbooks(iWB).ActiveChart.Location Where:=xlLocationAsNewSheet


'Format chart

With ObjXL.Workbooks(iWB).ActiveChart


..HasTitle = True

..ChartTitle.Characters.Text = "WO: " + sWO + " Cell ID: " + sCellID

..Axes(xlCategory, xlPrimary).HasTitle = True

..Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Cycles"

..Axes(xlValue, xlPrimary).HasTitle = True

..Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Neg Cadmium V"

..SeriesCollection(4).Select

..SeriesCollection(4).AxisGroup = 2

..PlotArea.Select

..Axes(xlCategory, xlSecondary).HasTitle = False

..Axes(xlValue, xlSecondary).HasTitle = True

..Axes(xlValue, xlSecondary).AxisTitle.Characters.Text = "% Capacity"


..ChartArea.Select

End With

'Release Excel Object

Set ObjXL = Nothing

Exit Sub

 

[Matthias Klaey]

Hi Matthias,

You are correct... I am using Access and Excel 2000, with the latest service
packs. Intersestingly though, I do get a message indicating the Excel
Workbook is in an earlier format when I save it after the code has run. Not
sure why, since my system never had previous versions installed. Here is my
code. As stated before, this is executed from an Access form which contents
are the result of a query:

Ok, if I dare to simplify: The basic structure of your code is

Dim ObjXL As Excel.Application
Set ObjXL = CreateObject("Excel.Application")
ObjXL.Workbooks.Open (sDocName)
ObjXL.Application.Visible = True
[do stuff with the Excel Workbook]
Set ObjXL = Nothing

As I understand, you want the users to interact with the Excel
worksheet. Thus you to set ObjXL.Application.Visible = True

I see your dilemma: On one hand, you want to keep Excel open for your
users. On the other hand, you are finished with Excel in your Access
Application. So when you execute "Set ObjXL = Nothing" the Excel
object remains open, isnt'it?

I have to admit that I don't know a clean solution to this problem,
Perhapse someone else could jump in here.

BTW the message "Excel Workbook is in an earlier format" comes from
the fact that "DoCmd.OutputTo acOutputForm, , acFormatXLS, sDocName"
is, in fact, using an earlier Excel file format than Excel 2000. This
is harmless; you can choose either action (to keep the current format
or tu use the Excel 2000 format).

Greetings
Matthias Kläy

 

[John Nurick]

Hi Troy

My problem is, after the excel workbook is closed by the user, it remains in
the task manager list of running processes until the Access database itself
is closed.

Surely the user needs to close Excel, not just the workbook.