Map SharePoint drive using VBA

Question

I am trying to map a SharePoint document library so I can parse it with a FileSystemObject.

Here is my code ...

Dim objNetwork  As Object
    
Set objNetwork = CreateObject("WScript.Network")
objNetwork.MapNetworkDrive "Z:", _
    "\\<mydomain>.sharepoint.com\sites\<my_sharepoint_site>\<document_library_site>", _
    False, _
    "<username>", _
    "<password>"

MsgBox "hello"

objNetwork.RemoveNetworkDrive "Z:"
Set objNetwork = Nothing

"mydomain" is my domain associated with Office 365

"my_sharepoint_site" is my main SP site (same as domain but has caps in it, no spaces)

"document_library_site" is a document library I added. It has two words with caps and a space, e.g. "BBC%20News"

All these were lifted from the web browser's address bar when on the document library's home page.

"username" and "password" are my Microsoft / Office 365 login

I am sure I have those right.

Z: is not already a drive.

The error I get is ...

"Run-time error '-2147023652 (800704dc)"

"The operation being requested was not performed because the user has not been authenticated"

---

I googled the error and found an MS resolution that involves tweaking the Registry.

https://docs.microsoft.com/en-us/troubleshoot/windows-client/networking/error-access-network-drive-mapped-web-share

I added "https://"mydomain".sharepoint.com" as my Server List and restarted WebClient service but same error occurs.

---

What am I doing wrong, do I need to change permissions in SharePoint somehow?

Thanks

Simon

Answer 1

This is an old question, but I wanted to share the solution I found to the authentication problem. The trick is to create a FileDialog object and point its InitialFileName to the URL of the SharePoint server like this:

Dim objNetwork  As Object
Dim objFd       As FileDialog
    
Set objFd = Application.FileDialog(msoFileDialogFolderPicker)
objFd.InitialFileName = "https://<mydomain>.sharepoint.com"   'force user authentication
Set objNetwork = CreateObject("WScript.Network")
objNetwork.MapNetworkDrive "Z:", _
    "\\<mydomain>.sharepoint.com\sites\<my_sharepoint_site>\<document_library_site>", _
    False

MsgBox "hello"

objNetwork.RemoveNetworkDrive "Z:"
Set objNetwork = Nothing

Hope this works for you as well.