Awards
- Community Pick
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:
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
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/sup
port/share point3/cre atedoclib)
- 2
- Add a new Column to the Document Library called “Quote Amount” and set the column type to Currency. (For help: http://office.microsoft.co
m/en-us/wi ndows-shar epoint- ser vices-help /add-edit- or-delete- a-column-H A001161199 .aspx)
- 3
- Copy the URL to the document library from your address bar. (For example, if your URL is http://site/library/forms/
allitems.a spx, 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.co
m/en-us/ex cel-help/s how-the- de veloper-ta b-or-run-i n-develope r-mode-HA0 10173052.a spx)
- 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
- 16
- In the function (after the Private Sub line) add:
- 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:
- 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.nam
by: ericpete on 2010-09-13 at 09:19:26ID: 19356
Your article has been published, and it designated as a Community Pick. We hope to see more articles from you.
Thanks,
ericpete
Page Editor