vba 引用另一个在单独窗口中打开的工作簿的方法是什么？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19191280/
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
What is the method to reference another opened workbook which is in a separate window?
提问by Adnan Al-Husain
I could open another workbook in separate window by using two methods
我可以使用两种方法在单独的窗口中打开另一个工作簿
TheEmu_Path = "excel.exe " & ThisWorkbook.Path & "\" & "myexcel.xlsx"
call Shell(TheEmu_Path, 3)
or
或者
Set oExcel = New Excel.Application
oExcel.Workbooks.Open Filename:=TheEmu_Path & "myexcel.xlsx"
The first method I could Open but don't know how to set a reference for the open workbook The second I could reference when opening the workbook but later for any later process I don't know how to reference that separately-opened worksheet
第一种方法我可以打开但不知道如何为打开的工作簿设置引用第二种我可以在打开工作簿时引用但后来的任何后续过程我不知道如何引用单独打开的工作表
Set oExcel = ??
Set oWB = oExcel.Workbooks("myexcel.xlsx")
Set oWS = oWB.Sheets("F1")
How can I set the reference for oExcel (the already separately opened workbook)?
如何设置 oExcel（已单独打开的工作簿）的引用？
After creating and opening, later I want to change value in that open separately workbook in new button command
创建和打开后，稍后我想在新按钮命令中更改该单独打开的工作簿中的值
Set oExcel = CreateObject("Excel.Application")
Set oWB = oExcel.Workbooks("myexcel.xlsx")
Set oWS = oWB.Sheets("1")
oWS.Cells(1, 1) = 55
I have a mistake in line two as I believe that I still haven't referenced oExcel correctly.
我在第二行有一个错误，因为我相信我仍然没有正确引用 oExcel。
Comment on David revision
评论大卫修订
Impressive, thanks a lot.
印象深刻，非常感谢。
It works perfectly with very little addition, oExcel will be considered as workbook directly - great!
它只需很少的添加即可完美运行，oExcel 将被直接视为工作簿 - 太棒了！
Dim oExcel As Object 'or Dim oExcel As Workbook
Dim oWS As Excel.Worksheet 'or Dim oWS As Worksheet
Set oExcel = GetObject(ThisWorkbook.Path & "\" & "myexcel.xlsx").Application
'or Set oExcel = GetObject(ThisWorkbook.Path & "\" & "myexcel.xlsx")
Set oWS = oExcel.Sheets("1")
oWS.Cells(1, 1) = 4
This is exciting and encouraging to ask other question as I have my file crashes when using UpdateRemoteReferences that result in #na values.
这是令人兴奋和鼓舞人心的提出其他问题，因为我在使用 UpdateRemoteReferences 导致 #na 值时我的文件崩溃。
采纳答案by David Zemens
Create a new instance of Excel:
创建一个新的 Excel 实例：
Dim oExcel as Excel.Application 'or As New Excel.Application
Set oExcel = New Excel.Application
'Dim oExcel as Object
'Set oExcel = CreateObject("Excel.Application") 'Alternative method for late-binding
When I do this, a new Excel opens, and there is one blank workbook file. To reference this workbook:
当我这样做时，会打开一个新的 Excel，并且有一个空白的工作簿文件。要引用此工作簿：
Dim oWB as Workbook
Set oWB = oExcel.Workbooks(1)
To open a file within this instance:
要在此实例中打开文件：
Dim anotherWB as Workbook
Set anotherWB = oExcel.Workbooks.Open("c:\test.xlsx")
Etc.
等等。
Although I don't usually recommend working with multiple instances of the application, I just don't have any use to do this, I just open all workbooks in one instance of Excel.Application.
虽然我通常不建议使用应用程序的多个实例，但我没有任何用处，我只是在 Excel.Application 的一个实例中打开所有工作簿。
Updated per comments requests
根据评论请求更新
The easiest way to control multiple instances, IMO, is to start with a clean slate and create new objects, controlling them during runtime, per examples above.
根据上面的示例，控制多个实例的最简单方法 IMO 是从一个干净的石板开始并创建新对象，并在运行时控制它们。
In cases where this is not possible, it is still possible to get the other instance. In both cases I think you will need to know the name of an open workbook file in the other instance of Excel.
在不可能的情况下，仍然可以获取另一个实例。在这两种情况下，我认为您都需要知道另一个 Excel 实例中打开的工作簿文件的名称。
Easier
更轻松
If you know the workbook name, you can get access like:
如果您知道工作簿名称，您可以获得如下访问权限：
Dim oExcel As Object ' or Excel.Application
Set oExcel = GetObject("Workbook_Name.xlsx").Application
You can then refer to workbooks using the ordinary methods, as long as you qualify that you're working with oExcel, e.g.:
然后，您可以使用普通方法引用工作簿，只要您有资格使用oExcel，例如：
Dim otherWorkbook as Workbook
Set otherWorkbook = oExcel.Workbooks(1) 'or oExcel.Workbooks("Workbook_Name.xlsx")
More difficult, possibly more versatile
更难，可能更通用
Another option is to use the WinAPI functions to get the windows handle. I have used the WinAPI to do things like this before, and although I have not tested the example code below, it should be a good place to start.
另一种选择是使用 WinAPI 函数来获取 Windows 句柄。我以前用 WinAPI 做过这样的事情，虽然我没有测试下面的示例代码，但它应该是一个很好的起点。
http://excelribbon.tips.net/T009452_Finding_Other_Instances_of_Excel_in_a_Macro.html
http://excelribbon.tips.net/T009452_Finding_Other_Instances_of_Excel_in_a_Macro.html
