Solved: Closing file takes A LONG TIME

[bassnsjp]

Im using MS 2003 in a Win XP Pro environment. I cannot connect to my PC that has the code so I must re-type a small example. I've completed a set of macros that perform an extensive comparsion between required and available inventory. Im now in the process of fine tuning it to eliminate how long it takes to run. I inserted time stamps throughout and have narrowed down one process that is taking 30+ seconds. The process is actually just closing a workbook without saving it. Its odd because I copy/close/use other workbooks that are larger and they take only a few seconds to process. Any suggestions/ideas would be greatly appreciated, thanks in advance.

[VBA]
sub Main

call xyz

end sub

sub xyz

Dim start
Dim finish

' This process takes less than a second
start = Timer
Workbooks("Invwkb").Worksheets("AvailInv").Activate
Cells.Select
Selection.Delete Shift:=xlup
finish = Timer
Debug.Print "Delete Process: " & finish - start

' This process takes less than a second
start = Timer
Workbooks("AVLwkb").Activate
Cells.Select
Selection.Copy
finish = Timer
Debug.Print "Delete Copy: " & finish - start

' This process takes less than a second
start = Timer
Workbooks("Invwkb").Worksheets("AvailInv").Activate
Cells.Select
Selection.Paste
finish = Timer
Debug.Print "Delete Paste: " & finish - start

' This process takes 30+ seconds ???? why ????
start = Timer
Application.DisplayAlerts = False
Workbooks("AVLwkb").Close savechanges:=False
Application.DisplayAlerts = True
finish = Timer
Debug.Print "Delete Close: " & finish - start

end sub
[/VBA]

[JONvdHeyden]

Does your destination workbook contain many formula? Does that workbook typically take a long time to calculate. You should considering switching the Calculation to xlManual.

[bassnsjp]

The AVLwkb workbook that is being closed contains no formulas, just data. The same data that is copied into the Invwkb along with alot of additional information and the Invwkb takes only about 10 seconds to save and close.

When I use the AVLwkb workbook in Excel and not in VBA the file closes less than a second.

[GTO]

I doubt it, but just for giggles, try CutCopyMode=False
[vba]
' This process takes 30+ seconds ???? why ????
Start = Timer
Application.CutCopyMode = False
Application.DisplayAlerts = False
Workbooks("AVLwkb").Close savechanges:=False
Application.DisplayAlerts = True
finish = Timer
Debug.Print "Delete Close: " & finish - Start
[/vba]

[bassnsjp]

Jon,

Yesterday I had the opportunity to set and reset the calculations to xlManual and xlAutomatic (after closing the file). Guess what, the file closed in record time, only taking a few seconds. I double checked and there are no formulas in that workbook so, I don't know why it would affect how it would close. Nonetheless, it worked, thanks for the suggestion.

Steve

[bassnsjp]

GTO,

Please see my reply to Jon. For whatever reason setting and resetting xlManual calculations worked. I plan to stay with that, but if I have an opportunity I will give your suggestion a try. Thank you for taking the time to reply.

Steve