Community Pick: Many members of our community have endorsed this article.

SharePoint - Update SharePoint Document Property from Excel VBA

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 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 or SharePoint 2010 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.

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


In SharePoint, create a new Document Library. (For help:


Add a new Column to the Document Library called “Quote Amount” and set the column type to Currency. (For help:


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).


Open Microsoft Excel and create a new Excel 2007/2010 workbook


Save the Workbook as a Macro Enabled Workbook to your document library
a. Go to the Office Button > Save As or File > Save As
b. Paste the URL of the document library into the File Name box and hit enter
c. Once you have browsed to your document library, give your document a name and change the type to Excel Macro-Enabled Workbook


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)


Select the Developer ribbon tab (if you need to enable the Developer tab, see


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.


Select Insert > ActiveX Button


Your mouse will be turned into crosshairs indicating you can draw a button. Somewhere on the document click and drag to create your button.


Right-click your new button and select Properties


Set the (Name) to “UpdateButton” and the Caption to “Update SharePoint Properties”


Close the Properties window


Double-click the button


Microsoft Visual Basic for Applications will pop up with the blank function for the UpdateButton loaded
Private Sub UpdateButton_Click()
                      End Sub

Open in new window


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

Open in new window


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).


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

Open in new window


Close the Microsoft Visual Basic for Applications window


Save your document


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.
And there you have it.  By using this method, you can create Excel documents that can update their SharePoint document properties programmatically.

Original Article:

Comments (4)


Congratulations, chapmanjw!

Your article has been published, and it designated as a Community Pick. We hope to see more articles from you.


Page Editor


What if I wanted to populate an excel worksheet cell (which is an excel template / content type for Purchase Orders) with the Purchase Order number associated with the item via an SPD Workflow?

what if I want the url of the document library? how do I grab that??

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.