Use a macro:

Sub CloseIt()

' Keyboard Shortcut: Ctrl+q
'
ActiveWorkbook.RunAutoMacros Which:=xlAutoClose
End Sub


Originally posted by John Allen
Hi All , This is going to sound really silly to all you experts out there...
but what is the correct command to use for closing Excel with Visual basic. I am new to VB and am using a book with writen examples , but , the example shows the following:

xlsApp.Workbooks.Close
xlsApp.Quit


This does close the workbook , but not the application.?
Any suggestions.
regards
John Allen
(Newbie)

Originally posted by sugarflux
Much easier than that, you can just use:

Application.Quit

You need not close the workbook first with this command. If you want to specify whether changes are saved in the workbook, do these first. This will stop the Save Changes? message being displayed.

ActiveWorkbook.Save
Application.Quit

If you don't want to save changes, use:

ActiveWorkbook.Saved = True
Application.Quit

This will make Excel think that the workbook has already been saved so it will not prompt the user.

Good luck,
sugarflux x

Thanks for that , worked with no probs...

Originally posted by RickKnight
Use a macro:

Sub CloseIt()

' Keyboard Shortcut: Ctrl+q
'
ActiveWorkbook.RunAutoMacros Which:=xlAutoClose
End Sub

I tried this but nothing seemed to happen...probably something I'm not doing.

I have tried all the solutions listed:

Application.Quit
varname.Application.Quit
varname.Quit
oApp.DoCmd.Quit acQuitSaveNone
ActiveWorkbook.RunAutoMacros Which:=xlAutoClose

I get basically the same results no matter what I do:

Excel visibly quits (the application closes), but an artifact or remnant of Excel remains (if you go to Task Manager, you will see it is still running). If you are running the automation a few times in a row, you will end up getting quite a few instances of Excel invisible but open (as shown by Task Manager), with the only way to close them being via Task Manager. Does anyone know how to resolve this?

Thanks,
Craig

Hi,

Sounds strange to me. Can You post the procedure enabling us to see how You connect to XL and so on.

TIA,
Dennis

Check your code to make sure that when you open Excel you are only opening one instance of it because if you are opening two, for example, when you run the quit command it will only close one.

Try closing down all your workbooks before running the quit command as well.

I am only opening one instance, and even tried closing the workbook before quitting. It didn't work either. Here is the code:


Dim oApp As Object

DoCmd.OutputTo acReport, "SOA1", "MicrosoftExcel(*.xls)", "C:\isaudits\SOA1.xls", False, ""

Set oApp = CreateObject("Excel.Application")
oApp.Visible = True
'Only XL 97 supports UserControl Property
On Error Resume Next
oApp.UserControl = True

Workbooks.Open Filename:= _
"C:\SOA\SOA1.xls"
Windows("SOA1.xls").Activate
Columns("C:C").select
Selection.Insert Shift:=xlToRight
Range("C1").select
ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[-2],"" / "",RC[-1])"
Range("C1").select
Selection.Copy
Selection.SpecialCells(xlCellTypeLastCell).select
Range("C1:C17").select
ActiveSheet.Paste
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
ActiveSheet.Paste
Range("C2").select
Cells.Replace What:=" / ", Replacement:=" ", LookAt:=xlWhole, SearchOrder _
:=xlByRows, MatchCase:=True
Range("C2").select
ActiveWorkbook.SaveAs Filename:= _
"C:\SOA\SOA1_new.xls", FileFormat:= _
xlExcel5, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False

Windows("SOA1_new.xls").Activate
Workbooks.Open Filename:= _
"C:\SOA\Status_new.xls"
Range("A2").select
Windows("SOA1_new.xls").Activate
Range("C2:J17").select
Selection.Copy
Windows("Status_new.xls").Activate
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("A2").select
ActiveWorkbook.SaveAs Filename:= _
"C:\SOA\Status_new.xls" _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

'oApp.Application.Quit
'Application.Quit
'oApp.DoCmd.Quit acQuitSaveNone
'ActiveWorkbook.RunAutoMacros Which:=xlAutoClose
oApp.Quit


Any help would be appreciated.

Craig Silberman

You seem to be saving these workbooks but not actually closing them.

Try adding an

oApp.Activeworkbook.Close SaveChanges:=False

after you have finished using the workbook and before you use

Application.Quit

