使用Excel VBA更新SharePoint文档库列值

Hey Steven, nice work getting the save-to-SharePoint part working! To populate those three columns (ThisRep, ThisCost, ThisTotal) in your SharePoint document library, you'll need to interact with SharePoint's list item properties after saving the file. Here's a straightforward approach using the SharePoint Client Object Model (CSOM) in VBA:

Step 1: Set Up Required References #

First, you need to add the SharePoint CSOM libraries to your VBA project:

1. Open the VBA Editor (Alt + F11 in Excel)
2. Go to Tools > References
3. Check the boxes for:
   • Microsoft SharePoint Client Runtime
   • Microsoft SharePoint Client
     Note: If these aren't listed, download and install the SharePoint Online Client Components SDK first.

Step 2: Updated VBA Code #

Here's your modified code that saves the workbook and updates the SharePoint library columns:

Sub SubmitApproval()
    Dim ThisPath As String
    Dim ThisRep As String, ThisCustomer As String, ThisDescription As String
    Dim ThisCost As Double, ThisTotal As Double
    Dim SaveFilePath As String
    
    ' --- Original save logic ---
    ThisPath = "https://recupeit.sharepoint.com/sales/Costing%20Sheets/"
    ThisRep = ThisWorkbook.Sheets("Quotation").Range("E11").Value
    ThisCustomer = ThisWorkbook.Sheets("Quotation").Range("B8").Value
    ThisDescription = ThisWorkbook.Sheets("Quotation").Range("B9").Value
    ThisCost = ThisWorkbook.Sheets("Hardware Costing Sheet").Range("J25").Value
    ThisTotal = ThisWorkbook.Sheets("Hardware Costing Sheet").Range("E25").Value
    
    SaveFilePath = ThisPath & ThisCustomer & " - " & ThisDescription & ".xlsm"
    ActiveWorkbook.SaveAs Filename:=SaveFilePath, FileFormat:=xlOpenXMLWorkbookMacroEnabled
    
    ' --- New: Update SharePoint library columns ---
    Dim clientContext As SharePoint.Client.ClientContext
    Dim web As SharePoint.Client.Web
    Dim list As SharePoint.Client.List
    Dim listItem As SharePoint.Client.ListItem
    Dim fileUrl As String
    
    ' Initialize SharePoint context
    Set clientContext = New SharePoint.Client.ClientContext(ThisPath)
    ' For SharePoint Online, use your credentials (optional if using logged-in account)
    ' clientContext.Credentials = New SharePoint.Client.SharePointOnlineCredentials("your-email", "your-password")
    
    Set web = clientContext.Web
    Set list = web.Lists.GetByTitle("Costing Sheets") ' Use your library's exact title
    
    ' Get the file's corresponding list item
    fileUrl = Replace(SaveFilePath, ThisPath, "") ' Extract relative file path
    Set listItem = list.GetItemByFileUrl(fileUrl)
    
    ' Update columns (use the INTERNAL names of your SharePoint columns!)
    listItem("ThisRep") = ThisRep
    listItem("ThisCost") = ThisCost
    listItem("ThisTotal") = ThisTotal
    
    ' Commit changes to SharePoint
    listItem.Update()
    clientContext.ExecuteQuery()
    
    ' Cleanup objects
    Set listItem = Nothing
    Set list = Nothing
    Set web = Nothing
    Set clientContext = Nothing
    
    MsgBox "File saved and SharePoint columns updated successfully!", vbInformation
End Sub

Key Notes to Avoid Headaches #

• Column Internal Names: Don't rely on the display name of your SharePoint columns—use the internal name instead. To find it:
  1. Go to your SharePoint library > Settings > Library Settings
  2. Under "Columns", click the column name
  3. Check the URL: the internal name is the value after Field=, e.g., Field=This_x0020_Rep means the internal name is This_x0020_Rep
• Permissions: Make sure your account has Edit access to the SharePoint document library
• On-Premises SharePoint: If you're using an on-prem server, swap out the authentication line to use NetworkCredentials instead of SharePointOnlineCredentials

This method ties the save and column update into one seamless process, so you won't have to manually input data after saving. Let me know if you hit snags with column names or authentication!

内容的提问来源于stack exchange，提问作者Steven Nel