Excel stops working after VBA runs workbook.close on a big file (1.7GB)

[Bassie]

Hi,

Is this a known problem that VBA stops working on workbook.close if it is trying to close a big file (1.7GB).

My code basically only needs to open the file, copy all the information into a sheet on the workbook with the macro and then close the file. It does everything perfectly but on the workbook.close excel crashes (on smaller workbooks excel doesn't crash)

The code until crash:

Dim wb, QueryWB As Workbook
Dim MacroWS, Temp As Worksheet
Set wb = ActiveWorkbook
Set MacroWS = Sheets("Macro")

'Let user locate the query output
MsgBox ("Please locate the query output")
Dim FilePath As String
FilePath = Application.GetOpenFilename()
        
        'If user cancels file selection
    If (FilePath) = "False" Then Exit Sub

Workbooks.Open (FilePath)
Set QueryWB = ActiveWorkbook

Application.EnableEvents = False
Application.ScreenUpdating = False
Application.DisplayAlerts = False

wb.Activate

'Copy data from QueryWB to Macro WB
QueryWB.Activate
Range("A1").CurrentRegion.Copy
wb.Activate
Range("A1").PasteSpecial
QueryWB.Close
^^^^ [B]excel crash[/B]

 

[offthelip]

You could try clearing the clipboard after the paste and before the close which might clear a bit of memory in case that is the problem
add this line:

Application.CutCopyMode = False

 

[Skyybot]

Also, if you're going to do A Workbook.Close event with DisplayAlerts = False, you need to specify a Save option. QueryWB.Save

 

[Skyybot]

Also, if you're going to do A Workbook.Close event with DisplayAlerts = False, you need to specify a Save option. QueryWB.Save

At least this worked for me when I had an Application freeze with one of my Subs

 

[Bassie]

You could try clearing the clipboard after the paste and before the close which might clear a bit of memory in case that is the problem
add this line:

Application.CutCopyMode = False

Thanks that fixed it !