Error when setting workbook variable - 2147352565

Question

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:

"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."

This error occurs on the line

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

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.

Answer 1

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

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

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

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/).