How to close Excel from Access using VBA? 3

[datras]

Hi All
I have tried to use automation between Access and Excel for creating charts. It works all right until I close the Excel application. I close Excel with .quit as described in Help and Excel disappears from applications in the task manager, however, it continues to run under processes, which creates problems the next time I open Excel. I use Access 2000 and the code is below.

I would be very pleased if any one could help me with solving this problem.
Lena



Dim objXLApp As New EXCEL.Application
Dim objXLBook As EXCEL.Workbook
Dim objResultsSheet As EXCEL.Worksheet
Dim objChartSheet As EXCEL.Worksheet

objXLApp.Workbooks.Add
Set objXLBook = objXLApp.ActiveWorkbook
objXLBook.Sheets(1).Select
objXLBook.Sheets(1).Name = "Data"
objXLBook.Sheets(2).Select
objXLBook.Sheets(2).Name = "Charts"
Set objResultsSheet = objXLBook.Worksheets("Data&quot
Set objChartSheet = objXLBook.Worksheets("Charts&quot

' Code transferring data from Access to Excel and creates a number of charts in Excel

objXLBook.Close 'SaveChanges:=False
objXLApp.Quit

Set objResultsSheet = Nothing
Set objChartSheet = Nothing
Set objXLBook = Nothing
Set objXLApp = Nothing

 

[rusty666]

Hi,

I notice in most of these solutions you are defining a new excel object, then opening a workbook. This actually spwans another instance of excel within itself which cannot be closed easily. If you don't create a new instance initially it seems to work better. Here is some code that works fine for me.

Dim xlWorkBook As Workbook

Set xlWorkBook = Workbooks.Open("C:\stats\login.csv")

xlWorkBook.Application.Visible = True

'your code here
'Worksheets(1).Select
'Sheets("login").Select

xlWorkBook.Application.DisplayAlerts = False

xlWorkBook.SaveAs "c:\stats\loginIMPORTED.csv"
xlWorkBook.Application.Quit

Hope this help someone.

 

[rusty666]

..darnit..

The only reason my code was working was because I was breaking out of the program rather than allowing it to run through, obviously when I stopped it any processes it has spawned are automatically quit.

I have been battling with this for 2 days now and it's causing me problems. If the EXCEL process is open then I can't open any other sheets, they freeze, I did actually manage to get round this by using:

xlWorkBook.Application.IgnoreRemoteRequests = True

This leaves the EXCEL.EXE process in the background but will not affect any other sheets at all. It doesn't spawn a new process when the code is run again either.

An acceptable compromise I would say.

 

[Burglar]

The problem with the IgnoreRemoteRequests = True option is that it disables the ability to open a workbook by simply double-clicking on it. Also, if set to false after the execution of the code, the problem reoccurs. Is there any other solution for this problem that doesn't remove one bit of functionality for the sake of another?

Burglar

 

[Burglar]

After pulling my hair out for the last 2 days, ratsstar69's solution actually worked. I thought I had tried it but I missed one range statement. When I added the Objxl.range instead, it started working. Thanks a lot.

 

[DanSc0tt]

OK this is probably very simple, but I have just got a new PC and this code JUST TO OPEN EXCEL e.g.

Dim objXLApp As Object
Dim objXLBook As Object


Set objXLApp = CreateObject("Excel.Application")
objXLApp.Workbooks.Add
Set objXLBook = objXLApp.ActiveWorkbook


used to work on my old PC, however it now will not work on my new one.
- I have tried enabling the TOOLS>>REFERENCES in the library for Excel
- Was wondering if there is some sort of enhanced security in the latest version of excel to prevent other programs from controlling it??

Any ideas gratefully recieved!!

Dan