Macro Crashing Excel

collin8579

New Member
Joined
Oct 31, 2017
Messages
19
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

VBA Code:
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.
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,066
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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

New Member
Joined
Oct 31, 2017
Messages
19
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,066
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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

New Member
Joined
Oct 31, 2017
Messages
19

ADVERTISEMENT

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

Board Regular
Joined
Jul 13, 2011
Messages
143
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
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.


Edit: Just thought - if you do use the example logger from that link you might want to change the following:
VBA Code:
    ' 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:
VBA Code:
    ' 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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,066
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,456
Messages
5,596,234
Members
414,048
Latest member
wnied1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top