一个工作簿中的宏可以更改另一工作簿吗？

发布于 2024-11-26 02:02:46 字数 327 浏览 1 评论 0原文

我在一个工作表（internal.xls）中定义了一个宏，

Public Sub sheet2test()
   Workbooks.Open Filename:="external.xls"
   Windows("external.xls").Activate
   Sheets("Sheet3").Activate
   Range("A5").Value = 5

End Sub

运行此代码，打开 external.xls，并激活其工作表 3。但是，值 5 放置在 internal.xls 而不是 external.xls 中。如何确保对其他工作表进行更改？

原文

I defined a macro in one worksheet(internal.xls) as

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?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

你曾走过我的故事 2024-12-03 02:02:46

我宁愿在这里使用对象变量：

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"

正如其他人所说，各种 Activate 指令在这里更不方便，而不是有用，更不用说它们很慢、有风险并且使调试变得困难。

I would rather use object variables here:

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 Activate instructions are more inconvenient than useful here, not to mention that they are slow, risky and make debugging difficult.

回复收藏 0 原文

霊感 2024-12-03 02:02:46

您不需要执行所有这些激活操作。它所做的只是导致屏幕闪烁和处理延迟。

Public Sub sheet2test()
   Workbooks.Open Filename:="external.xls"
   Workbooks("external.xls").Sheets("Sheet3").Range("A5").Value = 5
End Sub

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

回复收藏 0 原文

随心而道 2024-12-03 02:02:46

答案是肯定的。

尝试使用以下代码对 external.xls 进行更改：

Public Sub sheet2test()
 Workbooks.Open Filename:="external.xls"
 Workbooks("external.xls").Activate
 ActiveWorkbook.Sheets("Sheet3").Activate
 Range("A5").Value = 5
End Sub

我将 ActiveWorkbook. 添加到第 4 行。

必须添加此代码才能在当前活动工作簿 (external.xls) 中进行更改，而不是在包含以下内容的工作簿中进行更改：并执行宏
（内部.xls）

The answer is Yes.

Try the following code to make changes to 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.

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)

回复收藏 0 原文

~没有更多了~