vba runtime error 9 subscript out of range when setting a workbook

[Guinaba]

Hi guys,

Just wondering what I am missing here. I am trying to set the workbook below which is a opended workbook but keep getting the error 9.

  FileSource = "S:\Everyone\"
  Dim WB as Workbook
  FileName = "Test" & ".XLSM"
  Set WB = Application.Workbooks(FileName)

I am skipping the file path because a workbook object variable refers to an open workbook, not the workbook on disk.

 

[RoryA]

Either the workbook name is incorrect, or the workbook is open in a different instance of Excel.

 

[Guinaba]

Either the workbook name is incorrect, or the workbook is open in a different instance of Excel.

Hi @RoryA.

If the workbook is open in a different instance of Excel, how should I sort this out?

 

[RoryA]

Use GetObject with the full file path to get a reference to it