Creating folders in sharepoint site with VBA

Question

hey all,

I'm trying to write a VBA script that will connect to SharePoint site : https://name.sharepoint.com/sites/name/folder/folder2, will check whether folder1 and folder2 already exist, and will create as them as necessary.

So far the only thing that work for me is mapping the site as a drive , but since this script is intended to be used by a broad audience I prefer to find a safer alternative, does anyone have any idea in mind ?

Thx in advance

Answer 1

I found out a way to get it working. The key is to temporarily map the site's main folder to a drive letter. From there, you can treat it like a folder on your local computer drive.

Sub CreateFolderInSharePointAndUnmount()

    Dim network As Object

    Dim folderPath As String

    Dim folderName As String

    Dim driveLetter As String

    Dim fs As Object

    ' Create instance of WScript.Network object

    Set network = CreateObject("WScript.Network")

    ' Specify the SharePoint site URL

    Dim sharepointURL As String

    sharepointURL = "https://your_sharepoint_site_url_here/sites/your_site_name_here/library_name"

    ' Specify the drive letter to map

    driveLetter = "Z:" ' Change this to your preferred drive letter

    ' Map the SharePoint document library to a network drive

    On Error Resume Next

    network.MapNetworkDrive driveLetter, sharepointURL

    If Err.Number <> 0 Then

        MsgBox "Failed to map network drive. Error: " & Err.Description, vbCritical

        GoTo UnmapDrive

    End If

    On Error GoTo 0

    ' Specify the folder path within the mapped drive

    folderPath = driveLetter & "\Test"

    ' Check if the folder already exists

    If Dir(folderPath, vbDirectory) <> "" Then

        MsgBox "Folder already exists.", vbExclamation

        GoTo UnmapDrive

    End If

    ' Create the folder

    Set fs = CreateObject("Scripting.FileSystemObject")

    fs.CreateFolder folderPath

    ' Check if folder creation was successful

    If Dir(folderPath, vbDirectory) <> "" Then

        MsgBox "Folder created successfully.", vbInformation

    Else

        MsgBox "Failed to create folder.", vbCritical

        GoTo UnmapDrive

    End If

UnmapDrive:

    ' Unmap the network drive

    network.RemoveNetworkDrive driveLetter, True, True

    ' Clean up objects

    Set network = Nothing

    Set fs = Nothing

End Sub

Answer 2

Thanks David, that's a great solution, I tested it and it works like a charm. I wish MS agents could give users good answers like this one instead of just giving us generic answers. To be honest, in most forums the best answers come from users instead of agents, I wonder why they keep the agents.