Using VBA to close a workbook results in program hanging up and then opens repair pane on next open

[bisel]

I have a macro in my workbook that opens another Excel workbook to copy some data from it. At the end of my macro, I close the source workbook but lately this is resulting in the macro hanging up and after a period of time Excel shuts down. There is no error message, just hangs up.

I have tried two methods. In both methods the name wbCopyFrom, is the name of the source workbook.

Method 1 is quite simple ... only a single line of code ...

:
:
wbCopyFrom.Close SaveChanges:=False
:
:

I just tried this method but with same result ...

:
:
wbCopyFrom.Activate
Application.DisplayAlerts = False
ActiveWorkbook.Close
Application.DisplayAlerts = True
:
:

Any assistance is appreciated.

Regards,

Steve

 

[dmt32]

Hi
just an idea but see if update to code makes any difference.

Sub testclose()
    
    Dim vFile       As Variant
    Dim wbCopyTo    As Workbook
    Dim wbCopyFrom  As Workbook
    
    ' set the current active workbook equal to wbCopyTo to be the target workbook
    Set wbCopyTo = ActiveWorkbook
    
    MsgBox "Prompting To Select source file"
    
    ' open source workbook and set the variable for defining the source workbook
    vFile = Application.GetOpenFilename("Excel Files (*.xl*)," & _
    "*.xl*", 1, "Select Excel File", "Open", False)
    
    If Cancel Then Exit Sub

    
    On Error GoTo myerror
    Set wbCopyFrom = Workbooks.Open(vFile, 0, True)
    
    ' Attempt to close workbook
    
    MsgBox "Attempting To Close the source file"
    
myerror:
    If Not wbCopyFrom Is Nothing Then wbCopyFrom.Close False
    If Err <> 0 Then MsgBox (Error(Err)), 48, "Error"
End Sub

Dave

 

[bisel]

If you create a new, blank workbook and paste in your test code, does it crash then as well?

I am finding that if I open a file version that is older, then Excel does not crash. There seems to be a relationship when opening a newer file version that is causing the crashing.

 

[rlv01]

Does it matter if the file being opened is .xlsx, xlsm, or .xlsb?

 

[rlv01]

What about if you experiment with .SaveCopyAs or .SaveAs?

    With wbCopyFrom
        FilePath = .Path
        Ext = Split(.FullName, ".")(1)
    End With

    'CopySaveAs test
    wbCopyFrom.SaveCopyAs Filename:=FilePath & "\TestWB1." & Ext
    Application.DisplayAlerts = False
    Set WB2 = Workbooks.Open(FilePath & "\TestWB1." & Ext)
    Application.DisplayAlerts = True

    Select Case MsgBox("Close?", vbYesNo, WB2.Name)
    Case vbYes
        WB2.Close False
    End Select

    'File SaveAs test
    Application.DisplayAlerts = False
    wbCopyFrom.SaveAs Filename:=FilePath & "\TestWB2." & Ext
    Application.DisplayAlerts = True

    Select Case MsgBox("Close?", vbYesNo, wbCopyFrom.Name)
    Case vbYes
        wbCopyFrom.Close False
    End Select

 

[rlv01]

Lastly, do you have any add-ins installed that could be causing trouble?

(though 'safe mode' should have tested that)

 

[NdNoviceHlp]

You can trial changing your variable declaration from wb to object. Remove the DoEvents(x2) and set the copywb to nothing after you close it. Not sure that your msgbox use is good...

Dim wbCopyFrom As Object
Application.DisplayAlerts = False
If MsgBox(prompt:="Do you want to save changes?", Buttons:=vbYesNo, Title:="SAVE CHANGES?") = vbYes Then
wbCopyFrom.Close SaveChanges:=True
Else
wbCopyFrom.Close SaveChanges:=False
End If
Application.DisplayAlerts = True
Set wbCopyFrom = Nothing

OR....Go to your task manager and see if any XL processes are running before you open any XL wb. Open your wb and make sure that only 1 XL process is active.
OR... If that checks out then... go to the registry editor. Select Hkey Current users, then software, then Microsoft, then Office, then Version number, then Excel and see if there is a resiliency folder. If there is, then you have copy(s) of crashed file(s) saved for restore. You can click on the folder and select the number and right click and delete the file reference but be warned that messing with the registry is bad. However, I have done this many times when XL has gone wonky without any apparent adverse effects and it fixed the wonky. (I posted here on this forum how to VBA this, but that was for a few versions previous to now). Not real sure that this will help you but it may be good to just find out if the resiliency folder and file reference numbers exist without deleting anything. Dave

 

[bisel]

This is really getting weird.

Trying to work my through things here and I commented out the VBA code that closes the file that was opened at the start of the macro. The result is that the sub ends and the source file (where data was copied from) and the target file (where the copied data was pasted to) are both remaining open. The sub has ended and the user is then left with two instances of Excel ... each instance in their own window.

Now for the strange part. If I attempt to perform any functions in either of the two Excel instances, Excel crashes. It then attempts to restart Excel and it does ... except the Document Recovery window is open indicating that the target file was repaired during recovery. The source file also re-opens and I can now close that file without problems. Leaving me the instance of the target file with the Document Recovery window. But upon closing the document recovery window I discover that macros have been disabled in the recovered file.

My conclusion is that there may not be anything wrong with the VBA code itself. There is something going on in Excel. I went to look at my Add-ins and saw that Logitech had added an add-in that was not there before. Removed it, however this made no difference at all. Same issue.

Would lover to hear from anyone with their thoughts.

Steve

 

[rlv01]

Time to reinstall Excel.

 

[bisel]

Time to reinstall Excel.

I have Office 365 and I tried that already. No difference. But, I might just give it another try. It cannot hurt anything.

Regards,

Steve

 

[bisel]

Time to reinstall Excel.

Thanks for the suggestion.

I uninstalled Office 365 and made sure that the registry did not contain any references to MS Office installed. Rebooted the PC and then reinstalled Office 365 products on the PC.

Everything works as it should now. Thank you again for the suggestion. Whatever occurred now seems to be fully resolved.

Steve