Open Excel file on Sharepoint server from VBA

SteveJCO

New Member
Joined
Oct 14, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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!
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

SteveJCO

New Member
Joined
Oct 14, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Doesn't anyone know how to open a file located on a Sharepoint server from VBA?
 

Fraxav

New Member
Joined
Dec 7, 2020
Messages
16
Office Version
  1. 365
Platform
  1. Windows
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:

VBA Code:
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

New Member
Joined
Dec 10, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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
1607627393855.png

- 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.
 

Forum statistics

Threads
1,147,922
Messages
5,743,890
Members
423,824
Latest member
leahpatterson

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top