Solved

SharePoint Custom List

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Last week I faced a strange issue recently, i have deployed SharePoint 2003 servers for one project and one of the requirements was to open SharePoint site from same server. when i was trying to open site from the same server i was getting authentic…
I recently came across an issue with a MOSS 2007 deployment where access into some sub-sites were denied, even for the MOSS farm administrators. A bit of background to the setup of this MOSS farm; this was a three server setup, consisting of a fr…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…

864 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now