Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Dec 2003
    Location
    Wellingborough , UK
    Posts
    3

    Unanswered: Closing Excel with Visual Basic

    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)

  2. #2
    Join Date
    Sep 2003
    Location
    Cincinnati, Oh USA
    Posts
    203

    Re: Closing Excel with Visual Basic

    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)
    Rick Knight
    KnightShift Office Solutions and Horse Breaking
    VB, VBA, FileMaker, Access Solutions, Web Solutions

  3. #3
    Join Date
    Aug 2003
    Posts
    106

    Better than that...

    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

  4. #4
    Join Date
    Dec 2003
    Location
    Wellingborough , UK
    Posts
    3

    Talking Re: Better than that...

    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...

  5. #5
    Join Date
    Dec 2003
    Location
    Wellingborough , UK
    Posts
    3

    Unhappy Re: Closing Excel with Visual Basic

    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.

  6. #6
    Join Date
    Jan 2004
    Posts
    5

    I also have this problem

    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

  7. #7
    Join Date
    Dec 2003
    Location
    Östersund Sweden
    Posts
    60
    Hi,

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

    TIA,
    Dennis
    Kind regards,
    Dennis

  8. #8
    Join Date
    Aug 2003
    Posts
    106

    More than one instance?

    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.

  9. #9
    Join Date
    Jan 2004
    Posts
    5

    More information...

    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

  10. #10
    Join Date
    Aug 2003
    Posts
    106

    Saving but not closing

    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

  11. #11
    Join Date
    Jan 2004
    Posts
    5

    Thanks for the try, but...

    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

  12. #12
    Join Date
    Dec 2003
    Location
    Östersund Sweden
    Posts
    60
    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:

    Code:
    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
    Kind regards,
    Dennis

  13. #13
    Join Date
    Oct 2003
    Posts
    706

    Exclamation

    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.
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

  14. #14
    Join Date
    Jan 2004
    Posts
    5

    Still having problems

    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

  15. #15
    Join Date
    Jan 2004
    Posts
    1

    quitting excel application from access

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •