Accessing SharePoint Text file via VBA

[Gilgamesh1964]

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

[#2]

Hello @Gilgamesh1964 ,

How are you authenticating to the SharePoint Online tenant from VBA? Here are some posts for VBA in Excel that might help you get started: 

https://sharepoint.stackexchange.com/questions/272896/vba-autheticate-to-sharepoint-office-365 

https://social.msdn.microsoft.com/Forums/en-US/c6ec3042-636c-4df2-855a-d12daa0407d5/interacting-with... 

Paul

[#3]

Thank you for taking the time to respond.
I wasn't doing anything in VBA to authenticate. Our browsers, teams, OneDrive can access SharePoint after a normal logon to our domain with no further authentication requests, so some sort of federation exists.
My (obviously mistaken) belief was that would be sufficient for VBA, and I wouldn't need anything else.
I have looked at the links you provided and the details there are (currently) a bit much for me to worry about.
My need to read from SharePoint would have added a "nice to have" feature rather than a "must have" feature, so for the time being I may let it drop.

[#4]

Hello @Gilgamesh1964 ,

From VBA to connect to SharePoint Online, you would need to authenticate. For the apps you mention, MS handles the auth for those, in custom code, you'd need to handle the auth.

Paul