Solved

SharePoint Custom List

Posted on 2010-09-07
5
397 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Work Over Net is a new and very powerful collaboration product. With its new easy interface it is becoming very competitive to other similar products like webex and office interactive. WON 2010 have the standard business tools needed for multi-offic…
SharePoint Designer 2010 has tools and commands to do everything that can be done with web parts in the browser, and then some – except uploading a web part straight into a page that is edited in SPD. So, can it be done? Scenario For a recent pr…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

756 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