Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2002
    Location
    Alabama
    Posts
    2

    Unanswered: Can't close Excel from Access VBA

    Hi,

    I am having a problem getting VBA to mind me. I am creating an instance of Excel from Access using VBA (works fine), creating charts using Excel (works fine), and closing the instance of Excel (it ignores me). Excel not closing here causes other problems further down my code. The code I am using appears below. Any input as to why Excel is doing this would be greatly appreciated.

    Thanks

    Sub Centerline()
    Dim Filename_CTL As String
    Dim AppExcel As Excel.Application
    Set AppExcel = New Excel.Application

    With AppExcel
    .Application.ScreenUpdating = False
    .Application.DisplayAlerts = False
    End With
    ChDrive "d"

    Dim RSItem As New ADODB.Recordset
    Set RSItem = New ADODB.Recordset
    RSItem.Open "select matrix_id from completed_grid_files", CurrentProject.Connection

    Do While Not RSItem.EOF
    AppExcel.Workbooks.OpenText filename:="D:\Data Compilation\Grid Files\Centerline Profiles\" + RSItem("matrix_id") + ".dat", DataType:=xlDelimited, Space:=True
    AppExcel.ActiveSheet.Name = "Datafile"
    AppExcel.Sheets.Add
    AppExcel.ActiveSheet.Name = "Centerline Data"
    get_xvals
    get_zvals
    Create_Charts
    AppExcel.ActiveWorkbook.SaveAs filename:="D:\Data Compilation\Data Presentation\Excel Centerline Profiles\" + RSItem("matrix_id") + ".xls", FileFormat:=xlNormal
    AppExcel.ActiveWorkbook.Close
    RSItem.MoveNext
    Loop

    With AppExcel
    .DisplayAlerts = True
    .ScreenUpdating = True
    .Quit
    End With
    AppExcel.ActiveWorkbook.Close
    Set AppExcel = Nothing
    Set RSItem = Nothing

    End Sub

  2. #2
    Join Date
    Nov 2002
    Location
    Ohio
    Posts
    90
    Two suggestions:

    Remove the AppExcel.ActiveWorkbook.Close that follows the .Quit.

    If that doesn't work, try changing .DisplayAlerts=True to False.

    Good Luck,
    Bruce Baasch

  3. #3
    Join Date
    Dec 2002
    Location
    Alabama
    Posts
    2
    I made the suggested change and it still does not work. I also tried changing
    With AppExcel
    .DisplayAlerts = False
    .ScreenUpdating = True
    .Quit
    End With

    to

    With AppExcel
    .DisplayAlerts = False
    .ScreenUpdating = True
    .Application.Quit
    End With

    with no success either. Any other thoughts would be appreciated.

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    ▲ Can we delete spammers if we don't like them?

    Edit Thanks for deleting the post above mine without deleting mine modz!
    Last edited by StarTrekker; 06-22-09 at 23:36.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    Oct 2004
    Location
    Melbourne, Australia
    Posts
    201

    Closing down Excel in VBA

    I have been playing around with VBA and Excel for quite a while and I have noticed that unless you run Excel in a Visible mode, it does not close down when you Quit, but rather runs as a process and not a program. You can verify this by running the Task Manager and checking the process list. At this point, you can, of course, end the process manually. If you don't, the next time you try and start up the same worksheet, it tells you it is already running.

    One would imagine that there is a system function in the Excel VBA library that would close down Excel as a process but I have not found it yet. Alternatively, it might close down properly if you make it Visible just before quitting.

    Weird and wonderful are the ways of Microsoft !

  6. #6
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580

    Question "Weird and wonderful are the ways of Microsoft"

    Just your coding Jim - Post an example of how you automate Excel - you are probably not clearing your object variables.
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

Posting Permissions

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