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:
- In SharePoint, create a new Document Library. (For help: http://www.hosting.com/support/sharepoint3/createdoclib)
- 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)
- 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
- Go to the Office Button > Save As or File > Save As
- Paste the URL of the document library into the File Name box and hit enter
- 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 http://office.microsoft.com/en-us/excel-help/show-the-developer-tab-or-run-in-developer-mode-HA010173052.aspx)
- 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
- 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 - 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 - 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.
hey how did you declare the variable “Prop”?
Hello
I have exactly the same question, as I can not find this variable in Excel already, someone had an idea how to declare it? thxs!
If you’re getting an error at compile time, there is a problem with the quotes around “Quote Amount”. Delete and rekey them, the routine will work.
Nice work.. This is useful to know and will keep this functionality in mind as we plan our projects
Hi John,
I am trying to figure out a way to tell Excel to display the properties when a user opens the workbook from SharePoint. We are using MOSS 2007 and Excel 2007 right now. Want to avoid having the users click the button. then Prepare and then Properties.
I tried recording a macro of those steps but nothing records.
Any ideas?
Regards,
Dave Jackson
You can probably use the on load option:
Private Sub Workbook_Open()
Run “code”
End Sub
OK, I found the code to turn DIP on and off (see below) but not to set the parameter to always display it upon opening.
Application.DisplayDocumentInformationPanel = True
Application.DisplayDocumentInformationPanel = False
Any thoughts on that?
Dave
Is there any way to get the URL of the doc library with Excel VBA?
When I do a custom file name (to ensure uniqueness) it wants to save to my local drive.
Thank you so much for posting this. I have spent the entire weekend working on an automated spreadsheet and this was the last piece missing; updating SharePoint column information from data within the spreadsheet. And your solution works like a charm! Thank you once again.
I’m glad it helped.
Hi,
I am trying to do something similar but slightly more complex.
I have a SharePoint document library which has several custom fields defined. Some of these are set to required to enforce users to populate them before they save a document.
I have code which was used with office XP saving to SharePoint 2007. In that code i populate the required ‘custom document properties’ and these map to the fields in my SharePoint document library. However, when i run the same code in Excel 2010 to save a .xlsx file, SharePoint does not recognise the custom document properties and the save fails because i have failed to populate the required fields. If i try to reference the content type properties i get an error because they do not exist before you have saved to SharePoint.
I have played around in a list which does not have required fields and found a solution. I firstly populate my custom doc properties and save to the SharePoint doc library. Next i iterate through my custom document properties, and if i have a corresponding content type property i copy the values across. This gets the desired end result but is not a good solution for several reasons.
1) Double saving can cause issues when versioning is turned on in the document library
2) I cannot save the initial file if the library has required fields
3) I am relying on the fact that the property i want to add exists already in the SharePoint list
4) It just seems horrible
I was wondering whether you know of any way of accessing the content type properties from VBA so that i don¢t first have to save to SharePoint. It seems that the Office.MetaPrpoerties collection is read only and i can’t find any way of adding items to it.
I would be very grateful for any assistance with this.
Thanks
Mike Sullivan, Did you ever find a solution to your Excel Load to Sharepoint with required ContentTypeProperties issue? I am trying to do the same, using VBA to load an Excel file to a Sharepoint list that has required ContentTypeProperties. I setup the “Network Place” and can use it to save to – but need to pass the required properties. Using Excel 2007 (xlsm) which is supposed to only be able to load read-only files, but that is not a problem. I’m hoping you found an answer. Deb
Hi Deb,
No solution as yet. My client is now experiencing issues with the current ‘hack’ solution we went with intially as they are using Nintex workflows on the lists and updating the files post initial save is upsetting the workflows. This has brought the issue to the fore once more so I am trying again to find a solution. I will let you know if I manage to solve it.
Great. I needed to create a purchase req that would feed details into the SP library metadata. I thought I was going to have to create a word document in order to display and edit the server properties. Being able to do it in Excel should save loads of time.
Hi John, Great code, Thanks!
I’m trying to use it to change an Expiration Date column and I keep getting an automation error saying that the file is read only (even though I open it on “Edit”). Do you know what could be causing this?
Hi great post
is there a way to dynamically populate a SP property with multiselect?
The property is an array and it seems difficult to specify array names with a string variable from VB from VB
Code
If Prop.Name = “Feeders” Then
FeederNames(0) = Sheets(1).Cells(6, 3).Value
Prop.Value = FeederNames
End If
Giving value an adress gives an error and I have tried everything really?
Thanks
I haven’t really tried this before, however are you taking each value of the multiselect and adding them to an array that you are then populating the property with?
great idea, and it seems to work in 2010 also!
Thank you!
Thank you for the post. I used it in a different way. I wanted to display the values instead of changing them.
With your help I created a function that is able to display the value of a server property.
Function DocumentProperty(Property As String)
Application.Volatile
On Error GoTo NoDocumentPropertyDefined
For Each prop In ThisWorkbook.ContentTypeProperties
If prop.Name = Property Then
DocumentProperty = prop.Value
End If
Next prop
Exit Function
NoDocumentPropertyDefined:
DocumentProperty = CVErr(xlErrValue)
End Function
After scouring the Internet, this seems to be the only way to access Sharepoint properties in Excel VBA. It works for simple types like Text, Currency, etc. but fail on object types like User. I wouldn’t mind updating the document from Excel, or Excel from the document, but in some way I want to link a Sharepoint user property with an Excel text value to the Sharepoint user.
Is there any way to accomplish this in VBA?
We’ve been trying to do this for a Sharepoint 2013 quote sheet library ourselves, but instead of using the button we’ve been using the on close command.:
Dim Sheet1 As Worksheet
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Set Info = ActiveWorkbook.Sheets(“Sheet1”)
For Each Prop In ThisWorkbook.ContentTypeProperties
If Prop.Name = “T11” Then
Prop.Value = Info.Cells(1, 1).Value
End If
If Prop.Name = “T12” Then
Prop.Value = Info.Cells(13, 8).Value
End If
If Prop.Name = “T13” Then
Prop.Value = Info.Cells(25, 18).Value
End If
Next Prop
End Sub
So this works, with a caveat, we cannot get it to work when we turn on Content Types (we’ve done tests on multiple libraries, with the same code, only difference being that Content Type management is turned on). Is there a particular VBA code that identifies the documents properties (columns) of a specific content type in sharepoint?
Thanks a lot!! Updating the SP list items when the Excel spreadsheet is edited was the last part missing! Although the problem now are the “forgetful” users who neglet to click the update button. Thought of running the script upon save in a VBAProject module but does not seem to work. Maybe I’m doing something wrong, or maybe it’s not supposed to work that way. Any ideas? Or someone who did something similar already?
You saved me big time.
Thanks a lot for sharing the same.
I have 3 X Excell files that all work together. The main document imports information from the other 2 documents. This all works perfectly. The problem starts when uploaded to MOSS. The other documents have been updated and saved.
When I click import I cannot seem to find the documents to imports. How to I put code in place so as to find and import this information?
This is a real problem. Your help will be greatly appreciated.