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

 

[Guest_imported]

That's exactly the same problem I have now. Did you find a solution in the meantime? You could help me a lot. Thank you. Robert

 

[pser]

I have the same problem. I think one solution could be to find the windows handle with the following API and then close that handle.

Right after you create the Excel object you do this:

hWndXl = FindWindow("XLMAIN", AppExcel.Caption)

Then you get the handle and it just matter of closing process with that particulat handle. Well that what i'm trying to figure out. But i'm not finish yet.

Steven
pseriksen@yahoo.no

Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long

 

[datras]

Hi There

Just realised that the discussion on how to open and closing Excel from Access is still going on.

Mine is working now. I have these two routines one to open and one to closing Excel. In between I have a fair bit off code so this might not be enough but try this first – Steven I think you are making your life more complicated than it has to be.

This code is made in 97 but works now in 2000.
Lena

Public objXLApp As Excel.Application
Dim objXLBook As Excel.Workbook
Dim objResultsSheet As Excel.Worksheet
Dim objChartSheet As Excel.Worksheet

Public Sub CreateExcel()

Set objXLApp = CreateObject("excel.application&quot
Set objXLBook = objXLApp.Workbooks.Add

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

End Sub

Public Sub SaveFig()
On Error GoTo Checkerror


objXLApp.Quit
Set newChart = Nothing
Set objResultsSheet = Nothing
Set objChartSheet = Nothing
Set objXLBook = Nothing
Set objXLApp = Nothing

Exit Sub

Exit_Checkerror:
Exit Sub

Checkerror:
Resume Exit_Checkerror
End Sub

 

[pser]

I've think i've solved my problem now. It's seems like a matter of using the right command for doing the right thing or else it creates another instance of the object, so at the end when you close it you're not able to close the second instance.

One thing i haven't solved it to sort the worksheet i create from Access. If i try to sort with this command:

ObjXL.Selection.Sort Key1:=Range("C1&quot, Order1:=xlAscending, Header:=xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

I'm not able to close Excel, without this command everything is ok. This is how i did it:

Dim ObjXL As Excel.Application
Dim ObjXLBook As Excel.Workbook
Dim ObjXLSheet As Excel.Worksheet


Set ObjXL = CreateObject("Excel.Application&quot
Set ObjXLBook = ObjXL.Workbooks.Open("Path & navn of xl file&quot
Set ObjXLSheet = ObjXLBook.Worksheets("Name of sheet&quot

..
..

ObjXLBook.Save
Set ObjXLSheet = Nothing
Set ObjXLBook = Nothing
ObjXL.Quit
Set ObjXL = Nothing

This works fine for me, without the sort stuff. It's bothering me allot since i create a Pivot table and i wish to sort it.

Steven

 

[pser]

I've solved it in another way. Silly of me not discover that solution earlier. I just did the sort in Access, before exporting it to Excel as pivot table. So now everything is just perfect.

It's seems like using the Xl object creates another instance of Excel, so when you close it you're not able to close the second.

Anyway i'm happy now.

Thanks

Steven

 

[dynasty]

Dear all,
I had the same problem and tried all your suggestions but did not work.
Now I found the solution and am glad to share it with you all:

Const ERR_APP_NOTRUNNING As Long = 429
On Error Resume Next
Set xlApp = GetObject(, "Excel.Application&quot
If Err = ERR_APP_NOTRUNNING Then
Set xlApp = New Excel.Application
End If



Good Luck

Dynasty

 

[meredithtx]

Did anyone ever find a solution to this? Dynasty's solution works great to run if you have an Excel app still running from the previous execution. I'm still stuck with the problem of trying to get Excel to close after execution, as I have to be able to open an Excel spreadsheet after execution.

 

[flize]

All,

I had the same issues with trying to control Excel 2000 from Access 97. The Microsoft Excel 9.0 Object Library ActiveX control does not close the spreadsheet file as described in several books, MSDN or the online help file examples.

To get it to work, I discarded the Excel.Application ActiveX approach and simply used variable type Object to control Excel.

Thanks to the excellent users on this forum with great tips, here is the basic code I used to finally make it work. This code does the following:

1) Opens an existing spreadsheet
2) Shows it on screen
3) Writes some data from a table to several different worksheet cells
4) Selects a worksheet and 'Autofits' the cells
5) Saves the existing file
6) closes it

Private Sub ExcelCode()

Dim appExcel As Object
Dim workBook As Object
Dim workSheet As Object

