vba 如何优化打开和关闭excel工作簿以提取数据以更快地运行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25287549/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to optimize opening and closing excel workbooks to extract data to run faster
提问by 110SidedHexagon
I currently know of two methods to open and close excel workbooks to extract data from them to summarize in a single workbook.
我目前知道两种打开和关闭 Excel 工作簿的方法，以从中提取数据以汇总在一个工作簿中。
The first method is as follows:
第一种方法如下：
Dim wbDataSheet As Workbook
For FNum = LBound(MyFiles) To UBound(MyFiles)
Set wbDataSheet = Workbooks.Open(MyPath & MyFiles(FNum), 0, True)
'Capture data
wbDataSheet.Close (False)
Next FNum
The second is this:
第二个是这样的：
Dim XL As New Excel.Application
For FNum = LBound(MyFiles) To UBound(MyFiles)
With XL
.Workbooks.Open FileName:=MyPath & MyFiles(FNum), ReadOnly:=True
.Visible = False
End With
'Capture Data
XL.ActiveWorkbook.Close False
Next FNum
My dilemma is this:
我的困境是这样的：
Method 1 is faster (about .35 seconds/file for 1052 files) but it explodes my task bar as it opens new workbooks (making it near impossible to select a different excel workbook) and for some reason my code can be easily broken by pressing shift more than once or holding it at all, forcing me to start the code over.
方法 1 更快（对于 1052 个文件，大约 0.35 秒/文件）但它在打开新工作簿时会爆炸我的任务栏（使得几乎不可能选择不同的 excel 工作簿）并且由于某种原因，我的代码可以通过按轻松破解多次移动或完全按住它，迫使我重新开始编写代码。
Method 2 is noticeably slower (about .56 seconds/file for 1052 files) but since the excel application is hidden my taskbar remains usable and pressing shift doesn't break the code.
方法 2 明显较慢（对于 1052 个文件，每个文件大约需要 0.56 秒），但由于隐藏了 excel 应用程序，我的任务栏仍然可用，按 shift 不会破坏代码。
What I want to know is, is there a way to run method 1 without ruining my taskbar or having shift stop the code? Alternatively, is there a way to speed up method two to speeds near method 1?
我想知道的是，有没有办法在不破坏任务栏或让 shift 停止代码的情况下运行方法 1？或者，有没有办法将方法 2 加速到接近方法 1 的速度？
*Note: I am already using the code below to optimize my speed and limiting vba/workbook interaction. Besides the methods to access the workbooks, the rest of the code in each scenario is identical.
*注意：我已经在使用下面的代码来优化我的速度并限制 vba/工作簿交互。除了访问工作簿的方法之外，每个场景中的其余代码都是相同的。
With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
.EnableEvents = False
.DisplayAlerts = False
End With
采纳答案by mongoose36
Example code for calling the Get value function which uses ExecuteExcel14Macro
调用使用 ExecuteExcel14Macro 的 Get value 函数的示例代码
Sub test()
Dim p As String, f As String, s as String, a as String
p = "C:\Users\User\Documents\"
f = "Example.xlsx"
s = "Sheet1" 'This is the name of the Sheet in your file
a = "D56" 'Range of the value you want I'm not sure if you can pull more than cell's value or not
ThisWorkbook.Worksheets("Sheet2").Range("F21") = GetValue(p, f, s, a) 'Transfer the value
End Sub
Below is the Function that you will need
下面是你需要的函数
Function GetValue(Path, file, sheet, ref)
' Retrieves a value from a closed workbook
Dim arg As String
' Make sure the file exists
If Dir(Path & file) = "" Then
GetValue = "File Not Found"
Exit Function
End If
' Create the argument
arg = "'" & Path & "[" & file & "]" & sheet & "'!" & _
Range(ref).Range("A1").Address(, , xlR1C1)
' Execute an XLM macro
GetValue = ExecuteExcel4Macro(arg)
End Function
