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

 

[NdNoviceHlp]

Hi Bisel. Are you using more than 1 instance of XL to open the wbcopyfrom wb? You should only use one. Do you have any wb close event code in the wbcopyfrom wb? This should work. HTH. Dave

Application.DisplayAlerts = False
wbCopyFrom.Close SaveChanges:=False
Set wbCopyFrom = Nothing
Application.DisplayAlerts = True

 

[bisel]

Hi Bisel. Are you using more than 1 instance of XL to open the wbcopyfrom wb? You should only use one. Do you have any wb close event code in the wbcopyfrom wb? This should work. HTH. Dave

Application.DisplayAlerts = False
wbCopyFrom.Close SaveChanges:=False
Set wbCopyFrom = Nothing
Application.DisplayAlerts = True

Thx for reply.

No. I have only a single instance of Excel running. I do not have any other wb close event. I tried the code you recommended. Excel crashes at the point where I attempt to close the workbook.

I have found numerous threads using Google where many people are having this problem ... i.e., trying to use VBA to close a workbook.

Steve

 

[bisel]

Here is a bit VBA code I am using to test things out. This code causes Excel to crash.

Sub testclose()

Dim vFile As Variant
Dim wbCopyTo As Workbook
Dim wbCopyFrom As Workbook

Set wbCopyTo = ActiveWorkbook ' set the current active workbook equal to wbCopyTo to be the target workbook

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
    End If

Set wbCopyFrom = Workbooks.Open(vFile)

' Attempt to close workbook

MsgBox "Attempting to Close the source file"

Application.DisplayAlerts = False

wbCopyFrom.Close SaveChanges:=False
Application.DisplayAlerts = True



End Sub

 

[rlv01]

What happens if you save the changes? Does it also crash?

Sub testclose()

    Dim vFile As Variant
    Dim wbCopyTo As Workbook
    Dim wbCopyFrom As Workbook

    Set wbCopyTo = ActiveWorkbook                     ' set the current active workbook equal to wbCopyTo to be the target workbook

    '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 vFile = False Then
        Exit Sub
    End If

    Set wbCopyFrom = Workbooks.Open(vFile)

    ' Attempt to close workbook

    MsgBox "Workbook to close:" & vbCrLf & vbCrLf _
         & "Name: " & wbCopyFrom.Name & vbCrLf _
         & "Type: " & TypeName(wbCopyFrom), vbOKOnly, Application.Name

    'Application.DisplayAlerts = False
    Select Case MsgBox("Attempting to Close the source file" & vbCr & vbCr & "Save Changes?", vbYesNo + vbQuestion + vbDefaultButton2)
    Case vbYes
        DoEvents
        wbCopyFrom.Close SaveChanges:=True
        DoEvents
    Case vbNo
        DoEvents
        wbCopyFrom.Close SaveChanges:=False
        DoEvents
    End Select
    Application.DisplayAlerts = True
End Sub

 

[rlv01]

Additionally, will your macro run when Excel is started in Safe mode?

 

[bisel]

Additionally, will your macro run when Excel is started in Safe mode?

Nope. Still crashes.

 

[bisel]

What happens if you save the changes? Does it also crash?

Sub testclose()

    Dim vFile As Variant
    Dim wbCopyTo As Workbook
    Dim wbCopyFrom As Workbook

    Set wbCopyTo = ActiveWorkbook                     ' set the current active workbook equal to wbCopyTo to be the target workbook

    '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 vFile = False Then
        Exit Sub
    End If

    Set wbCopyFrom = Workbooks.Open(vFile)

    ' Attempt to close workbook

    MsgBox "Workbook to close:" & vbCrLf & vbCrLf _
         & "Name: " & wbCopyFrom.Name & vbCrLf _
         & "Type: " & TypeName(wbCopyFrom), vbOKOnly, Application.Name

    'Application.DisplayAlerts = False
    Select Case MsgBox("Attempting to Close the source file" & vbCr & vbCr & "Save Changes?", vbYesNo + vbQuestion + vbDefaultButton2)
    Case vbYes
        DoEvents
        wbCopyFrom.Close SaveChanges:=True
        DoEvents
    Case vbNo
        DoEvents
        wbCopyFrom.Close SaveChanges:=False
        DoEvents
    End Select
    Application.DisplayAlerts = True
End Sub

Yes. Removed the SaveChanges:=False and replaced with SaveChanges:=True. Still crashes.

 

[rlv01]

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

 

[bisel]

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

Yes. I created blank workbook and saved as macro enabled workbook. Reopened this new workbook and ran the code above. Selected the workbook to open from the dialog box and when it attempted to close, Excel crashed.

When I reopen an instance of Excel, there is a window that shows the two files ... the original file and one I tried open/close. I can choose one or both of these files and attempt repair or whatnot. I generally just close that window.

Steve