如何通过Excel宏将文件保存到SharePoint并自动设置文档属性？

你已经实现了Excel和PDF文件保存到SharePoint的功能，现在可以通过SharePoint Client Object Model（CSOM）在VBA中自动化设置**内容类型（Content Type）和维护文档类型（自定义列）**属性，以下是完整实现代码：

Sub SaveToSPAndSetProperties()
    ' 定义变量
    Dim SPlocation As String, SPSiteUrl As String, SPLibraryName As String
    Dim SPExcelFile As String, SPPdfFile As String
    Dim clientContext As Object, oList As Object, oFile As Object, oListItem As Object
    Dim contentTypeName As String, maintenanceDocTypeValue As String, maintenanceDocColumnName As String
    
    ' 配置参数 - 根据你的实际情况修改
    SPlocation = "https://your-sharepoint-site.com/sites/your-site/Documents" ' 文档库完整路径
    SPSiteUrl = "https://your-sharepoint-site.com/sites/your-site" ' SharePoint站点根URL
    SPLibraryName = "Documents" ' 文档库名称
    contentTypeName = "你的内容类型名称" ' 目标内容类型名称（如"维护文档"）
    maintenanceDocColumnName = "Maintenance Document Type" ' SharePoint中对应列的内部名称（注意大小写匹配）
    maintenanceDocTypeValue = "你的维护文档类型值" ' 要设置的属性值（如"月度报告"）
    
    ' 生成文件名并保存Excel和PDF
    SPExcelFile = SPlocation & "/" & Year(Date) & "_" & Month(Date) & "_" & Day(Date) & "_" & Split(ThisWorkbook.Name, ".")(0) & ".xlsm"
    SPPdfFile = SPlocation & "/" & Year(Date) & "_" & Month(Date) & "_" & Day(Date) & "_" & Split(ThisWorkbook.Name, ".")(0) & ".pdf"
    
    ThisWorkbook.SaveAs Filename:=SPExcelFile, FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
    ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:=SPPdfFile
    
    ' --------------------------
    ' 设置Excel文件的SharePoint属性
    ' --------------------------
    On Error Resume Next
    ' 初始化SharePoint客户端上下文（后期绑定，无需手动添加引用）
    Set clientContext = CreateObject("Microsoft.SharePoint.Client.ClientContext")
    clientContext.Site = SPSiteUrl
    ' SharePoint Online 用这个凭证（运行时会弹出登录窗口）
    clientContext.Credentials = CreateObject("Microsoft.SharePoint.Client.SharePointOnlineCredentials")
    ' 本地SharePoint服务器替换为以下凭证：
    ' clientContext.Credentials = CreateObject("System.Net.NetworkCredential", "用户名", "密码", "域名")
    
    ' 获取目标文档库
    Set oList = clientContext.Web.Lists.GetByTitle(SPLibraryName)
    ' 获取刚保存的Excel文件
    Set oFile = clientContext.Web.GetFileByServerRelativeUrl(Replace(SPExcelFile, SPSiteUrl, ""))
    ' 获取文件对应的ListItem对象
    Set oListItem = oFile.ListItemAllFields
    
    ' 通过内容类型名称查找并设置ContentTypeId
    Dim contentTypes As Object, ct As Object
    Set contentTypes = oList.ContentTypes
    clientContext.Load(contentTypes)
    clientContext.ExecuteQuery
    
    For Each ct In contentTypes
        If ct.Name = contentTypeName Then
            oListItem("ContentTypeId") = ct.Id.ToString
            Exit For
        End If
    Next
    
    ' 设置维护文档类型属性
    oListItem(maintenanceDocColumnName) = maintenanceDocTypeValue
    
    ' 提交属性更新
    oListItem.Update()
    clientContext.ExecuteQuery
    
    ' --------------------------
    ' （可选）设置PDF文件的属性，不需要可删除此段
    ' --------------------------
    Set oFile = clientContext.Web.GetFileByServerRelativeUrl(Replace(SPPdfFile, SPSiteUrl, ""))
    Set oListItem = oFile.ListItemAllFields
    
    ' 设置PDF的内容类型（如需不同类型，修改contentTypeName即可）
    For Each ct In contentTypes
        If ct.Name = contentTypeName Then
            oListItem("ContentTypeId") = ct.Id.ToString
            Exit For
        End If
    Next
    
    oListItem(maintenanceDocColumnName) = maintenanceDocTypeValue
    oListItem.Update()
    clientContext.ExecuteQuery
    
    ' 清理对象
    Set oListItem = Nothing
    Set oFile = Nothing
    Set oList = Nothing
    Set clientContext = Nothing
    
    If Err.Number <> 0 Then
        MsgBox "设置属性时出错：" & Err.Description, vbCritical
    Else
        MsgBox "文件保存及属性设置完成", vbInformation
    End If
    On Error GoTo 0
End Sub

关键注意事项 #

• 凭证适配：SharePoint Online会自动弹出登录窗口；本地服务器需替换为对应域名、用户名和密码。
• 参数准确性：必须将代码中配置参数替换为你的实际SharePoint站点、文档库、内容类型及列信息。
• 列名匹配：maintenanceDocColumnName必须和SharePoint文档库中对应列的内部名称完全一致（可通过列表设置页面查看内部名称）。
• 后期绑定：代码采用后期绑定，无需手动添加COM引用；若需早期绑定，可添加Microsoft SharePoint Client Runtime和Microsoft SharePoint Client Object Model引用。

内容的提问来源于stack exchange，提问作者vincent strobbe