Macro Crashing Excel

[collin8579]

Good morning,

This macro is causing many peoples excel to crash. I can't reproduce it myself, but about 2/3 of the users have this problem a good bit of the time. Like Crash and reboot and the files are corrupted type of crash.

General purpose:
Open an excel workbook from a network drive
copy and paste from the local workbook to that one
Save the excel file back onto the network drive
****This triggers an internal report to run off the info in the now saved excel sheet****
Refresh a query to update the resultant report

Sub updateorders()


    Dim MyPath          As String
    Dim MyFile          As String
    Dim Wkb2             As Workbook
    Dim wb1 As Workbook
    Dim lastrow As Long

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    
    
    getbook = ActiveWorkbook.Name
    Set wb1 = ActiveWorkbook
     Application.Wait (Now + TimeValue("00:00:02"))
     Set sht = wb1.Worksheets("Orders Working On")
     lastrow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row

    MyFile = "\\netapp4\common\Service Delivery\Reporting and Analysis\order list.xlsx"

    Set Wkb2 = Workbooks.Open(MyFile)
    Application.Wait (Now + TimeValue("00:00:02"))
    Wkb2.Activate
    Application.Wait (Now + TimeValue("00:00:02"))
    Range("A2:C100").Select
    Selection.ClearContents
    Application.Wait (Now + TimeValue("00:00:02"))
    

    Wkb2.Worksheets("sheet1").Range("A2:c" & lastrow).Value = wb1.Worksheets("Orders Working On").Range("A2:c" & lastrow).Value
     Application.Wait (Now + TimeValue("00:00:005"))
     Wkb2.Close savechanges:=True


        wb1.Worksheets("CCIReport").Select
    Application.Wait (Now + TimeValue("00:00:005"))

On Error Resume Next
    ActiveWorkbook.Connections("Query - CCIreport").Refresh

    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    
    wb1.Worksheets("Main").Select
End Sub

Originally when the problem arose I thought their computers were trying to go "too fast" and put in the application.wait functions. Since I can't replicate it and they can, I figured it might be the computer speed. (I have a better laptop than they)
This didn't help

I'm pretty out of ideas and would appreciate any suggestion you all have.
I feel like it has to do with opening the network file maybe? But I can't pin it down. Especially because I can't replicate the issue myself and when I run it through steps with other people it doesn't crash. (another reason I thought it might be "going too fast")


Thank you for any help/ideas.

 

[RoryA]

I think it's going to be very difficult for us to debug anything that is environmental. I'd suggest you add some logging to your code so that it for example writes progress to a text file so that you can try and narrow down exactly how far it gets before crashing. There doesn't appear to be anything inherently wrong with your code.

 

[collin8579]

I think it's going to be very difficult for us to debug anything that is environmental. I'd suggest you add some logging to your code so that it for example writes progress to a text file so that you can try and narrow down exactly how far it gets before crashing. There doesn't appear to be anything inherently wrong with your code.

I've never used any logging but I like the idea, I thought any internal logging would go poof when excel crashed.
I'll look it up and go from there.
Thanks,

 

[RoryA]

If you use Open, Write and Close statements, you can log the progress to a text file which will persist even if your code goes boom.

 

[collin8579]

If you use Open, Write and Close statements, you can log the progress to a text file which will persist even if your code goes boom.

Google is failing me for this question:
I've got a handle on opening or appending the text file, writing to it,and closing it

I plan to open it up and write items to it as the script progresses,, but is there a way to save it without closing it?
So if it crashes on one line I won't loose the logfile?
Or do I need to open and close it each subsquent addition to save it properly if it dies mid macro?

Thanks,

Dim s As String
Dim textfile As Integer

Set objWS = CreateObject("WScript.Shell")
strdesktoppath = objWS.SpecialFolders("Desktop")
logfile = strdesktoppath & "\log.txt"
textfile = FreeFile

Open logfile For Output As textfile
s = "Hello world"
Print #textfile, s
Close textfile

Then use
Open logfileFor Append As TextFile
bla bla print more info
close textfile

 

[Sunjinsak]

Hi collin8579,

The below might help you out here.

It's only a basic example of a logger and you may need to adapt it to your specific needs but should get you started at least.

VBA Error Handling - A Complete Guide - Excel Macro Mastery

“Abort, Retry, Fail?” – MS-DOS error message circa 1986 This post provides a complete guide to VBA Error Handing. If you are looking for a quick summary then check out the quick guide table in the first section. If you are looking for a particular topic on VBA Error Handing then check out the...

excelmacromastery.com

Edit: Just thought - if you do use the example logger from that link you might want to change the following:

    ' Archive file at certain size
    If FileLen(sFilename) > 20000 Then
        FileCopy sFilename _
            , Replace(sFilename, ".txt", Format(Now, "ddmmyyyy hhmmss.txt"))
        Kill sFilename
    End If

To:

    ' Archive file at certain size
    If Not Dir(sFilename) = "" Then
        If FileLen(sFilename) > 20000 Then
            FileCopy sFilename _
                , Replace(sFilename, ".txt", Format(Now, "ddmmyyyy hhmmss.txt"))
            Kill sFilename
        End If
    End If

To prevent an error being thrown if the file doesn't already exist.

 

[RoryA]

do I need to open and close it each subsquent addition to save it properly if it dies mid macro?

I'd recommend that, using a separate routine. The overhead is pretty minimal.