如何使用VBA中工作簿的名称激活打开的工作簿 [英] How to activate a workbook that is open using the name of the workbook in VBA

问题描述

我已经打开了一个工作簿，但我正在从另一个工作簿运行一个宏。我想使用其名称激活第一个工作簿。

I have already a one workbook open but I am running a macro from another workbook. I would like to activate the first workbook using its name.

代码：

FileName = input_path_1 & input_file_1
Workbooks(FileName.xls).Activate

当我尝试这样做正在给我下标超出范围的错误。如何解决？

When I try to do so, it is giving me "Subscript out of range" error. How do I solve it?

推荐答案

检查您的变量文件名是否包含正确的文件名（例如 Sample.xls ）

还要检查 input_path_1 和 input_file_1 具有正确的值。

如果他们有它应该是这样的：

Check if your variable Filename contains the correct filename. (e.g. Sample.xls)
Also check if input_path_1 and input_file_1 have correct values.
If they have it should be like this:

Workbooks(Filename).Activate

现在，如果你需要附加扩展名（例如文件名值只是样本）：

Now, if you need to append the extension name (e.g. Filename value is just Sample):

Workbooks(Filename & ".xls").Activate

参数应该始终是字符串的形式，应该是完整的文件名（带扩展名）。虽然数字（索引）也被接受，你不能确定什么索引是指什么工作簿。更好的是将其分配给一个变量。

The argument should always be in the form of string and should be the complete filename (with extension). Although numerals (index) is also accepted, you can't be sure what index refer to what workbook. Better yet, assign it to a variable.

Dim otherWB As Workbook
Set otherWB = Workbooks(Filename)
'Set otherWB = Workbooks(Filename & ".xls") '~~> for second scenario above

Edit1：如果文件名包含完整路径，那么这可能正常。

From comment, if Filename contains the fullpath, then this might work.

Dim Filename1 As String
Filename1 = Split(Filename, "\")(UBound(Split(Filename, "\")))
Workbooks(Filename1).Activate

这篇关于如何使用VBA中工作簿的名称激活打开的工作簿的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！