Accessing SharePoint Text file via VBA

I originally posted this query in the SharePoint community a while ago but didn't get a response so I'm posting here.

I have a VBA macro in MS Project that needs to read a text file located on SharePoint.

I have tried two different methods of opening the file (code below [https replaced with xxxxs as it wouldn't let me post]), both of which work fine on a local copy but fail when using SharePoint. The SharePoint I'm using is not an in-house system but is using Microsoft's SharePoint service offering. I have tried names using %20 instead of spaces and just using spaces with the same result.

In my searches I have found multiple old references (10 years plus) about mapping drives or using UNC names. But mapping drives or using UNC are not possible with the SharePoint we use (I have tried). I have also found this - https://learn.microsoft.com/en-us/previous-versions/office/developer/sharepoint-2010/ms470176(v=offi... that originally gave me some hope, but that is for VB and not VBA.

I did consider OneDrive but the location of the file differs depending on whether the user has set up OneDrive sync or OneDrive shortcut from SharePoint. Plus, I need an option that will work regardless of a OneDrive connection being set up or not.

Thanks in advance for any assistance.

=======================================================

    'Const strLookup As String = "xxxxs://xxx.xxx.com/sites/xxxx/Shared%20Documents/xxxxx/xxxxx/My%20Text%20File.txt"

    ' Const strLookup As String = "\\xxx.xxx.com/sites\xxxx\Shared Documents\xxxx\xxxx\My Text File.txt"

    ' Const strLookup As String = "C:\Temp\My Text File.txt "

    intOutFileNum = FreeFile

    Open strLookup For Input As #intOutFileNum

    Line Input #intOutFileNum, strDataLine      ' read in first line

    MsgBox (strDataLine)

    Close #intOutFileNum

    Const ForReading = 1, ForWriting = 2, ForAppending = 8

    Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0    Set objFileSystem = CreateObject("Scripting.FileSystemObject")

    Set objFileSystem = CreateObject("Scripting.FileSystemObject")

    Set objTextStream = objFileSystem.OpenTextFile(strLookup, ForReading, False, TristateFalse)

    strDataLine = objTextStream.ReadLine

    MsgBox (strDataLine)

    objTextStream.Close