Search code examples
vbaexcelsharepointsharepoint-2010

VBA Export SharePoint Items Data to Excel Spreadsheet

Recently I am trying to export the data within each list item on SharePoint to Excel using VBA. Below is the list.

enter image description here

And if you click on each one it looks like:

enter image description here

So the data I crossed out are the values that I want them to be exported to an Excel Spreadsheet.

I know how to export the whole list of to excel, the code is:

Sub ImportSharePointData()

    Dim objMyList As ListObject
    Dim objWksheet As Worksheet
    Const SPServer As String = "http://ln-   sharepoint.XXXintra.net/XXX/XXX/XXX/_vti_bin"
    Const LISTNAME As String = "{06AAB69F-XXX-XXX-XXX-8F677FE38D76}"
    Const VIEWNAME As String = "Agreement"
    ActiveSheet.ListObjects.Add SourceType:=xlSrcExternal, Source:= _
    Array(SPServer, VIEWNAME, LISTNAME), LinkSource:=True, Destination:=Range("A1")

End Sub

Now I want data in each individual item in this list e.g. Item with ID 367, exporting the field of Counterparty Name, CSID etc to excel.

Also, it looks like these data are filled in by users into forms on SharePoint that are designed using InfoPath.

Is there anyway that I can do this without using the designer level of access?

Thank you very much in advance for any solution or advice.

Solution

  • As long as the desired fields on the InfoPath form are directly associated with SharePoint columns, the data will be available to display and export.

    Notice that you specify which view you want when you export the list to Excel.

    Const VIEWNAME As String = "Agreement"

    With this in mind, simply create a new public view of the list that shows all the columns of data that you want to export. Then export that list view instead.