Open an Excel file from SharePoint site

Question

I'm trying to open an Excel file from SharePoint using VBA. Because the file I'm looking for might be different each time I run the macro, I want to be able to view the SharePoint folder and select the file I need.

The code below works fine when I want to look for a file on a network drive, however when I replace that with a SharePoint address I get "run-time error 76: Path not found".

Sub Update_monthly_summary()

Dim SummaryWB As Workbook
Dim SummaryFileName As Variant

ChDir  "http://sharepoint/my/file/path"
SummaryFileName = Application.GetOpenFilename("Excel-files,*.xls", _
1, "Select monthly summary file", , False)
If SummaryFileName = False Then Exit Sub

Set SummaryWB = Workbooks.Open(SummaryFileName)

End Sub

When I paste this address into Windows Explorer I have no problems accessing the SharePoint folder, so I know the path is correct.

Why doesn't VBA like it?

Answer 1

Try this code to pick a file from a SharePoint site:

Dim SummaryWB As Workbook
Dim vrtSelectedItem As Variant

With Application.FileDialog(msoFileDialogOpen)
    .InitialFileName = "https://sharepoint.com/team/folder" & "\"
    .AllowMultiSelect = False
    .Show
    For Each vrtSelectedItem In .SelectedItems
        Set SummaryWB = Workbooks.Open(vrtSelectedItem)
    Next
End With

If SummaryWB Is Nothing then Exit Sub

If I remember correctly, the Microsoft Scripting Runtime reference must be enabled. Also, your site may use backslashes, mine uses forward slashes.