Wait for big files to open in Excel

Question

I've been trying to loop over a bunch of big .csv files in VBA. Each of them is approximately 50MB. At every iteration I open a new CSV to manipulate data but when the .csv is opening there is a downloading message saying that the file is opening and the progress bar always gets stuck at some point while the VBA is waiting for it to finish.

Actually the .csv is opened because if I click "cancel" on the progress bar the code continues running well but I have to do a manual action at every iteration.

My guess is that VBA goes to the next step while the file is not opened or something like that so maybe if I do a Sleep or something like that it could work but what I tried did not work for now. (I already tried Application.EnableEvents = False). Here is my code:

Sub GetOptions()

Application.DisplayAlerts = False
Application.EnableEvents = False

Set Dates = Sheets("Dates")
Set Res = Sheets("Options")

Dim dateToday As Date

ETF = "SPY"
nrows = Dates.Cells(Rows.Count, 1).End(xlUp).Row


For i = 708 To nrows

    If Dates.Cells(i, 2).Value = "B" Then
        dateToday = Dates.Cells(i, 1).Value
        dateYear = Year(dateToday)
        stringOpening = "P:\Options Database\CSV\" & dateYear & "\bb_" & dateYear & "_" & GetMonth(dateToday) & "\bb_options_" & Format(dateToday, "yyyymmdd") & ".csv"
        Workbooks.Open stringOpening, UpdateLinks:=0, ReadOnly:=True
        Set Options = Workbooks("bb_options_" & Format(dateToday, "yyyymmdd")).Sheets(1)

        Do things...

        Workbooks("bb_options_" & Format(dateToday, "yyyymmdd")).Close SaveChanges:=False
    End If
Next i


End Sub

Answer 1

A trick would be :

1. to open them as Read/Write files,
2. wait for the Write status which indicates that it is fully opened
3. set back the file to Read Only

This code loops until the file goes into a Write status :

Sub myWaitForFileOpen()
Dim wb As Workbook
Set wb = Application.Workbooks.Open("C:\File.xls")

Do Until wb.ReadOnly = False
    wb.Close
    Application.Wait Now + TimeValue("00:00:01")
    Set wb = Application.Workbooks.Open("C:\File.xls")
Loop
'Then the code that needs that Workbook open here!
'Or Call That other macro here!
End Sub

Here is your full code, that will open the CSV in Read/Write until it is fully loaded and then put it back to read only :

Sub GetOptions()
Dim wB As Workbook

Application.DisplayAlerts = False
Application.EnableEvents = False

Set Dates = Sheets("Dates")
Set Res = Sheets("Options")

Dim dateToday As Date

ETF = "SPY"
nrows = Dates.Cells(Rows.Count, 1).End(xlUp).Row


For i = 708 To nrows
    If Dates.Cells(i, 2).Value = "B" Then
        dateToday = Dates.Cells(i, 1).Value
        dateYear = Year(dateToday)
        stringOpening = "P:\Options Database\CSV\" & dateYear & "\bb_" & dateYear & "_" & GetMonth(dateToday) & "\bb_options_" & Format(dateToday, "yyyymmdd") & ".csv"

        Set wB = Workbooks.Open(stringOpening, UpdateLinks:=0, ReadOnly:=False)

        Do Until wB.ReadOnly = False
            wB.Close

            Application.Wait Now + TimeValue("00:00:01")

            Set wB = Application.Workbooks.Open("C:\My Files\AAA.xls")
        Loop
        wB.ReadOnly = True
        Set Options = wB.Sheets(1)


        Do
            'things...
        Loop

        wB.Close SaveChanges:=False
    End If
Next i

End Sub

Answer 2

If you want to open the file and use it immediately Excel might give an error because Excel activates file opening process and goes to execute next statement. A quick and dirty workaround for not very long files is to introduce an extra code that is not related to a file thus keeping Excel busy while file is going through the opening process.