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

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

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,114,520
Messages
5,548,534
Members
410,845
Latest member
OldSwimmer1650
Top