CODEWITHSUNDEEP
excelvbaexcel-2010
Diego Pulita
Diego Pulita

Reputation: 1

Update connections in Excel 2010 via VBA, one connection at a time

Good morning!

I have a spreadsheet, created on 365 but some users who would need to use it have Excel 2010 I have some connections that need to be updated separately, I cannot use Refreshall.

On the 365, I use:

    SpreadsheetOrigem.ListObjects("OCs").Refresh
    SpreadsheetOrigem.ListObjects("Stock").Refresh
    SpreadsheetOrigem.ListObjects("Demands").Refresh

But in Excel 2010, this code doesn't work.

I tried:

    ActiveWorkbook.Connections("OCs").Refresh
    ActiveWorkbook.Connections("Stock").Refresh
    ActiveWorkbook.Connections("Demands").Refresh

And the strange thing is that the first connection updates normally, but the other connections don't update. It seems that the update is "pending" and does not accept others. Even with the background turned off.

I appreciate any help.

Help in VBA to work in various versions.

Upvotes: 0

Views: 28

Answers (1)

ceci
ceci

Reputation: 784

You could try to call below. Without argument it'll refresh all connections in the wb. If you give an argment make sure you have the exact connection name. Uncoment the bckgr statement at the end if you want to reanable bckgr refresh. The msgbx at the end is optional so you can remove it or tailor to your need.

    Sub Refresh_All_Data_Connections(Optional TableToRefresh As String)
    On Error GoTo ErrorHandler
    Dim TableToRefresh As String
        Dim objConnection, bBackground
            For Each objConnection In ThisWorkbook.Connections
                'Get current background-refresh value
                bBackground = objConnection.OLEDBConnection.BackgroundQuery
                'Temporarily disable background-refresh
                objConnection.OLEDBConnection.BackgroundQuery = False
                
                If TableToRefresh = "" Then
                    'Refresh All connections
                    objConnection.Refresh
                ElseIf TableToRefresh = objConnection Then
                    'Refresh specific connection
                    objConnection.Refresh
                End If
        
                'Set background-refresh value back to original value
    '            objConnection.OLEDBConnection.BackgroundQuery = bBackground
            Next
        Exit Sub
    ErrorHandler:
    Debug.Print objConnection.Name
    If objConnection.Name <> "ThisWorkbookDataModel" Then
        MsgBox "The query: " & objConnection.Name & " could not refresh. Did you save the response Excel's in the response folder?", vbCritical
        End
    End If
    End Sub

Upvotes: 0

Related Questions