Map SharePoint drive using VBA

[RogueTemplat]

Hi

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_librarey_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"

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

Thanks

Simon

[RogueTemplat]

Update ...

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.

[RogueTemplat]

Further update ...

I have discovered I can get the code to work if I ...

1) Add the site to Internet Explorer's "Trusted Sites" - must be IE not Edge or Chrome etc.
2) Make sure I have gone to the site on IE at least once

Am pleased I have a mapping but I can't really insist on these things happening for users on different machines.

Is there another way? - I have seen mention of using ADO recordsets but not found a viable example of that and not sure if that works for sites that need a login. Does anyone have an example of doing it that way?

[abel1303]

You can then use the mapped network drive in your FileSystemObject code to parse and work with the SharePoint files.