Open File in a Sharepoint list via VBA

Question

I want to open an excel file in a Sharepoint list via VBA. Here is my code:

Sub OpenWB()

Dim SummaryWB As Workbook
Dim vrtSelectedItem As Variant

With Application.FileDialog(msoFileDialogOpen)
.InitialFileName = "https://sharepoint.com/teams/sdeteam/Shared Documents/folder/ALM Automated Transfer/" & "\"
.AllowMultiSelect = False
.Show
For Each vrtSelectedItem In .SelectedItems
    Set SummaryWB = Workbooks.Open("Financial Trackerv8.xlsx")
Next
End With

If SummaryWB Is Nothing Then Exit Sub

End Sub

It only opens the folder but not the file. What's supposed to be wrong with my code?

Answer 1

From my experience you are not able to directly open a file on SharePoint like that. You need to download the file first and then open it. I store the file in the user's temp folder via the code below, and if necessary delete it upon closing.

Sub DownloadSPfile()
Dim strFolder As String
Dim strName As String
Dim URL As String 

strFolder = Environ("temp")
strName = "\DART_db.accdb"
URL = <file location>

If Dir(strFolder & strName) <> "" Then ' if the temp file already exists then delete it first
      VBA.SetAttr strFolder & strName, vbNormal ' remove any file attributes (e.g. read-only) that would block the kill command
      VBA.Kill strFolder & strName ' delete the file
End If

FileCopy URL, strFolder & strName

End Sub

Answer 2

You can open a SharePoint file directly using VBA without having to save it to a temp folder. I use this code to open Email Templates with VBA that are stored on SharePoint, but you can modify it to open other files types as needed.

Sub SendIntEmail1()

Dim OutApp
Dim OutMail As Outlook.MailItem
Dim IVMmail, IntAttach As String

Sheets("Macro Run Controls").Select

IntAttach = Folder path to files I attach
IVMmail = \\SharePoint Site\Path to Email Templates\Email Template.oft

'Create the external manager email
If IntMgrStr <> "" Then
Set OutApp = CreateObject("Outlook.Application")

On Error Resume Next

Set OutMail = OutApp.CreateItemFromTemplate(IVMmail)
With OutMail
    .SentOnBehalfOfName = "[email protected]"
    .To = ""
    .CC = ""
    .BCC = IntMgrStr

    StrFile = Dir(IntAttach & "*.*")
    Do While Len(StrFile) > 0
        .Attachments.Add IntAttach & StrFile
        StrFile = Dir
    Loop

    .Display   'or use .Send
End With

On Error GoTo 0
End If