VBA运行时错误'9'：下标超出范围;尝试激活另一个工作簿 [英] VBA Run-time error '9': Subscript out of range; trying to activate another workbook

问题描述

我正在尝试在Excel 2010中创建一个VBA，从另一个电子表格获取信息，我不允许将其修改，并将其转换到内置宏的电子表格。这是我的代码：

  Sub BringUpWorkbook（）
工作簿（RITE 1624.xls）。激活
 End Sub 
  

我有几本VBA书籍，已经访问了互联网上的数十个网站，包括那些在stackoverflow.com上的网站，找不到为什么我收到运行时错误的原因。该工作簿已经开放，我已经尝试添加标题后面的所有内容，我已经尝试删除了 .xls ，我甚至在变量。任何想法？

解决方案

确保扩展名是正确的。如果它是像您指出的excel-2010文件，则可能需要更新代码以反映RITE 1624文件的扩展名（例如2010年Excel工作簿的.xlsx，2010年可启用Excel的工作簿的.xlsm，或任何扩展名。

  Sub BringUpWorkbook（）
工作簿（RITE 1624.xlsx）激活
 End Sub 
  

编辑：

为了确保您有正确的工作簿名称，您可以在立即窗口中打印每个工作簿的名称。

打开VBA编辑器，然后按Ctrl + G打开立即窗口（或执行视图>立即窗口），然后运行以下宏：

  Sub OpenWkbkNames（）
 
工作簿中的每个工作簿
 Debug.Print Workbook.Name 
下一个
 End Sub 
  

请注意，这将给您所有打开的工作簿的名称 在同一个Excel实例为您的宏 。如果您的RITE 1624文件位于单独的Excel实例中，那么宏所在的实例将无法看到该文件（并且不会显示在上面的OpenWkbkNames代码的输出中）。解决此问题的最简单方法是从包含宏的Excel实例中打开所有必需的文件。

  Sub BringUpWorkbook ）
 
 Workbooks.Open（RITE 1624.xls）。激活
 End Sub 
  

I'm trying to create a VBA in Excel 2010 that takes info from another spreadsheet that I'm not allowed to alter and bring it over to the spreadsheet with my macro built in. Here's my code:

Sub BringUpWorkbook()
    Workbooks("RITE 1624.xls").Activate
End Sub

I have several VBA books, have visited dozens of sites on the Internet, including those here at stackoverflow.com, and cannot find a reason why I'm receiving the run-time error. The workbook is already open, I've tried adding everything trailing the title, I've tried removing the .xls, I've even did all of the above with in a variable. Any ideas?

解决方案

Make sure the extension is correct. If it's a excel-2010 file like you indicate, you may need to update your code to reflect the extension of your "RITE 1624" file (e.g. .xlsx for a 2010 Excel workbook, .xlsm for a 2010 Excel macro-enabled workbook, or whatever the extension is.

Sub BringUpWorkbook()
    Workbooks("RITE 1624.xlsx").Activate
End Sub

EDIT:

To make sure you have the right name of the workbook, you can print the name of each workbook in an immediate window.

Open up the VBA editor, and then press Ctrl+G to open the Immediate Window (or do View > Immediate window). Then run the following Macro:

Sub OpenWkbkNames()

For Each Workbook In Workbooks
    Debug.Print Workbook.Name
Next
End Sub

Note that this will give you the names of all the open workbooks in the same Excel instance as your macro. If your RITE 1624 file is in a separate Excel instance, then the instance that your macro is in will not be able to see that file (and it won't appear in the output of the OpenWkbkNames code above). The simplest way to resolve this is to open all of your necessary files from the Excel instance that contains your macro.

Sub BringUpWorkbook()

    Workbooks.Open("RITE 1624.xls").Activate
End Sub

这篇关于VBA运行时错误'9'：下标超出范围;尝试激活另一个工作簿的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！