VBA - How to open & use workbooks/documents stored on Sharepoint Online (Office365)

Question

I got an Excel-VBA app that opens & reads another Workbook and a Word Document. Paths and Filenames are stored on a worksheet field.

Currently, the app opens the files from a private domain Sharepoint. We migrate to Office365. I have problem adapting the code. I am not understanding how to manage (keep and use) a reference to the office files when stored on Sharepoint365. Are the links dynamic ? How are they supposed to be accessed via VBA ? What about the authentication ?

When I tried to simply replace the current URLs to the local private Sharepoint document/workbook by the Office365 URLs, the VBA methods succeed at opening the files but their content is unavailable (I get blank documents).

I looked in support and forums but could not find comprehensive info on the matter. Am I supposed to use a REST API ? If so, how ? Can anybody point out or write a step by step explanation for newbie ?

The code is simple as can be :

1- For the Excel workbook (to do : open & search in range ; problem wkb is blank)

[...]

Set bookURLs = Workbooks.Open(fileName)
Set searchRange = bookURLs.Worksheets(1).Columns(1)

[...]

2- For the Word document (to do : open & search using bookmarks ; problem doc is blank)

[...]

Set appWord = New Word.Application
With appWord
    Set docTarget = .Documents.Open(fileName)
    docTarget.Bookmarks([...]).Range.Copy

[...]

D77F D77F · Accepted Answer · 2019-04-09T21:27:41

Found out something that does the trick for me...

It turned out that the problem is the URL format used in Sharepoint Online. When the link is obtained via "Copy link", it includes info on the file type and access that will be granted to the holder of the link. It also includes a GUID to the file instead of the path & filename. What I needed for programmatic access was an "old fashioned" link format. There is a way around to get that latter one : display the version history of the file and copy the URL to the latest version. This URL will be accepted by my old VBA coding.

(I also noticed that if the files are shared via Ms-Teams ; the UI in Teams will offer the 2 different format to copy straight from the file explorer.)

My problem is solved. If anyone knows better - for instance, a way to use the URL with GUID within VBA ; then any additional knowledge would be nice !

VBA - How to open & use workbooks/documents stored on Sharepoint Online (Office365)

1 Answers