Solved

SharePoint Custom List

Posted on 2010-09-07
5
399 Views
Last Modified: 2012-06-21
Hi

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.

Dan

sharepointquote.JPG
0
Comment
Question by:DHTS
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 21

Expert Comment

by:chapmanjw
ID: 33621936
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/).
0
 
LVL 21

Expert Comment

by:chapmanjw
ID: 33622421
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.
0
 
LVL 21

Accepted Solution

by:
chapmanjw earned 500 total points
ID: 33622711
0
 
LVL 17

Expert Comment

by:GreatGerm
ID: 36123444
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In case you ever have to remove a faulty web part from a page , add the following to the end of the page url ?contents=1
A recent project that involved parsing Tableau Desktop and Server log files to extract reusable user queries for use in other systems. I chose to use PowerShell to gather the data, and SharePoint to present it...
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question