Trying to open text file via SharePoint with VBA

I'm trying to open a text file via Sharepoint to read but keeping getting an error message (Run-time 52 Bad name or number).

I get the debug error on strFile line: Open strFile For Input as #1

Dim FileToOpen as Variant
Dim sh as Worksheet
Dim strFile As String
    
    '-------Open Text file -------------
Set FileToOpen = Application.FileDialog(msoFileDialogFilePicker)

With FileToOpen
        .Filters.Clear
        .Filters.Add "Text Files", "*.tx*"
        .Title = "Browse to text File"
        .AllowMultiSelect = False
        .InitialFileName = "https:\\my.sharepoint.com\sites\THALI01\"
        If .Show = True Then
            strFile = .SelectedItems(1)
        End If
    End With

   '''-----reading lines on text file-----

 Open strFile For Input As #1
    Do Until Left(Oneline, 42) = "Mapping Definition Name: "
      Line Input #1, Oneline
      DoEvents
    Loop
    
        Acq = Trim(Mid(Oneline, 42, 30))
        sh.Range("B8") = Acq
 
End Sub

Solution

You cannot call Open on a file using an HTTP path: that's for local/network files only. You'll need to download the file and open the local copy.

For example, using the Windows API URLDownloadToFile method:

'API declarations
#If VBA7 Then
    Private Declare PtrSafe Function URLDownloadToFile Lib "urlmon" _
      Alias "URLDownloadToFileA" (ByVal pCaller As LongPtr, ByVal szURL As String, _
      ByVal szFileName As String, ByVal dwReserved As Long, ByVal lpfnCB As LongPtr) As Long
#Else
    Private Declare Function URLDownloadToFile Lib "urlmon" _
      Alias "URLDownloadToFileA" (ByVal pCaller As Long, ByVal szURL As String, _
      ByVal szFileName As String, ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long
#End If

Sub ChooseAndOpenFileFromSharePoint()
    Dim FileToOpen As Variant, tempFile As String
    Dim sh As Worksheet
    Dim strFile As String
        
    '-------Open Text file -------------
    Set FileToOpen = Application.FileDialog(msoFileDialogFilePicker)
    
    With FileToOpen
        .Filters.Clear
        .Filters.Add "Text Files", "*.tx*"
        .Title = "Browse to text File"
        .AllowMultiSelect = False
        .InitialFileName = "https://theravance.sharepoint.com/sites/RIR/TestLibrary/"
        If .Show = True Then
            strFile = .SelectedItems(1)
            tempFile = TempPath()
            DownloadFile strFile, tempFile
            Debug.Print "Downloaded:" & vbLf & "    " & strFile & _
                         vbLf & "to" & vbLf & "     " & tempFile
        Else
            Exit Sub
        End If
    End With
    
    'process file at `tempFile`....
    
 
End Sub

'return a full path to a file in the user's Temp folder
Function TempPath() As String
    With CreateObject("scripting.filesystemobject")
        TempPath = .BuildPath(.GetSpecialFolder(2), .GetTempName)
    End With
End Function

Function DownloadFile(sURL, sSaveAs) As Boolean
    DownloadFile = (URLDownloadToFile(0, sURL, sSaveAs, 0, 0) = 0)
End Function