The Scenario: Let’s say you have a quote worksheet in Excel that you use to work up sales figures and such for your clients. You utilize SharePoint 2007 to manage and keep track of these documents. You would like values from your worksheet to populate SharePoint properties in the document library. For instance, you would like the Quote Date and Quote Amounts to populate in the SharePoint properties for the document.

Out-of-the-box there isn’t a simple way to make this happen. You can view and edit the Document Properties from SharePoint in Excel, but there isn’t a straightforward method of setting those properties from values within your Worksheets. We can, however, accomplish this by using a very simple VBA (Visual Basic for Applications) script.

If you are reading this tutorial, I assume that you are using SharePoint 2007 with Excel 2007 or Excel 2010 and have a basic understanding of working with both document libraries in SharePoint and with Excel spreadsheets. I will include some links to other tutorials if you are unsure how to complete certain steps. This method also works with SharePoint 2010.

Here’s how to add the VBA script to accomplish this:

  1. In SharePoint, create a new Document Library. (For help: http://www.hosting.com/support/sharepoint3/createdoclib)
  2. Add a new Column to the Document Library called “Quote Amount” and set the column type to Currency. (For help: http://office.microsoft.com/en-us/windows-sharepoint-services-help/add-edit-or-delete-a-column-HA001161199.aspx)
  3. Copy the URL to the document library from your address bar. (For example, if your URL is http://site/library/forms/allitems.aspx, select http://site/library/ only, the forms/allitems.aspx is the view you are looking at).
  4. Open Microsoft Excel and create a new Excel 2007/2010 workbook
  5. Save the Workbook as a Macro Enabled Workbook to your document library
    1. Go to the Office Button > Save As or File > Save As
    2. Paste the URL of the document library into the File Name box and hit enter
    3. Once you have browsed to your document library, give your document a name and change the type to Excel Macro-Enabled Workbook
  6. Change the value of Cell A7 to 125.99. Change the cell type to Accounting (the dollar symbol on the Home ribbon in the Number section)
  7. Select the Developer ribbon tab (if you need to enable the Developer tab, see http://office.microsoft.com/en-us/excel-help/show-the-developer-tab-or-run-in-developer-mode-HA010173052.aspx)
  8. Select the Document Panel button to show the SharePoint Document Properties for the document. This is where you could manually edit the properties if you wished.
  9. Select Insert > ActiveX Button
  10. Your mouse will be turned into crosshairs indicating you can draw a button. Somewhere on the document click and drag to create your button.
  11. Right-click your new button and select Properties
  12. Set the (Name) to “UpdateButton” and the Caption to “Update SharePoint Properties”
  13. Close the Properties window
  14. Double-click the button
  15. Microsoft Visual Basic for Applications will pop up with the blank function for the UpdateButton loaded
    Private Sub UpdateButton_Click()

    End Sub

  16. In the function (after the Private Sub line) add:
    For Each Prop In ThisWorkbook.ContentTypeProperties
    If Prop.Name = “Quote Amount” Then
    Prop.Value = Cells(7, 1).Value
    End If
    Next Prop
  17. This function looks for the Prop object in each of the current workbook’s ContentTypeProperties. If it finds the property matching the specified name (“Quote Amount”) is sets its value to our Cell A1. When addressing cells in VBA, it looks first for the row (7) and then for the numeric column (1 instead of A).
  18. You can add more properties if you wish. For example, if we had two:
    For Each Prop In ThisWorkbook.ContentTypeProperties
    If Prop.Name = “Quote Amount” Then
    Prop.Value = Cells(7, 1).Value
    End If
    If Prop.Name = “Quote Date” Then
    Prop.Value = Cells(8, 1).Value
    End If
    Next Prop
  19. Close the Microsoft Visual Basic for Applications window
  20. Save your document
  21. Every time you click the button you created, the SharePoint Document Properties will be updated on the Document Properties Panel and updated in SharePoint when you save the document.

Pin It on Pinterest

Share This
%d bloggers like this: