设置工作簿变量时出错-2147352565 [英] Error when setting workbook variable - 2147352565

问题描述

我有一个简单的代码，该代码在初始化一个用户窗体时运行，该用户窗体设置了一些工作簿和工作表变量，因此可以在我的其余模块中轻松使用它们，并且如果文件移动，引用可以在一个位置轻松更改.我最近将工作簿从台式机迁移到单独的服务器/驱动器，并相应地更新了文件路径.但是，当我现在尝试运行代码时，我立即收到消息:

I have a simple code that runs upon initialization of a userform that sets a few workbook and worksheet variables so they can be used easily throughout the rest of my modules, and the references can be easily changed in one place if the file moves. I recently migrated my workbooks from my desktop to a separate server/drive, and accordingly updated the file pathways; however, when I try to run the code now I immediately get the message:

运行时错误'-2147352565(8002000b)":无法将焦点移到控件上，因为它是不可见的，未启用或类型不接受焦点."

"Run-time error '-2147352565 (8002000b)': Can't move focus to the control because it is invisible, not enabled, or of a type that does not accept the focus."

此错误发生在行上

Set ReportWkbk = Workbooks("N:\ rest of file pathway here\QuaRT_Template.xlsm")

服务器/驱动器上是否可能发生任何导致此问题的事情?如果是这样，我有什么办法可以解决?我确实使用了保存在相同位置的其他excel工作簿，这些工作簿似乎没有被引用，尽管它们的引用位于工作簿本身中，而不是通过Visual Basic.

Is there anything that can be happening on the server/drive that is causing this issue? If so, is there anything I can do to fix it? I do work with other excel workbooks saved in the same location that seem to have no issue being referenced, though their references are in the workbook itself, not through Visual Basic.

推荐答案

发生下标超出范围错误是因为被引用的Excel工作簿未在同一中打开(或打开) Excel实例.通过创建两个工作簿(名称为WorkBook2.xlsx)并确保它们在单独的Excel实例中打开，可以轻松地重现该错误.然后运行以下代码:

The Subscript Out of Range error occurs because the Excel workbook being referenced is not open (or opening) in the same instance of Excel. You can easily reproduce the error by creating two workbooks (name one WorkBook2.xlsx) and ensuring they open in separate instances of Excel. Then run this code:

Sub OpenWkbkNames()
    Dim wbk As Workbook
    For Each wbk In Workbooks
        Debug.Print wbk.Name
    Next
    'Hmm. Workbook 2 is not listed, but lets activate it and see what happens.
    Workbooks("workbook2.xlsx").Activate
End Sub

您可以通过遍历在Excel的 THIS 实例中打开的工作簿的名称来确保其可用，来避免此问题.

You could avoid the issue by looping through the names of the workbooks open in THIS instance of Excel to ensure its available.

我一直在研究运行时错误'-2147352565(8002000b)'，并发现了以下内容:

I've been researching Run-time error '-2147352565 (8002000b)' and found this: https://msdn.microsoft.com/en-us/library/microsoft.visualstudio.vsconstants.disp_e_badindex.aspx

显然，这也是VS表示下标超出范围的方式.我怀疑(但没有确凿的证据)由于代码在本地工作，但是在文件迁移到共享驱动器后出现了问题，Windows需要很长时间才能打开文件，因此需要在新的Excel实例中打开文件.本质上，Excel不耐烦，并决定在Windows完成任务时继续前进.我基于添加到Excel 2013(

Apparently, that is VS's way of saying Subscript Out of Range too. I suspect (but have no solid proof) that since the code worked locally but the issue appeared after the file was migrated to a shared drive that Windows is taking a long time to open the file so opens it in a new instance of Excel. Essentially, Excel gets impatient and decides to move on while Windows completes its task. I base this assumption on the detection logic that was added to Excel 2013 (https://blogs.office.com/2013/06/03/opening-workbooks-by-running-separate-instances-of-excel/).

这篇关于设置工作簿变量时出错-2147352565的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！