How to open a file on Sharepoint using VBA

Question

I am trying to open a file with a file name that changes every week. This means that part of the date in the file name is changing. In addition, this file is ONLY a file inside the folder. But its file name is changing. I use the code below, but it throws an error: "Runtime 52: Incorrect file name and number." I need your help.

 Dim ThePath As String
 Dim TheFile As String

        ThePath = "https://ts.company.com/sites/folder1/folder2/folder3/folder4/"
        TheFile = Dir(ThePath & "MANILA_ShiftRecord_*" & ".xlsx")
        Workbooks.Open (ThePath & TheFile)

Thank!

Answer 1

If it is a single file, you can use this approach:

Dim sharepointFolder As String
Dim colDisks As Variant
Dim objWMIService As Object
Dim objDisk As Variant
Dim driveLetter As String

'Create FSO and network object
Set objNet = CreateObject("WScript.Network")
Set fs = CreateObject("Scripting.FileSystemObject")

'Get all used Drive-Letters
Set objWMIService = GetObject("winmgmts:\\" & "." & "\root\cimv2")
Set colDisks = objWMIService.ExecQuery("Select * from Win32_LogicalDisk")

'Loop through used Drive-Letters
For Each objDisk In colDisks
    For i = 65 To 90
        'If letter is in use exit loop and remember letter.
        If i = Asc(objDisk.DeviceID) Then
            j = i
            Exit For
        'letters which are not checked yet are possible only
        ElseIf i > j Then
            driveLetter = Chr(i) & ":"
            Exit For
        End If
    Next i
    'If a Drive-Letter is found exit the loop
    If driveLetter <> "" Then
        Exit For
    End If
Next

'define path to SharePoint
sharepointFolder = "https://spFolder/Sector Reports/"
'Map the sharePoint folder to the free Drive-Letter
objNet.MapNetworkDrive driveLetter, sharepointFolder
'set the folder to the mapped SharePoint-Path
Set folder = fs.GetFolder(driveLetter)

After that, you can process the folder using the file system functions.