Also, to check what version of Excel is open after you closed down your application, try using this after all the code you pasted

Dim myOpenWorkbooks as String
Dim myWorkbooks, myWorkbook as Integer

myWorkbooks = oApp.Workbooks.Count
myWorkbook = 0
myOpenWorkbooks = ""

Do
myWorkbook = myWorkbook + 1
myOpenWorkbooks = myOpenWorkbooks & "; " & oApp.Workbooks(myWorkbook).Name
Loop Until myWorkbook = myWorkbooks

MsgBox myOpenWorkbook

This will display the names of any open workbooks.

Hope this helps!
sugarflux

Thanks for the code to try, but it didn't work. Adding the close statement (which I had already tried a few variants of) did no good - although excel is closed, it remains open in task manager. This causes some problems as you run the code a few times (you build up applications open and taking up menu, as well as causing some problems in the operation of the code on occasion).

Adding the rest of the code you suggested caused an endless loop. The contents of myOpenWorkbook was always "" and myWorkbooks was always 0. This is because for all intents and purposes, Excel is closed as far as Access is concerned.

Anyone have any other suggestions?

Thanks,
Craig Silberman

Hi,

You need to set a reference to Microsoft Excel Object Library x.x in the VB-editor via the command Tools | Reference... and then add the following code inte a standardmodule:


Option Explicit

Sub Control_XL()
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim bXLRunning As Boolean
Dim stMainWbook As String

stMainWbook = "C:\Test.xls"

On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")

If Err Then
bXLRunning = True
Set xlApp = New Excel.Application
End If

On Error GoTo Err_Handler

Set xlBook = xlApp.Workbooks.Open(FileName:=stMainWbook)

If bXLRunning Then
xlApp.Quit
End If

Set xlBook = Nothing
Set xlApp = Nothing
Exit Sub

Err_Handler:
MsgBox stMainWbook & " caused a problem. " & Err.Description, vbCritical, _
"Error: " & Err.Number
If bXLRunning Then
xlApp.Quit
End If
End Sub



Pleas run above code and let us now the outcome of it. If OK we then take the next step :)

Kind regards,
Dennis

There is one accepted way to control another application, and that is through OLE. But there is also an accepted, gentlemanly protocol which determines how you should handle your connection vis a vis any others...

If you need to use Excel, you must do it in a way that has no impact on any other application's use of Excel, nor anything that the user may be doing directly from the user interface. The prescribed way to do this is to address all of your attention and your commands to OLE document objects (in this case a "spreadsheet" or "workbook"), and not to the servers which support them.

OLE knows what applications need to be started, and whether they need to be started or not. It also knows when those applications should be closed down. You should not tell Excel to terminate because, although it will obey you, doing so pulls the rug out of whatever the user might be doing! Leave that to the OLE subsystem only.

Xl-Dennis - I tried your code, and the result was - Nothing. There was no error reported, and the application remained open in Task Manager. Thanks anyway.

sundialsvcs - Can you supply any suggestions on how to do what I'm trying to accomplish in exporting, merging and formatting? Any possible chance you have some snippet of code I can look at?

Thanks,
Craig

I was having the same issue with the excel process not quitting. My problem was not using fully qualified excel references. See link below

http://forums.devshed.com/archive/52/2003/12/3/86272

Hope this helps you

I was having the same problem and solved it by adding the following lines of code to get the window handle and then activate it and after I saved the workbook I sent the WM_CLOSE message:

Private Declare Function SendMessage Lib "user32" Alias "SendMessageA" _
(ByVal hWnd As Long, ByVal wMsg As Long, ByVal wParam As Long, ByVal lParam As Long) As Long

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

Private Declare Function ShowWindow Lib "user32" Alias "ShowWindow" (ByVal hwnd As Long, ByVal nCmdShow As Long) As Long

Dim WinHandle As Long
Dim curApp As Excel.Application

Do While (FindWindow("XLMain", vbNullString)) <> 0
WinHandle = FindWindow("XLMain", vbNullString)
ShowWindow WinWnd1, SW_SHOWNORMAL

Set curApp = GetObject(, "Excel.Application")
curApp.ActiveWorkbook.SaveAs "C:\backup\Excel" & i & "_"
i = i + 1

SendMessage WinHandle, WM_CLOSE, 0, 0

Set curApp = Nothing

Loop

Let me know if this helps.

Steve