Open Excel file on Sharepoint server from VBA

[SteveJCO]

Hello all, I've been using VBA in Excel to write scripts to automate order processing for seven years. About six months ago my company started moving projects from a network share to a Sharepoint server, and that's when this problem began.

I'm unable to open any Excel file on a Sharepoint server from a VBA script in an Excel file located on the same server. I can open the files if they're located on a network share. I've read and tried all the variations and I'm not having any success. Do you have a suggestion?

The code that seemingly should work is:

Filespec = “https://pathto.sharepoint_server/folder/myfile.xlsx”
Application.Workbooks.Open Filename:=filespec

Or if you want to assign to a Workbook object, you can say

Dim wb as Workbook
Filespec = “https://pathto.sharepoint_server/folder/myfile.xlsx”
Set wb = Application.Workbooks.Open (Filename:=filespec)

Some say you need to specify the filename as a WebDAV filespec:

Filespec – “//pathto.sharepoint_server/folder/myfile.xlsx”

Some say if it’s https then you have to specify @SSL:

Filespec – “//pathto.sharepoint_server@SSL/folder/myfile.xlsx”

Some say the forward slashes should be backslashes. And on and on. A million permutations, and none of them work.

I do not want to open a locally-cached copy, it needs to be the server version. Please, someone show me how to do this. It can't be this complicated!

 

[SteveJCO]

Doesn't anyone know how to open a file located on a Sharepoint server from VBA?

 

[Fraxav]

In a very simplistic attempt to do something similar, I found that treating it as a local folder seems to work to open a dialog box:

Sub Import()
Dim wb As Workbook

    With Application.FileDialog(3)
         .AllowMultiSelect = False
         .InitialFileName = "https://company.sharepoint.com/sites/MyTeam/Shared%20Documents/MyGroup/Data/"
         
         If .Show Then
         
            fullpath = .SelectedItems.Item(1)
            Set wb = Workbooks.Open(fullpath)
         End If
         If wb Is Nothing Then Exit Sub
        wb.Close False
    End With
End Sub

 

[rovert_enrobso]

Just like you I also tried every one of those steps to alter the URL that I found on various posts from this forum and others. I finally came across a post that provided a breakthrough solution after all the failed attempts at changing the SP file url. The url I was passing to Workbooks.Open() method was obtained by clicking "Copy link" for the designated file on the SP site. The miracle post that I discovered detailed that a subtle change needed to be made to obtain the url that would successfully locate and open the file.

The process is as follows:
- On the SP site, click the "Show actions" icon (kebab menu) next to the designated file and select Open -> Open in app
- Once the file opens, navigate to the 'File' tab on the Ribbon and select 'Info' tab on the sidebar
- You should see the file name at the top of the pane and the file breadcrumb trail underneath with the option to copy the path

- Paste the copied path as is to the Workbooks.Open() method and it should open successfully

When I compared the path copied from the file breadcrumb trail on the Info tab to the path that was copied from the SP site, the subtle difference I noticed was that my personal credentials were present in the breadcrumb trail path and there were actually some different folder references.

Info tab path: https://[companyname]-my.sharepoint.com/personal/[myname]-[companyname]-com/[folders leading to file]?web=1
vs
SP site path: https://[companyname]portal.sharepoint.com//r/sites/[folders leading to file]?d=xxxxxxxxxxxxxxxxxx&csf=1&web=1&e=xxxxx

Hopefully this helps. I am no expert but I believe the reason that there are so many posts with different solutions that work for others is just due to the permission settings that are configured for different companies/users. Looking at the paths, when I actually physically access the file it is being pulled from a different path then what is present on the main SP site (looks like it stores the files that I have access to in my personal directory). I just had my first encounter with SharePoint this week and had only created macros that navigated files located on a network drive so I was banging my head for awhile.