Creating a new folder in Sharepoint using VBA

Question

I'm trying to automatically save workbooks using VBA to a Sharepoint folder- which needs to be created first. Code I have below works ONLY for saving the file. The MkDir function returns a Run-time error 76: Path not found. I'm at a loss what causes this error, because the path before /txt DOES exist and I do have the right permissions to add new folders by hand. I have also tried replacing the current path with \ instead of /, but other than making the code significantly slower this has no effect. No "solutions" online have done the trick for me.

Any ideas?

MkDir "https://placeholder.sharepoint.com/teams/Services_NL/Shared Documents/txt"
DateAndTime = Left(Replace(Replace(Replace(Now, " ", "_"), ":", ""), "/", ""), Len(Replace(Replace(Replace(Now, " ", "_"), ":", ""), "/", "")) - 5)
ActiveWorkbook.SaveAs "https://placeholder.sharepoint.com/teams/Services_NL/Shared Documents/Export/" & ExportSheet & DateAndTime & ".txt", FileFormat:=xlTextWindows

Solution 1

I had tried all different combinations of forward slash and backslash, "https://"...etc. But I finally got the MkDir command to work using the following syntax:

MkDir "\\mysite.sharepoint.com\IS\Shared Documents\My Docs\" & "MyNewFolder"

Note: Replace any %20 (used by Sharepoint to substitute a blank space in the URL) with a blank space. Or better yet, copy the folder address directly from the Windows Explorer navigation bar and then add the backslash to the end of it.

Also, I believe its important to first test the ability of your environment (permissions or otherwise) by opening your Sharepoint directory folders within Windows Explorer and navigating through them as a test. If you can do this, then MKDir command should work because you've proven the file directory is accessible.

The "Open with Explorer" icon is found on the Library tab within Sharepoint (I'm using Office 365). I've found that in some Sharepoint environments I've worked with the Library tab is accessible by default, but in others it is hidden (only the Browse and Page tabs are viewable). In that case, I got the Library (and Files) tab to appear by navigating into a Sharepoint folder, highlighting a bunch of files, and then just right-clicking on the highlighted section without clicking on any of the flyout options. Odd behavior, but it worked.

Solution 2

I am having the same issue, using Office 365 from both Win 7 and Win 10 to Sharepoint 2013. It may relate to a security feature which interferes with file System Object actions to online addresses with VBA, resulting in

error 76 path not found; and error 70 permission denied; and also error 52 bad file name.

We have several Win 10 Office 206 users with VBA, macros, trusted locations, user permissions, explorer navigation, UI folder creation, etc are all fully functional, and set in MSWord and SP2013: errors 70, 76 and sometimes 52 result when the VBA tries to Save As to the Sharepoint folder they all have unfettered access to.

The same users, running Office 2010, to the same server, using the same VBA, have no problems with file system Object saving, navigation etc.

I have found that mapping and un-mapping the Sharepoint 2013 path/drive/folder, in the session with VBA seems to make it work. I tried that on Fri Feb 22 2019. In the VBA immediately before the navigation and creating folders code, I mapped the Shared Documents library to the next available drive (step through if exist for drive-letters 67 to 89, then do the Save As, then remove the mapping. I'll be testing more tomorrow Mon Feb 24 2019.

my path string is: \\sp.url@ssl\davwwwroot\site\subsite\shared%20documents and the drive letter as noted is dynamic based on whatever the user has.

The path in my string therefore is using teh WebClient service, and that has been very reliable for me under SP2013. until Office 365/Office 2016 that is.