vba 打开在后台的工作簿
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25499676/
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
Open a Workbook that is in the background
提问by Madosa
I'm trying to open a workbook that is in the background.
我正在尝试打开一个在后台的工作簿。
Dim app As New Excel.Application
app.Visible = False
Dim book As Excel.Workbook
Set book = app.Workbooks.Add(UserForm1.TextBox1.Text)
I get the filename out of a textbox.
我从文本框中获取文件名。
I get a crash at:
我在以下位置崩溃：
Sheets("Start").select
I guess the workbook in the background is not the active one. How can I activate it?
我猜后台的工作簿不是活动的。我怎样才能激活它？
My idea was:
我的想法是：
Workbooks(UserForm1.TextBox1.Text).Activate
But I only have the filename, not the whole path and filename.
但我只有文件名，而不是整个路径和文件名。
回答by R. Karl
You could try
book.Sheets("Start").select
你可以试试
book.Sheets("Start").select
I think the better way is using the with statement
我认为更好的方法是使用 with 语句
With book.Sheets("Start")
.cells("A1").Select
End With
With book.Sheets("Start")
.cells("A1").Select
End With
回答by MP24
You could just use book.Activatefollowed by any code you want to perform on the new workbook. And please note that most actions are possible without using .Select. See How to avoid using Select in Excel VBA macrosfor more information.
您可以只使用book.Activate后跟要在新工作簿上执行的任何代码。请注意，大多数操作无需使用.Select. 有关详细信息，请参阅如何避免在 Excel VBA 宏中使用 Select。
回答by user1644564
Always avoid selecting or activating anything whether it's a cell, a range, a sheet or a workbook.
始终避免选择或激活任何内容，无论是单元格、区域、工作表还是工作簿。
When you write your code refer to
当您编写代码时，请参考
Workbooks("freds wrk").Worksheets("Start").Cells(1, 1)
Workbooks("freds wrk").Worksheets("Start").Range("A1")
You can also use the With command to make it shorter.
您还可以使用 With 命令使其更短。
With Workbooks("freds wrk").Worksheets("Start")
.Cells(1, 1)
.Range("A1")
end with
When your code is running a end user will just start clicking buttons and then everything will go wrong because what you think is active is no long active. And what you think is selected isn't any more.
当您的代码运行时，最终用户将开始单击按钮，然后一切都会出错，因为您认为处于活动状态的内容不再处于活动状态。你认为被选中的不再是。
The problems get worse when you start calling sub's in the middle of your code. How can you know what sheet is now going to be active and what is now selected by the sub you just called.
当您在代码中间开始调用 sub 时，问题会变得更糟。您怎么知道现在哪个工作表将处于活动状态以及您刚刚调用的子程序现在选择了什么。
And a with statement will make your code run faster. The Excel Objects Workbook, Worksheet, Cell and Range are amazingly slow for the computer to handle.
with 语句将使您的代码运行得更快。Excel 对象工作簿、工作表、单元格和范围对于计算机来说处理速度非常慢。
