VBA\C#.How to automatically map MS Project fields to a list on a SharePoint site

Use the Text# fields in Microsoft Project to store custom data. Here is an example that maps "Description" from SharePoint to the "Text5" field in Microsoft Project.

Sub MapFields()
    Dim projApp As MSProject.Application
    Dim projTask As MSProject.Task
    Dim customFieldNumber As Integer

    ' Set the Microsoft Project Application
    Set projApp = GetObject(, "MSProject.Application")

    ' Check if Microsoft Project is open
    If projApp Is Nothing Then
        MsgBox "Microsoft Project is not open. Please open Microsoft Project and try again.", vbExclamation
        Exit Sub
    End If

    ' Specify the custom field number (Text5)
    customFieldNumber = 5

    ' Loop through each task in the active project
    For Each projTask In projApp.ActiveProject.Tasks
        ' Update the Text5 field with the value from the SharePoint "Description" field
        projTask.TextFields(customFieldNumber).Text = GetDescriptionFromSharePoint(projTask)
    Next projTask

    ' Clean up
    Set projApp = Nothing
End Sub

To get the description from SharePoint in VBA it is not straightforward due to the limitations of VBA in interacting with SharePoint's API. However, you can use a workaround by mapping the SharePoint folder to a drive letter.

Option Explicit

Private oMappedDrive As Scripting.Drive
Private oFSO As New Scripting.FileSystemObject
Private oNetwork As New WshNetwork

Private Sub Class_Terminate()
    UnmapDrive
End Sub

Public Function MapDrive(NetworkPath As String) As Scripting.Folder
    Dim DriveLetter As String, i As Integer

    UnmapDrive

    For i = Asc("Z") To Asc("A") Step -1
        DriveLetter = Chr(i)
        If Not oFSO.DriveExists(DriveLetter) Then
            oNetwork.MapNetworkDrive DriveLetter & ":", NetworkPath
            Set oMappedDrive = oFSO.GetDrive(DriveLetter)
            Set MapDrive = oMappedDrive.RootFolder
            Exit For
        End If
    Next i
End Function

Private Sub UnmapDrive()
    If Not oMappedDrive Is Nothing Then
        If oMappedDrive.IsReady Then
            oNetwork.RemoveNetworkDrive oMappedDrive.DriveLetter & ":"
        End If
        Set oMappedDrive = Nothing
    End If
End Sub

Try this and let me know if it helps.