等待大文件在Excel中打开 [英] Wait for big files to open in Excel

问题描述

我一直在试图在VBA中循环一堆大的.csv文件。每个大约是50MB。在每次迭代我打开一个新的CSV来操作数据，但当.CSV打开时，有一个下载消息，说明文件正在打开，进度条总是卡在某一点，而VBA等待它完成。 / p>

实际上.csv是打开的，因为如果我在进度条上单击取消，代码继续运行良好，但我必须在每次迭代时执行手动操作。 p>

我的猜测是VBA进入下一步，而文件没有打开或类似的东西，所以也许如果我做一个睡眠或类似的东西，它可以工作，但是我试图没有工作，现在。 （我已经尝试Application.EnableEvents = False）。这是我的代码：

  Sub GetOptions（）
 
 Application.DisplayAlerts = False 
 Application.EnableEvents = False 
 
 Set Dates = Sheets（Dates）
设置Res = Sheets（Options）
 
 Dim dateToday As date 
 
 ETF =SPY
 nrows = Dates.Cells（Rows.Count，1）.End（xlUp）.Row 
 
 
 For i = 708 To nrows 
 
如果Dates.Cells（i，2）.Value =Bthen 
 dateToday = Dates.Cells（i，1）.Value 
 dateYear = Year ）
 stringOpening =P：\Options Database\CSV\& dateYear& \bb_& dateYear& _& GetMonth（dateToday）& \bb_options_&格式（dateToday，yyyymmdd）& .csv
 Workbooks.Open stringOpening，UpdateLinks：= 0，ReadOnly：= True 
 Set Options = Workbooks（bb_options_& Format（dateToday，yyyymmdd）） 
 
做事情... 
 
工作簿（bb_options_& Format（dateToday，yyyymmdd））Close SaveChanges：= False 
 End If b $ b Next i 
 
 
 End Sub 
  

方案

一个窍门是：

1. 打开它们作为读/写文件，


2. 等待写入状态，指示其已完全打开


3. 将文件设置为只读

此代码循环，直到文件进入写入状态：

  Sub myWaitForFileOpen $ b Dim wb As Workbook 
设置wb = Application.Workbooks.Open（C：\File.xls）
 
 Do Until wb.ReadOnly = False 
 wb。关闭
 Application.Wait Now + TimeValue（00:00:01）
设置wb = Application.Workbooks.Open（C：\File.xls）
 Loop 
'然后需要工作簿打开这里的代码！ 
'或调用其他宏在这里！ 
 End Sub 
  

这是您的完整代码，将以读/写方式打开CSV直到完全加载，然后将其还原为只读：

  Sub GetOptions（）
 Dim wB As Workbook 
 
 Application.DisplayAlerts = False 
 Application.EnableEvents = False 
 
设置Dates =表格（Dates）
设置Res = Sheets ）
 
 Dim dateToday As Date 
 
 ETF =SPY
 nrows = Dates.Cells（Rows.Count，1）.End（xlUp）.Row 
 
 
 For i = 708如果Dates.Cells（i，2）.Value =B，则
 dateToday = Dates.Cells（i， 1）.Value 
 dateYear = Year（dateToday）
 stringOpening =P：\Options Database\CSV\& dateYear& \bb_& dateYear& _& GetMonth（dateToday）& \bb_options_&格式（dateToday，yyyymmdd）& .csv
 
设置wB = Workbooks.Open（stringOpening，UpdateLinks：= 0，ReadOnly：= False）
 
 Do Until wB.ReadOnly = False 
 wB.Close 
 
 Application.Wait Now + TimeValue（00:00:01）
 
设置wB = Application.Workbooks.Open（C：\ My Files \AAA.xls）
 Loop 
 wB.ReadOnly = True 
设置选项= wB.Sheets（1）
 
 
 Do 
'things ... 
 Loop 
 
 wB.Close SaveChanges：= False 
结束如果
下一个i 
 
结束子
  

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

解决方案

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

这篇关于等待大文件在Excel中打开的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！