Ensure connection has worked

I'm working on a VBA code to get data fro a Sharepoint list, and update it directly from my Excel file.

My code is actually working, and here it is :

Option Explicit


Dim HomeSh As Worksheet


Sub ImportList()

    Set HomeSh = Sheets("Feuil2")
    
    Dim SpSite As String
    SpSite = "https://mybestwebsiste"
    
    Dim Guid As String
    Guid = "{51748b98}"
    
    On Error Resume Next
    HomeSh.ListObjects(1).Unlist
    On Error GoTo 0
    
    HomeSh.Range("A1:X10000").Clear
    
    Dim SiteSrc(0 To 1) As Variant
    
    SiteSrc(0) = SpSite & "/_vti_bin"
    SiteSrc(1) = Guid
    
    HomeSh.ListObjects.Add xlSrcExternal, SiteSrc, True, xlYes, HomeSh.Range("A1")
    
    Set HomeSh = Nothing
    
End Sub


Sub UpdateSPList()

    Dim theList As Object
    
    Set HomeSh = Sheets("Feuil2")
    Set theList = HomeSh.ListObjects(1)
    
    theList.UpdateChanges xlListConflictDialog
    
    Set theList = Nothing
    
End Sub

When I checked to integrate it in another file / macro, something doesn't work well : The editor doesn't wait for the connection to be updated and goes. The problem is : I need this data updated to have a nice execution of my code..

I was looking on internet at some code samples to wait for the connection to be updated, and doesn't find the right solution. Here is what i tried :

• 1 :

  Application.CalculateUntilAsyncQueriesDone

-2 :

HomeSh.ListObjects.Add xlSrcExternal, SiteSrc, True, xlYes, HomeSh.Range("A1")
DoEvents

Should it be those lines which gave me some problems ?

Application.DisplayAlerts = False
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

I saw that an expression like this should help but don't know ho to integrate it in my code..

While http.readyState <> 4
DoEvents

Thank you for your help !

I've found it's more flexible to manage this type of task using ADO instead of using workbook queries. It lets you retrieve information without needing to place it on a worksheet.

For example, you can use the code below to query data from a SharePoint List (or a file listing from a SP Library)

Similar code also works for list updates.

'Use ADO to query a recordset from a SharePoint List
'Needs a VBA Project reference to "Microsoft ActiveX Data Objects x.x Library"
Sub SPListQuery()
    'Parent Site URL for the List/Library
    Const SiteUrl As String = "https://contoso.sharepoint.com/sites/ABCSite/"
    'Name or GUID for the List or Library to be queried
    Const ListName As String = "{aaaaaaa-zzzz-bbbbb-yyyy-zzzzzzzzz}" 
    
    Dim Conn As New ADODB.Connection, rs As ADODB.Recordset, sql As String
    
    With Conn ' Open the connection
        .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;WSS;IMEX=2;RetrieveIds=Yes;" & _
                             "DATABASE=" & SiteUrl & ";" & _
                             "LIST=" & ListName & ";"
        .Open
    End With
    
    'query all records for display
    sql = "SELECT * FROM [" & ListName & "]"
    Set rs = New ADODB.Recordset
    rs.Open sql, Conn, adOpenStatic
    
    With ThisWorkbook.Worksheets("Results")
        .Cells.Clear 'clear any previous data
        RecordsetToWorksheet rs, .Range("A1")
    End With

    rs.Close
    Conn.Close
End Sub
    
'Copy data from a recordset `rs` to a worksheet, starting at `StartCell`
Sub RecordsetToWorksheet(rs As ADODB.Recordset, StartCell As Range)
    Dim f As ADODB.Field, i As Long
    For Each f In rs.Fields
            Debug.Print f.Name, f.Type
            StartCell.Offset(0, i).Value = f.Name
            i = i + 1
        Next f
    If Not rs.EOF Then StartCell.Offset(1).CopyFromRecordset rs
End Sub