<

[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x

SharePoint - Update SharePoint Document Property from Excel VBA

Published on
27,108 Points
20,108 Views
Last Modified:
Approved
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

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

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

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/
0
Comment
Author:chapmanjw
  • 2
4 Comments
 
LVL 15

Expert Comment

by:Eric AKA Netminder
Congratulations, chapmanjw!

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

Thanks,

ericpete
Page Editor
0
 

Expert Comment

by:pperisic
Awesome
0
 

Expert Comment

by:sullisnyc44
awesome!

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?



0
 

Expert Comment

by:sullisnyc44
what if I want the url of the document library? how do I grab that??
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Join & Write a Comment

Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month