' Open an existing spreadsheet
Set appExcel = GetObject("C:\Accounting\File1.xls&quot

' Show spreadsheet on screen
appExcel.Application.Visible = True
appExcel.Parent.Windows(1).Visible = True

' Loop through record set and populate cells with data
Set rst = db.OpenRecordset("tbl_xref&quot
rst.MoveFirst
Do Until rst.EOF
Set workSheet = appExcel.Worksheets(rst![Tab])
workSheet.Cells(rst![CellRow], rst![CellColumn]).Value = rst![Value]
rst.MoveNext
Loop

' Autofit all columns on a worksheet
Set workSheet = appExcel.Worksheets("wks1&quot
'workSheet.Cells.Select NOTE:this line's code doesn't work!
workSheet.Cells.EntireColumn.AutoFit

' Turn prompting OFF and save the sheet with original name
appExcel.Application.DisplayAlerts = False
appExcel.Application.Save
appExcel.Application.DisplayAlerts = True
appExcel.Application.Quit

' Release objects
Set workSheet = Nothing
Set workBook = Nothing
Set appExcel = Nothing

End Sub


Hope this helps...

flize

 

[uconnscott]

flize,

I think you forgot to "set" workbook...I get an error...fyi

Scott

 

[jvantichelt]

I found this solution at the Microsoft knowlegde database (Microsoft knows his bug)number "Q187745: BUG: Microsoft Excel Does Not Repaint Properly with Automation".

It says:
This article was previously published under Q187745
SYMPTOMS
When automating Microsoft Excel 97, Excel 2000, or Excel 2002, if the application window has been made visible and the user manually closes it, Excel will not repaint correctly the next time the application window is made visible again.
CAUSE
This problem occurs when a user attempts to quit a running instance of Excel while an Automation client still has a reference to the application object for that instance. By design, Excel does not quit an instance of itself unless all external references are released; if a user tries to quit Excel manually, the application window is merely hidden so that the Automation client may continue working. If, however, the Automation client attempts to make Excel visible again, the application window will not be displayed properly and repainting will not occur.
RESOLUTION
A workaround is to set the ScreenUpdating property of the application object to True after you have made the window visible. This will force Excel to repaint its client area so that it will be displayed properly.
STATUS
Microsoft has confirmed this to be a bug in the Microsoft products listed at the beginning of this article.
MORE INFORMATION
Steps to Reproduce Behavior
Start a new Standard EXE project in Visual Basic. Form1 is created by default.
On the Project menu, click References, and then select the Microsoft Excel 8.0 object library. For Excel 2000, select Microsoft Excel 9.0 object library, and for Excel 2002, select the Microsoft Excel 10.0 object library.
Place a CommandButton on Form1.
Copy the following code to the Code Window of Form1: Private oApp As Excel.Application

Private Sub Command1_Click()
oApp.Visible = True
End Sub

Private Sub Form_Load()
Set oApp = CreateObject("Excel.Application&quot
Command1.Caption = "Show Excel"
End Sub




Private Sub Form_Unload(Cancel As Integer)
Set oApp = Nothing
End Sub

On the Run menu, click Start, or press the F5 key to start the program.
Click on the Command button to make Excel visible. Close Excel by pressing the Close button on Excel's title bar, or by selecting Exit from the File menu. Now press the Visual Basic Command button again, and note that Excel does not paint itself correctly.
Repeat the steps again with the Command button's code modified as follows: Private Sub Command1_Click()
oApp.Visible = True
oApp.ScreenUpdating = True
End Sub

 

[shaiss]

I've searched the web for hours trying to figure out how to open, print, then close the excel file. This is all triggered by a button on the switchboard. Well, this code seems to do the trick. Only one problem, when i open another excel file manualy excell doesnt seem to repaint itself. I tried adding:
appExcel.Application.ScreenUpdating = True
like jvantichelt says refering to MSDN article Q187745 above in his post. But still no luck. The odd thing is. when i automatically open a file through this script again it works with no problems. It seems like you need to end the Excel.exe proccess after each time you run the script, is there a way to do that?

And I'm sure i did the print object wrong with the with statement, but it works, is there an eiser way to do it? also if I set the Application.visible=false then printing the active window doesnt work. Is there a way to hide excel, to make it open in the background?

Function OpenExcelFile()
    Hourglass = True
    Dim appExcel As Object
    Dim objExcel As Object
    Set objExcel = ActiveSheet
    Dim workBook As Object
    Dim workSheet As Object
    Dim ExcelFile As String
    
    ExcelFile = "c:\test.xls"
    
        ' Open an existing spreadsheet
        Set appExcel = GetObject(ExcelFile)
        ' Show spreadsheet on screen
        appExcel.Application.Visible = True
        appExcel.Parent.Windows(1).Visible = True
        
        'Print the active worksheet
        With objExcel
             ActiveSheet.PrintOut
        End With
        
        ' Turn prompting OFF and save the sheet with original name
        appExcel.Application.DisplayAlerts = False
        'appExcel.Application.Save
        'appExcel.Application.DisplayAlerts = True
        appExcel.Application.Quit

        ' Release objects
        Set workSheet = Nothing
        Set workBook = Nothing
        Set appExcel = Nothing
        Set objExcel = Nothing
        Hourglass = False
End Function

 

[trader99]

I am having the same problem where an instance of Excel remains open but hidden (viewable in Task manager - processes). I am opening an existing spreadsheet within Access, populating it with data, and then closing it. Actually I have a loop where I update about 40 spreadsheets before I close the app. I don't want to make Excel visible during this process so don't need .Visible or .ScreenUpdating. The proposed solution to close Excel that works for some people does not work for me:

Do Until ...

..
..

xlBook.Save
xlBook.Close
Set xlBook = Nothing
..
Loop ' loop thru all spreadsheets

xlApp.Quit
Set xlApp = Nothing
End Sub

Any other suggestions on how to close the hidden instance of Excel without using Task Manager or re-booting. Thanks in advance.

 

[shaiss]

Yeah, there has to be some way to kill the process itself. Maybe finding the window handle and sending the kill message?

 

[ratsstar99]

I was faffing around with this problem for an hour or two also. I am using winxp and access and Excel XP. I found that if you have a excel function call that is not called off the excel application object then the thing hangs. Look below as all the functions hang off the objExcel object.

Before you do anything fancy try this code.

Dim objExcel As Excel.Application
Set objExcel = CreateObject("Excel.Application")
objExcel.Workbooks.Add
objExcel.Visible = False
objExcel.range("A1") = "WSH was here"
objExcel.DisplayAlerts = False
objExcel.Workbooks(1).SaveAs ("R:\zvezek1.xls")
objExcel.DisplayAlerts = True
objExcel.range("A1:A2").Select
objExcel.Quit
Set objExcel = Nothing

Then check your processes.

This should work for you.

What I found was that leaving a line in the code like.....

range("A1") = "WSH was here"
instead of ...
objExcel.range("A1") = "WSH was here"

will stop excel closing properly.

Hope this may help someone.

 

[ttabs11]

I have a similar problem and after trying all the suggestions giving I have been unable to resolve it. I am using Win98, excel 2000, and vb6. I am opening an existing excel file from vb, editing a large number of cells, and closing it. I am doing the editing in a loop. When the loop variable is small everything works as expected. When I increased the variable excel no longer closes. It remains in the running processes. When I increase it even more, I get a component request pending error. Excel stops responding to the vb program. I thought I had an issue with RAM but I increased that and it didn't seem to make a difference. Any ideas?
My code:

Public xlApp As Excel.Application
Public xlWb As Excel.Workbook
Public xlWs1 As Excel.Worksheet
Public xlWs2 As Excel.Worksheet

Set xlApp = New Excel.Application
Set xlWb = xlApp.Workbooks.Open("C:\WINDOWS\desktop\Tonya\2Counters\FreqData.xls")
Set xlWs1 = xlWb.Worksheets(1)
Set xlWs2 = xlWb.Worksheets(2)

'begin speed optimization
xlApp.Calculation = xlCalculationManual
xlApp.ScreenUpdating = False

'Loop
'Edit spreadsheets here
'End Loop

'end speed optimization
xlApp.Calculation = xlCalculationAutomatic
xlApp.ScreenUpdating = True

xlWb.Save
xlWb.Close
xlApp.Quit
Set xlWs1 = Nothing
Set xlWs2 = Nothing
Set xlWb = Nothing
Set xlApp = Nothing

 

[vindos79]

hi,
you might want to check out this link.

http://weblogs.asp.net/josh.robinson/archive/2004/03/29/101445.aspx

it says something about using ReleaseComObject(object)
hope it helps...!!!

 

[Jeff333333]

If you don't want to have problems when opening excel files try:

Dim objXLApp As New Excel.Application
Dim objXLBook As Excel.workBook
Dim currentWorksheet As Excel.workSheet
Const ERR_APP_NOTRUNNING As Long = 429

On Error Resume Next
'Set objXLApp = CreateObject("Excel.Application")
Set objXLApp = GetObject(, "Excel.Application")
If Err = ERR_APP_NOTRUNNING Then
Set objXLApp = New Excel.Application
End If

objXLApp.Application.DisplayAlerts = False
objXLApp.Application.ScreenUpdating = True

'on ouvre le fichier où ont été exportées les données
Set objXLBook = objXLApp.Workbooks.Open(FileName:=strFileName)

'code
'----
'---
'---

objXLBook.Save
objXLBook.Close
objXLApp.Application.DisplayAlerts = True
objXLApp.Quit

Set currentWorksheet = Nothing
Set objXLBook = Nothing
Set objXLApp = Nothing

I don't know why but if you turn on ScreenUpdating = True everything works fine when you want to use excel after having opened it with access!

 

[mikeski52]

I've been trying to access Excel objects from Access for a while now, and this code is the first thing I've seen that's supposed to do what I'm looking for. When I try to run some of this code tho, excel.application isn't recognized in Access. Is there a library I have to enable for this to work?

 

[jbpez]

Yes, go to Tools/References and click on Microsoft Excel 9.0 Object Library, or whatever version you have.

John Borges