vba 一个工作簿中的宏可以更改另一个工作簿吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6812140/
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
Can a macro in one workbook make changes to another workbook?
提问by Ali
I defined a macro in one worksheet(internal.xls) as
我在一个工作表（internal.xls）中定义了一个宏
Public Sub sheet2test()
Workbooks.Open Filename:="external.xls"
Windows("external.xls").Activate
Sheets("Sheet3").Activate
Range("A5").Value = 5
End Sub
Running this code, opens external.xls, and activates its sheet 3. However the Value of 5 is placed in internal.xls and not external.xls. How do i ensure that the changes are made to the other worksheet?
运行此代码，打开 external.xls，并激活其表 3。然而，值 5 放置在 internal.xls 中，而不是 external.xls。我如何确保对另一个工作表进行了更改？
回答by Patrick Honorez
I would rather use:
我宁愿使用：
dim wb as workbook, sh as worksheet
set wb = workbooks.open("thatWorkbook.xls")
'Now you have a proper reference to the newly opened workbook !
set sh = wb.sheets("sheet3")
sh.range("a1") = "hello world"
As stated by others, the various Activateinstructions are more inconvenient than useful here.
正如其他人所说，各种Activate说明在这里不方便而不是有用。
回答by Jon Peltier
You don't need to do all this activating. All it does is cause flashing of the screen and delays in processing.
您不需要进行所有这些激活。它所做的只是导致屏幕闪烁和处理延迟。
Public Sub sheet2test()
Workbooks.Open Filename:="external.xls"
Workbooks("external.xls").Sheets("Sheet3").Range("A5").Value = 5
End Sub
回答by DKSan
The answer is Yes.
答案是肯定的。
Try the following code to make changes to external.xls:
尝试使用以下代码对 external.xls 进行更改：
Public Sub sheet2test()
Workbooks.Open Filename:="external.xls"
Workbooks("external.xls").Activate
ActiveWorkbook.Sheets("Sheet3").Activate
Range("A5").Value = 5
End Sub
I added ActiveWorkbook.to line 4.
我添加ActiveWorkbook.到第 4 行。
This has to be added to make changes in the currently active workbook (external.xls) and not the workbook containing and executing the macro (internal.xls)
必须添加此项以在当前活动的工作簿 (external.xls) 中进行更改，而不是在包含和执行宏的工作簿 (internal.xls) 中进行更改
