Excel quitting for some unknown reason - doesn't even prompt about saving

[PTP86]

Hi

A problem that's only started happening on my spreadsheet in the last couple of months.
I've got some VBA that works fine if I sit there and watch it.....it does everything it should. It finishes all of its loops and Excel stays open as it should.

Whereas if I don't sit there watching for an hour and instead I [Ctrl][Alt][Delete] to lock the computer and go away....I come back after 30 minutes to check on it and see it's still running ok and then lock the computer again for another half hour. I eventually come back and find that it finished all its loops but Excel closed itself without saving.

NB: How do I know it did all of its loops? I'm confident because I can see in the properties of the Access file that it calls on, what time the Access file was last closed.

I've done a [Ctrl][F] inside the VBA and searched within Current Project for "Workbook.Close" or "Application.Quit" and it certainly isn't there - and has never been there. Nor is there anything telling it not to display alerts. So it ought to be asking are you sure you want to quit without saving changes.

Any idea why this is happening or how I could catch when it's about to happen so that I can see what's causing it?




I doubt the opening and closing of a database connection is relevant. But in case it is....Here's that code:

Dim Conn as ADODB.Connection
Dim NumberofRecords as Long


Sub ImportData()

    Dim AccessFile as string
    Dim i as Byte
    Dim QueryTableToImport as String
    Dim QueryOutputRange as String
    Dim ImportHeadingsTRUEFALSE as Boolean
    

    AccessFile = Range("DBPath")

    Set Conn = New ADODB.Connection
    Conn.Open "Provider = Microsoft.ACE.OLDDB.12.0;Data Source=" & AccessFile

    Conn.Execute "DELETE FROM Criteria"

    Conn.Execute "INSERT INTO Criteria ([Reference Number]) values(" _
             & "'" & Range("RefNo") & "')"

    Do Until Len(Range("DataToImport").cells(i,1)) = 0
        QueryToImport = Range("DataToImport").cells(i,1)
        QueryOutputRange = Range("DataToImport").cells(i,2)
        ImportHeadingsTRUEFALSE = Range(DataToImport").cells(i,3)

        Call ImportQueryResults(QueryToImport, QueryOutputRange, ImportHeadingsTRUEFALSE)

        Range("RecordCount").cells(i) = NumberofRecords
    Loop

    Conn.close
    Set Conn = Nothing

End Sub



Sub ImportQueryResults(QueryName As String, OutputRange As String, ImportHeaders As Boolean)

    Dim RS as ADODB.RecordSet
    Dim strSQL as string

    Set RS = New ADODB.Recordset
    strSQL = "SELECT [" & QueryName & "]"
    strSQL = strSQL & ".* FROM ["
    strSQL = strSQL & QueryName & "];"

    RS.Open strSQL, Conn, adlockReadOnly, adCmdText

    NumberofRecords = RS.RecordCount

    If NumberofRecords > 0 then
         Range(OutputRange).CopyFromRecordset RS
    End if

    'Plus a bit of code for the handling of whether headers are wanted
    
    RS.Close
    Set RS = Nothing
End Sub

Thanks

 

[PTP86]

I've thought of a workaround & it's not rocket science! Just put MsgBox("Run Complete") at the very end of the code. That seems to get around it.
If I do that, then when I get back from lunch I can just click OK and it all stays loaded as it should.

Still, I don't like how without that, it quits Excel when not instructed.
It seems as though it quits Excel when the run is finished only if the computer is locked as you do when you leave your desk

 

[Logit]

.
My experience here as shown that using CTRL / ALT / DEL or CTRL / BRK causes problems with Excel/VBA.

In my case, my code has become corrupted ... macros stopped working after several times of using those key combinations ... required me to
shut everything down and restart the computer. In a majority of cases, even after re-starting the computer, the Excel file was still corrupted requiring
me to retype everything from the beginning. Nothing I did to repair the corrupted file worked.

Although it may be bothersome to close Excel / Access and then be required to restart it after lunch (or whenever), it really is the best approach. At least
for me it is.

Having to completely rebuild a workbook is not my idea of effective use of resources.

 

[PTP86]

Although it may be bothersome to close Excel / Access and then be required to restart it after lunch (or whenever), it really is the best approach. At least
for me it is.

Thanks for that Logit

Unfortuntely this pattern of leaving Excel running while the computer is locked, is unavoidable for me.
The code I've written is reasonably efficient - it takes just 1 second per customer that it has to process.
But there can be 1000's of customers. So it needs to be left to run during lunch / overnight sometimes

Yes I used to find that pressing [Ctrl][Alt][Del] while Excel was the active application sometimes caused "Not Responding" / Crash.
So I avoid doing that by opening Notepad and pressing [Ctrl][Alt][Del] with Notepad the active application.