Excel VBA macro for opening files crashes without any error message

Question

for few years I've a macro somewhere from the internet which opens all files in selected folder and does something with them (changes something in all of them, merges them in one, changes column order etc.).

For some reason, with some files the macro crashes without any error message. One day it crashes, the other day it does not. It crashes on my computer, as well as on there's computers or it doesn't crash on my computer and it crashes on there's computers etc...

It crashes if there are 50 files in the folder or just two small files.

There is only way I can make the macro run till the end is to create a breakpoint on Set wb = Workbooks.Open(Pathname & Filename) and sometimes even on Do Work wb. And then I press just F5 when the macros stops on this breakpoint(s) and it does everything it should until it reaches the breakpoint again.

Public LastLine As Long
Public final_file As String
Public my_directory As String

Sub ProcessFiles()
Dim Filename, Pathname As String
Dim wb As Workbook


Application.AskToUpdateLinks = False
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.Calculation = xlCalculationManual
 Application.EnableEvents = False

final_file = ActiveWorkbook.Name


my_directory = InputBox("What's the name of the folder with the files?:", "What's the name of the folder with the files", "New files")
Pathname = ActiveWorkbook.Path & "\" & my_directory & "\"
Filename = Dir(Pathname & "*.xlsx")

Do While Filename <> ""
    Set wb = Workbooks.Open(Pathname & Filename)
    DoWork wb
    ActiveWindow.Close
    Filename = Dir()
Loop

    LastLine = 0
Application.ScreenUpdating = True
Application.AskToUpdateLinks = True
Application.DisplayAlerts = True
Application.TransitionNavigKeys = False
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
End Sub



Sub DoWork(wb As Workbook)
   With wb
       'Here it does the tricks with each opened file

End Sub

Do you have any idea what can be wrong with this? Thank you

You should comment out, by adding an 'in front of the lines, application.screenupdating, application.displayalerts. This will make excel show any messages there are, and could help you identify the problems at hand. — Luuklag
– Luuklag, Commented May 22, 2018 at 8:25
Excel files can become corrupt over time. You should use MZTools Clean Project feature. It will rebuild the project, removing any code residue that has built up over the years. — user9298223
– user9298223, Commented May 22, 2018 at 8:28

Julian · Accepted Answer · 2022-09-17 18:20:49Z

2

I had a similar situation, that for no reason Excel would just crash without any error message when opening multiple workbooks in sequence.

Try adding Application.Wait to your code after Workbook.Open and again after Workbook.Close - for at least 3 seconds. Haven't had the problem since.

newHour = Hour(Now()) 
newMinute = Minute(Now())
newSecond = Second(Now()) + 3
waitTime = TimeSerial(newHour, newMinute, newSecond) 
Application.Wait waitTime

I got this from https://learn.microsoft.com/en-us/office/vba/api/excel.application.wait

edited Sep 17, 2022 at 18:20

answered Apr 14, 2020 at 16:05

Julian

214 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Excel VBA macro for opening files crashes without any error message

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related