none
VBA Excel import of Sharepoint list does not import the "multiple value" fields RRS feed

  • Question

  •  I would like to create an excel vba code to upload a Sharepoint list into Excel for further manipulation.

    I have tried to do it through listobjects.add command like this

    Dim objWksheet As Worksheet
        Dim strSPServer As String
        Dim noOfCols As Integer, noOfRows As Integer
        
        Const SERVER As String = "server name"
        
        Const LISTNAME As String = "{strListGUID}"
       
        Const VIEWNAME As String = "{strViewGUID}"
        strSPServer = "https://" & SERVER & "/_vti_bin"
        Debug.Print strSPServer
        
       
        Set objWksheet = ActiveSheet
       Set objWksheet = ActiveWorkbook.Sheets("Sheet1")
    
       Application.StatusBar = "Clearing Contents"
        objWksheet.Cells.Clear
        Application.StatusBar = "Importing data from Share Point.........."
        Set objMyList = objWksheet.ListObjects.Add(xlSrcExternal, _
        Array(strSPServer, LISTNAME, VIEWNAME), True, xlGuess, objWksheet.Range("A1"))
    Application.StatusBar = ""
    MsgBox "Refresh Done"
    End Sub

    It works great, except that one column is missing in the excel tables created like this.

    This missing column is of type 'Lookup' in Sharepoint .

    It looks like that this column is not imported at all (no error message).

    Strangely, if I click on 'Export to Excel' feature in SharePoint, the ".iqy" file create properly import that column.

    Is there any way to get those 'multiple value' filed be properly imported in VBA?

    Thanks in advance

    Mine




    • Edited by mine1981 Friday, February 17, 2017 6:13 AM
    Friday, February 17, 2017 6:12 AM

All replies

  • Hi,

    One workaround is get SharePoint listitems by REST call and then populate the excel file.

    Here is the sample code for calling REST api for your reference.

     

    Sub SendEmail()
        Const sUrl As String = "http://sp:12001/_api/Web/Lists/getByTitle('Parent')/items"
        Dim oRequest As WinHttp.WinHttpRequest
        Dim sResult As String
        
        Set oRequest = New WinHttp.WinHttpRequest
    With oRequest
        .Open "GET", sUrl, True
        .setRequestHeader "Content-Type", "application/json"
        .SetCredentials "domain\administrator", "pw", HTTPREQUEST_SETCREDENTIALS_FOR_SERVER
        .send
        .waitForResponse
        sResult = .responseText
        Debug.Print sResult
        sResult = oRequest.Status
        Debug.Print sResult
    End With
    End Sub

    For processing httpresopnse returned by rest call, I would suggest you post issues(if any) in Visual Basic for Applications (VBA) forum.

    https://social.msdn.microsoft.com/Forums/office/en-US/home?forum=isvvba

    Best Regards,

    Lee


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com

    Monday, February 20, 2017 7:03 AM
  • Hi,

    Could you provide any feedback for your issue?

    Best Regards,

    Lee


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com

    Wednesday, February 22, 2017 9:50 AM