- Create a SharePoint Document Library and create the columns you want filled in by Excel;
- I created for this example the columns: Date, Amount and Total
- Upload a empty Excel Document with Macro enabled to the SharePoint Library
- Open the Excel document from your SharePoint site and add the Developer toolbar to Excel
- In my example I will add the data to columns: Date B5 (2,5), Amount (2,6), Total (2,7)
- Click on the developer toolbar and Insert and select the Active X button
- Now select the button and right click (Properties)
- Change the value Name to UpdateButtom and Caption to Update SharePoint Properties. Close the window
- Double click the button to open the VB Editor.
- Add the following code:
Private Sub UpdateButtom_Click()
For Each Prop In ThisWorkbook.ContentTypeProperties
If Prop.Name = "Date" Then
Prop.Value = Cells(2, 5).Value
End If
Next Prop
End Sub
- For every other cell just add after Next Prop and before End Sub again the code starting with If Prop.Name so for my sheet the code would be:
Private Sub UpdateButtom_Click()
For Each Prop In ThisWorkbook.ContentTypeProperties
If Prop.Name = "Date" Then
Prop.Value = Cells(2, 5).Value
End If
If Prop.Name = "Amount" Then
Prop.Value = Cells(2, 6).Value
End If
If Prop.Name = "Total" Then
Prop.Value = Cells(2, 7).Value
End If
Next Prop
End Sub
- Now save the Excel as Macro Enabled as Template and use it in SharePoint as Content Type. When you open the Excel and fill in the properties and you click the button the Properties will be copied to SharePoint.
