vba CreateObject("Excel.Application") .Workbooks.Open 和 Workbooks.Open 之间的区别
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13044473/
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
Difference between CreateObject("Excel.Application") .Workbooks.Open and just Workbooks.Open
提问by Siraj Samsudeen
I am currently using Workbooks.Opento process a large number of files contained in a directory. But opening and closing these files make the files appear in the task bar and I wanted to avoid the constant flickering.
我目前正在使用Workbooks.Open来处理目录中包含的大量文件。但是打开和关闭这些文件会使文件出现在任务栏中，我想避免不断闪烁。
I got a suggestion from that I can use CreateObjectto create a new instance since that opens a new Excel instance which is hidden.
我得到了一个建议，我可以用它CreateObject来创建一个新实例，因为这会打开一个隐藏的新 Excel 实例。
Is there any other difference between the two ways of opening new workbooks in terms of performance?
Also, should I just use one instance of Excel created using
CreateObjectto open all Workbooks or do I need to create one instance for each workbook I have to process (which seems like a waste of lot of memory and less speed)?
这两种打开新工作簿的方式在性能方面还有其他区别吗？
另外，我应该只使用创建的一个 Excel 实例
CreateObject来打开所有工作簿，还是需要为我必须处理的每个工作簿创建一个实例（这似乎浪费了大量内存和速度）？
回答by Siddharth Rout
Workbooks.Openuses the current MS Excel instance and CreateObject(“Excel.Application”)creates a new MS Excel instance. You can read up on CreateObjecthere.
Workbooks.Open使用当前的 MS Excel 实例并CreateObject(“Excel.Application”)创建一个新的 MS Excel 实例。你可以在CreateObject这里阅读。
Simply issuing a Workbooks.Openafter creating a new instance will not ensure that the workbooks open in the new instance. You will have to bind with it. For example
Workbooks.Open在创建新实例后简单地发出 a并不能确保工作簿在新实例中打开。你将不得不与它绑定。例如
Dim oXLApp As Object, wb As Object
Set oXLApp = CreateObject("Excel.Application")
'~~> Hide Excel
oXLApp.Visible = False
'~~> Open files
Set wb = oXLApp.Workbooks.Open("C:\Sample.xls")
Regarding your other question
关于你的另一个问题
Also, should I just use one instance of Excel created using CreateObject to open all Workbooks or do I need to create one instance for each workbook I have to process
另外，我应该只使用一个使用 CreateObject 创建的 Excel 实例来打开所有工作簿，还是需要为我必须处理的每个工作簿创建一个实例
You don't need several instances. You can work with one instance. For example
您不需要多个实例。您可以使用一个实例。例如
Dim oXLApp As Object, wb As Object
Set oXLApp = CreateObject("Excel.Application")
'~~> Hide Excel
oXLApp.Visible = False
'~~> Open files
Set wb = oXLApp.Workbooks.Open("C:\Sample1.xls")
'
'~~> Do some Stuff
'
wb.Close (False)
'~~> Open files
Set wb = oXLApp.Workbooks.Open("C:\Sample2.xls")
'
'~~> Do some Stuff
'
wb.Close (False)
'
'~~> And So on
'
回答by Fionnuala
Late binding is slightly slower than early binding, but you may not even notice the difference. Yes, you can use just use one instance for all the workbooks. Note that this:
后期绑定比早期绑定稍慢，但您甚至可能不会注意到差异。是的，您可以为所有工作簿使用一个实例。请注意：
Dim xl As New Excel.Application
xl.Workbooks.Open "z:\docs\test.xlsm"
Will not be visible unless you say:
除非您说，否则将不可见：
xl.Visible = True
Be sure to close any instances in your error trap.
确保关闭错误陷阱中的所有实例。
