Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 06-06-2018, 10:54 AM   #1
Frothingslosh
Premier Pale Stale Ale
 
Frothingslosh's Avatar
 
Join Date: Oct 2012
Location: Flint, Michigan, USA
Posts: 3,211
Thanks: 83
Thanked 457 Times in 413 Posts
Frothingslosh will become famous soon enough Frothingslosh will become famous soon enough
VBA to Close Excel Not Closing Excel

Okay, this one is driving me bananas.

I have a driver procedure that is supposed to call a number of sub-procedures, then close the spreadsheet I was importing from and quit Excel.

Here is the code. Please excuse the mess, it's still under development, so this isn't my final code by any means.

Code:
Private Sub cmdImportCase_Click()
 
Dim CaseData As Collection
Dim ValCheck As clsFieldData
 
On Error GoTo ErrHandler

    If Not CaseData Is Nothing Then Set CaseData = Nothing
    Set CaseData = New Collection
    HeaderRow = GetDBOption("ArchiveImport_HeaderRow", otNumber, dbeBackEnd)
    
    CurrentDb.Execute "DELETE * FROM tblArchiveCaseStaging", dbSeeChanges + dbFailOnError
    CurrentDb.Execute "DELETE * FROM tblArchiveClaimStaging", dbSeeChanges + dbFailOnError
 
    'Open the source file and assign values to xlApp, xlWorkBook, and xlWorkSheet.
    If OpenImportFile() Then
        'Verify the format of the source file.
        If (VerifyCaseRows() And VerifyColumnNames()) Then
            'The data file has been found and opened.  Import and stage the CASE information.
            If ImportHeader(CaseData) Then
                If ImportClaimData() Then
                    MsgBox "Import Complete."
                End If
            End If
        Else
            Beep
            ShowMsg "The source file is formatted incorrectly." & vbCrLf & vbCrLf & _
                    "If you feel you have received this message in error, please contact PIU App Support.", _
                    "INVALID FILE FORMAT", _
                    "VALIDATION FAILURE", _
                    mbCritical
        End If
    End If
    
    Me.RecordSource = "tblArchiveCaseStaging"
    
    Call ToggleVisibility(True)
    If Me.txtFileNumber.Visible Then Me.txtFileNumber.SetFocus
    
ProcExit:
    On Error Resume Next
    If Not xlWorkSheet Is Nothing Then Set xlWorkSheet = Nothing
    If Not xlWorkBook Is Nothing Then
        xlWorkBook.Close SaveChanges:=False
        Set xlWorkBook = Nothing
    End If
    If Not xlApp Is Nothing Then
        xlApp.Quit
        Set xlApp = Nothing
    End If
    Exit Sub
    
ErrHandler:
    Trap.Handle Me.Name, "cmdImportCase_Click"
    Resume ProcExit
    
End Sub
The Excel variable declarations are module-level:

Code:
'Early bound - FOR DEVELOPMENT ONLY
Dim xlApp As Excel.Application
Dim xlWorkBook As Excel.Workbook
Dim xlWorkSheet As Excel.Worksheet
 
'Late bound - FOR RELEASE VERSIONS
'Dim xlApp As Object
'Dim xlWorkBook As Object
'Dim xlWorkSheet As Object
The objects themselves are assigned in OpenImportFile. There is some extra stuff, but the actual assignments are pretty standard:

Code:
       'Open a new session of Excel and then open the workbook.
        Set xlApp = CreateObject("Excel.Application")
                
       'Open the indicated workbook and worksheet
        Set xlWorkBook = xlApp.Workbooks.Open(FileName:=SourceFilePath)
        Set xlWorkSheet = xlWorkBook.Sheets("INPUT FILE")
(I'm deliberately opening a new instance of Excel instead of using any existing one specifically so I can hide the workbook and close it when I'm done, since this is a straight validation and data import.)

So here is the situation:

While the cleanup in ProcExit works perfectly if the user has no other instances of Excel open, if they already have any open, then this code opens the spreadsheet being imported in a new, visible instance of Excel, then leaves it open rather than closing it out. Stepping through the cleanup code in ProcExit shows that every line is being executed; they just aren't DOING anything.

