none
Using VBA to Import SharePoint 2013 Lists RRS feed

  • Question

  • There seems to be an issue with importing SharePoint Lists which contain drop down fields containing people.

    If you export to Excel directly the fields are there but if you use VBA to import then they are not.

    In VBA I'm using

     Set objMyList = objWksheet.ListObjects.Add(xlSrcExternal, Array(strSPServer, LISTNAME, VIEWNAME), False, , objWksheet.Range("A1"))

    The list name and view name are taken from the SharePoint - modify view - Mobile section

    Note there is a question on this forum"VBA Excel import of SharePoint list does not import the "multiple value" fields (person lookup)" which is marked as answered but it not (at all).

    Thanks for any help


    • Edited by britcam Tuesday, September 29, 2015 1:00 PM
    Tuesday, September 29, 2015 12:57 PM

Answers

  • Hi britcam,

    >> If you export to Excel directly the fields are there but if you use VBA to import then they are not.

    What do you mean with this? Did you get any error or all other fields import except drop down fields like “Person or Group”? Could you share us your complete code and screenshots about your sp list and result in excel?

    I made a test with the code below, and it import the “Person or Group” column which is drop down fields in Sharepoint site.

    Sub ImportSharePointList()
        Dim objMyList As ListObject
        Dim objWksheet As Worksheet
        Dim strSPServer As String
        Const SERVER As String = "mysite:officedevgroup.sharepoint.com/sites "
        Const LISTNAME As String = "{91AFD235-8B09-46E0-B00D-xxxxxx}"
        Const VIEWNAME As String = "{DFE71F05-C36B-4986-8F28- xxxxxx}"
       ' The SharePoint server URL pointing to
       ' the SharePoint list to import into Excel.
        strSPServer = "http://" & SERVER & "/_vti_bin"
        ' Add a new worksheet to the active workbook.
        Set objWksheet = Worksheets.Add
        ' Add a list range to the newly created worksheet
        ' and populated it with the data from the SharePoint list.
        Set objMyList = objWksheet.ListObjects.Add(xlSrcExternal, _
            Array(strSPServer, LISTNAME, VIEWNAME), False, , range("A1"))
    End Sub

    I am not sure whether your parameters are correctly, I suggest you check it whether it looks like mine, or you could get it from Definition of your Connection Properties.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Wednesday, September 30, 2015 3:35 AM