SharePoint Online: Open latest version of Excel-file (if exists)

Dear community,

I'm standing in front of a very mysterious behaviour of my Excel-macro, that worked well for five years with SharePoint 2013 and SharePoint 2016, where we used to open several xlsm-files from a folder structure via WebDAV, collected some relevant data and arranged this information in a new Excel spreadsheet.

Now, our IT migrated to SharePoint Online. WebDAV ist not available any more and I modified the VBA code to open the file using the url. On a first look this works, files are opened and data is written into my report.

Now the mystery (for me):

When I rename one of the Excel file or move it to another folder the macro used to tell me "file not found" (triggering further investigations by a person in charge). However, since SharePoint Online the (original) filename is somehow (!!!) opened, data is collected and transferred to my report, although the original file(name) isn't in the folder at all. This is bad, since we have to rely on getting data only of "one and only" (well named) file.

My attempts to solve the problem so far:

• I let IT switch off versioning in SPO (what for some reason was possible only by an SPO-admin script)
• I implemented a sub clearing the cache of InternetExplorer
• I deleted my RECENT folder
• I deleted the content of my C:\Users\[USER]\AppData\Local\Microsoft\Office\16.0\Wef

Nothing helped. When the file is deleted it is properly reported as "file not found". Only renaming or moving causes my mystery-report. Note, sync to OneDrive is not a preferred action since the VBA-code is in an Excel-AddIn used by several colleagues...

Has someone any idea for the reason of this behaviour and a way to force Excel / SharePoint Online to get only the actual folder content?

Many thanks in advance and kind regards,
Alfa

Oh, JEC. I think you gave me a decisive hint in your comments! Provided further testing maybe the solution....

Up to now (leading to the problem) I opened the workbook like

Please Login or Register  to view this content.

Now I tried this (based on your Power Query hint]:

I recorded a macro when setting up a Power Query connection to an (existing) file. Then I changed the target file name in the recorded code to a file that should not be found due to renaming. Of course this led to a Power Query error. Holding the mouse to the data connections bar displayed the yellow error message. In that message, the URL of the file (which was not found) was composed like this:

Please Login or Register  to view this content.

.

Note, since I'm a newbie in this forum I'm not allowed to post links, so [TEXT] stands for the beginning of the URL (that works).

Adding the "_api/web/getfilebyserverrelativeurl"-stuff to my report macro seems to give the expected results:

• Exact filename is there = File is opened
• Exact filename is not there (for whatever reason) = error message

I'll do a bunch of tests now and of course let you know if my heart has survived the day...

Many thanks!


Update: The REST API reference with "getfilebyserverrelativeurl" works properly and throws error messages when expected.