Finding a sharepoint excel file which is already open in Windows with VBA

[Mcshaz]

I am trying to identify if a particular workbook is open, and if so, set that workbook object as the return value of a function.

If the file is closed I can open it, but I seem unable to get a reference to the workbook if it is open on my desktop, and of course if I try and open the same workbook, which is already open, I generate an error.

When using file paths on the Windows file system and earlier versions of Office which, from memory, only allowed a single application instance (I am currently using the 16.0 object library) , I used to be able to do something like:

    Set myXcel = New Excel.Application
    Set eWBs = myXcel.Workbooks
    For Each WB In eWBs
      If WB.fullName = fullpath Then
          wasWorkbookOpen = True
          Exit For
      End If
    Next WB

However now myXcel.Workbooks collection contains no items, despite having the relevant MS Excel application and file open on my desktop. The same occurs when using Set myXcel= GetObject(, "Excel.Application") - that is, I get a non-empty object, but the workbooks collection contains no items despite there being the relevant workbook open on my desktop.

I can find a pointer to the window using FindWindowA and GetWindow, but the Instance.GetObject method does not work with a sharepoint file path like "https://sharepointcompanyname.sharepoint.com/pathtofile/filename.xlsx"

Can someone please help me identify how to get a reference to an open workbook which has a sharepoint (as opposed to Windows filesystem) path.

Thank you very much