Any thoughts on why this is acting like this or how to fix that would be most appreciated.

__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Frothingslosh is offline   Reply With Quote
Old 06-06-2018, 10:56 AM   #2
Frothingslosh
Premier Pale Stale Ale
 
Frothingslosh's Avatar
 
Join Date: Oct 2012
Location: Flint, Michigan, USA
Posts: 3,211
Thanks: 83
Thanked 457 Times in 413 Posts
Frothingslosh will become famous soon enough Frothingslosh will become famous soon enough
Re: VBA to Close Excel Not Closing Excel

Also, I've checked with it Resume Next turned off, and no errors are being suppressed by it.
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Frothingslosh is offline   Reply With Quote
Old 06-06-2018, 11:58 AM   #3
JHB
Have been here a while
 
Join Date: Jun 2012
Location: In the south of Denmark (Jutland), near the German border.
Posts: 7,720
Thanks: 3
Thanked 2,082 Times in 2,037 Posts
JHB has a spectacular aura about JHB has a spectacular aura about
Re: VBA to Close Excel Not Closing Excel

I would also comment that out:
Code:
On Error GoTo ErrHandler
What happen if you only have xlApp.Quit in ProcExit:.

__________________
If the above post has helped you, why not take the time to say thank you, by pressing the "Thumbs up."
JHB is offline   Reply With Quote
The Following User Says Thank You to JHB For This Useful Post:
Frothingslosh (06-06-2018)
Old 06-06-2018, 12:30 PM   #4
Frothingslosh
Premier Pale Stale Ale
 
Frothingslosh's Avatar
 
Join Date: Oct 2012
Location: Flint, Michigan, USA
Posts: 3,211
Thanks: 83
Thanked 457 Times in 413 Posts
Frothingslosh will become famous soon enough Frothingslosh will become famous soon enough
Re: VBA to Close Excel Not Closing Excel

It's not throwing any errors at all, even after removing all error handling. I've stepped through the entire procedure, and everything works, except for closing out. It just outright ignores the Workbook.Close and Excel.Quit commands. Doesn't even throw any errors on them - it just process them and does nothing.

It appears to be something about saving changes - I've changed the workbook assignment to include ReadOnly:=True and the issue went away.

I don't know what precisely was causing it or how to fix it if I ever need to change a workbook and then import something (I tested with Workbook.Close SaveChanges:=True and the behavior never changed), but for now, I'll take what I can get. I would love to know WHY it was showing the workbook and refusing to close out, though, even if I DID find a work-around.

Oh, nearly forgot:

Quote:
Originally Posted by JHB View Post
What happen if you only have xlApp.Quit in ProcExit:.
Absolutely no changes. The workbook appears, Excel stays open, and no errors are thrown.
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by Frothingslosh; 06-06-2018 at 12:37 PM.
Frothingslosh is offline   Reply With Quote
Old 06-06-2018, 12:34 PM   #5
Frothingslosh
Premier Pale Stale Ale
 
Frothingslosh's Avatar
 
Join Date: Oct 2012
Location: Flint, Michigan, USA
Posts: 3,211
Thanks: 83
Thanked 457 Times in 413 Posts
Frothingslosh will become famous soon enough Frothingslosh will become famous soon enough
Re: VBA to Close Excel Not Closing Excel

Interestingly, unless I use that ReadOnly:=True, about 5 seconds after the procedure terminates, a pop-up appears in Excel saying 'ArchiveTest.xlsx is now available for editing. Chose Read-Write to open it for editing.' with a Read-Write and a Cancel button.
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Frothingslosh is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Closing Excel from Access beginner83 Modules & VBA 7 08-14-2013 07:24 AM
Access 2007 VBA - Excel Formatting Automation - Excel Instance Not Closing emprimi Modules & VBA 1 03-19-2012 07:59 AM
Excel not closing DreamGenius Modules & VBA 1 05-07-2009 03:06 AM
open excel & have access wait for excel to close krberube Modules & VBA 5 06-09-2005 07:15 AM
Closing Excel files from VB Elvis Modules & VBA 2 08-15-2002 08:11 AM




All times are GMT -8. The time now is 03:06 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World