Tech or Treat! Write an article about your scariest tech disaster to win gadgets!Learn more

x
?
Solved

SharePoint Custom List

Posted on 2010-09-07
5
Medium Priority
?
401 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 2000 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

Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

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

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 Sh…
There is one common problem that all we SharePoint developers share: custom solution deployment. This topic can't be covered fully in this short article, so all I want to do in this one is to review it from a development-to-operations perspectiv…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

647 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