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!
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

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?
 

Watch MrExcel Video

Forum statistics

Threads
1,113,777
Messages
5,544,174
Members
410,596
Latest member
JoeyZ
Top