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:

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

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
                      

Select all Open in new window

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
                      

Select all Open in new window

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
                      

Select all Open in new window

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

Original Article: http://www.johnchapman.name/sharepoint-2007-%E2%80%93-update-sharepoint-document-property-from-excel-vba/