Word VBA MkDir Invalid Path in Sharepoint

Question

I am trying to use VBA to create a folder within Sharepoint at my work. The document is opened from Sharepoint so there should be no credential issues (I would think).

I have tried all of the following and always get Run-time error '76': Path not found

How .Path reads the document's location (having removed the document obviously)

MkDir "https://company.sharepoint.com/directory/directory with spaces"

Without certificate

MkDir "//company.sharepoint.com/directory/directory with spaces"

With backslashes between directories

MkDir "https://company.sharepoint.com\directory\directory with spaces"

With corrected spaces

MkDir "https://company.sharepoint.com/directory/directory%20with%20spaces"

and most combinations of the above.

I noted that it takes much longer for Word to decide it's an invalid path without certificate.

I cannot post the actual paths due to NDA issues, but the above recreation should have all pertinent possible issues within the path. I am not parsing the path from variables or input (though I will later) and they are held within a private sub.

I appreciate any help you can give.

this answer temporarily maps the site as a network drive and adds the folder https://stackoverflow.com/a/31075139/2727437 — Marcucciboy2, Aug 07 '18 at 16:32
for future reference, your examples come from this answer https://stackoverflow.com/a/18751751/2727437 — Marcucciboy2, Aug 07 '18 at 16:50
That's funny. I actually thought of those changes without being able to find that information. — Garrett Williamson, Aug 08 '18 at 08:13

score 0 · Accepted Answer · answered Aug 07 '18 at 18:38

Okay, this took me far longer to complete than I expected. I essentially just grabbed the solution from the link in my first comment link I above and added error handling so that (hopefully) all scenarios have a good exit point and explanation.

Sub SharepointAddFolder()

    Dim filePath As String
    filePath = "https://web.site.com/SharedDocuments/Folder"

    'filePath = Replace(filePath, "https:", "")     'I didn't need these but who knows
    'filePath = Replace(filePath, "/", "\")
    'filePath = Replace(filePath, " ", "%20")

    Dim newFolderName As String
    newFolderName = "New Folder"

    Dim driveLetter As String
    driveLetter = "Z:"

    Dim ntwk As Object
    Set ntwk = CreateObject("WScript.Network")

    On Error GoTo ErrHandler
    ntwk.MapNetworkDrive driveLetter, filePath, False ', "username", "password"

    If Len(Dir(driveLetter & "/" & newFolderName, vbDirectory)) = 0 Then
        MkDir driveLetter & "/" & newFolderName
    Else
        MsgBox "Folder " & newFolderName & " already exists."
    End If

ExitThis:
    ntwk.RemoveNetworkDrive driveLetter
    Exit Sub

ErrHandler:
    Select Case Err.Number
        Case -2147024829
            MsgBox "Sharepoint site not found"

        Case 76
            'sharepoint directory not found
            MsgBox "Mapping failed"

        Case -2147024811
            'drive already mapped
            Resume Next

        Case -2147022646
            'drive not found and thus cannot be closed

        Case -2147022495
            MsgBox "This network connection has files open or requests pending." & vbNewLine & vbNewLine & _
                "Either close the files or wait until the files are closed, then try to cancel the connection."

        Case Else
            MsgBox "Error " & Err.Number & ": " & vbNewLine & Err.Description
    End Select

End Sub

Thanks. At first I had the issue of already having a Z drive, but eventually I rememebered I forget stupid stuff. I am currently having user authentication issues, but that is another question. I appreciate your help. — Garrett Williamson, Aug 08 '18 at 08:25
@GarrettWilliamson Hmm okay yeah then I should probably change around my error for `'drive already mapped'` because of the example you gave — Marcucciboy2, Aug 08 '18 at 12:43

score 0 · Answer 2 · answered Aug 10 '18 at 11:17

Note for those of you desiring to continue to work on temporarily mapping SharePoint drives: this code work without any need for username or password (My company uses Authenticator), but only once you have logged in to SharePoint using Internet Explorer. I learned that, when using IE, an option under “All Documents” called “View in File Explorer” exists. It does not exist for Chrome or other browsers (as far as I know). My intent was to permanently map the drives, but once I had logged in from IE the code worked. You do not even have to stay logged in to IE and, when you return to the SharePoint via IE, you are still logged in (I have done this over a one day no-use period). I assume this something to do with the IE being a Microsoft product and therefore being trusted to keep login credentials.

Word VBA MkDir Invalid Path in Sharepoint

2 Answers2