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
For Each vrtSelectedItem In .SelectedItems
    Set SummaryWB = Workbooks.Open("Financial Trackerv8.xlsx")
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?


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

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 = "TeamEmail@mail.com"
    .To = ""
    .CC = ""
    .BCC = IntMgrStr

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

    .Display   'or use .Send
End With

On Error GoTo 0
End If

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.