• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 403
  • Last Modified:

SharePoint Custom List


I have a sharepoint custom list we use for quotations. we create a new quotation vis the new file drop down, this is an excel template file. when we save it we have to fill out various columns/fields like customer, reference, issued, won etc. one field is amount, is it possible i can use a sharepoint formula column that will extract/import the total amount of the quotation from the excel quotation file that gets saved to the sharepoint list so its viewable in sharepoint. I have a total cell in the excel file that stays the same.

Attached is a screenshot.

Many thanks.


  • 3
1 Solution
There isn't anything out of the box that can do this.  However, you could take advantage of the Document Properties panel in Excel.  (Here is how to do that: http://excel.shilor.org/2009/08/excel-and-sharepoint-metadata.html).  Once you have it setup to use the Document Properties, you might try using VBA to populate the property (http://www.codeforexcelandoutlook.com/blog/2009/02/editing-excels-built-in-document-properties/).
Ok, so I actually have figured out a way to do exactly what you want to do.

I did the following steps:

1) Created the Excel document and saved to the SharePoint document library as a Macro Enabled Spreadsheet (so it would pick up the document properties)
2) On the Developer toolbar (which you can enable in the settings), I enabled the Document Panel (so you can see the SharePoint properties for the document)
3) On the Developer toolbar, I clicked Insert > ActiveX Button
4) I double clicked to edit the VBA behind the button
5) I added the following code (the Private Sub UpdateButton_Click() was already there):

Private Sub UpdateButton_Click()
Dim Book As Workbook
Set Book = ThisWorkbook
For Each Prop In Book.ContentTypeProperties
If Prop.Name = "TestData" Then
Prop.Value = Cells(7, 1).Value
End If
Next Prop
End Sub

My Document Property that I was setting was "TestData".  Change that to your SharePoint property.  Cells(7,1) refers to A7.  Change that to match the cell that has your data (7 = the row, 1 = the column).  

When you hit the button, it updates the property and you can save it.
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now