等待大文件在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
一个窍门是:
- 打开它们作为读/写文件,
- 等待写入状态,指示其已完全打开
- 将文件设置为只读
此代码循环,直到文件进入写入状态:
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 :
- to open them as Read/Write files,
- wait for the Write status which indicates that it is fully opened
- 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屋!