SharePoint and Excel Properties step by step

In this blog I will describe how to create an Excel and publish data in SharePoint columns by a simple VB Script. This example is created in SharePoint 2013 and Excel 2013.

- 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

- Now you need to make a note where you want to connect  the data. You need to count the cells. For Example if you want to place the data in A1 you count 1,1 or B1 you count 2,1 or C2 you count 3,2.

- 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.