VBA hangs when opening excel file

[cwunderlich]

I have a macro which loops through a list of excel filepaths. The macro will read each path, open the workbook, copy/pull various data out of each workbook and then pastevalues the data into a central workbook. There are about 2000 filepaths it will need to loop through. So as you can see, it is a pretty simple macro. It runs just fine.

However, after running for a while (maybe after it has loop thru the first 70 or so filepaths), excel keeps hanging up on me (it shows the "Downloading" message box that you get when opening a file). If I click cancel on the msg box, the workbook still opens and the macro continues as normal. But with these "Downloading" message boxes constantly popping up, I would have to sit here and babysit the macro for 1000s of more filepaths. Does anyone know why excel gets stuck here? And is there a way around it?

What is even weirder is that this hanging message box does not happen on every Workbooks.Open instance, it just happens after every few.

here is an example of the code I am using:

With ThisWorkbook.Sheets("Filepaths")
           For i = firstrow To lastrow
                   SourceFile = .Cells(i, 1).Value
                   Workbooks.Open SourceFile, ReadOnly:=True 
                   Set MyFile = Workbooks(Workbooks.Count)
                   ..more code..........
                   ..more code..........
                   ..more code..........
                    MyFile.close
           Next i
End With

 

[cwunderlich]

anyone??