Refresh Data Queries in Excel workbook - Python and VBA

Below code snippets written in Python and VBA as well, to refresh Excel Workbook Data Queries (Connections) whether For Table or Pivots Table or Charts:

---

in Python

import win32com.client as win32
import time

Files = ['C:\\Folder\\File1.xlsx', 'C:\\Folder\\File2.xlsx', 'C:\\Folder\\File3.xlsx']


def RefreshFiles(Files):
    UpdatedFiles = []

    xl = win32.DispatchEx("Excel.Application")
    xl.visible = True
    for filename in Files:
        try:
            WB = xl.workbooks.open(filename)
            for con in WB.connections:
                con.refresh
            WB.Close(True)

            UpdatedFiles.append(filename)
            time.sleep(1)
        except:
            print "File Not Updated : " + filename

    xl.Quit()
    return UpdatedFiles;

in VBA

Option Explicit

Public Sub GetFiles()
    Dim Files
    
    Files = Application.GetOpenFilename("Micrsoft excel workbooks (*.xls*),(*.xls*)", MultiSelect:=True)
    
    If IsArray(Files) Then
        RefreshAll Files
    End If
End Sub

Private Sub RefreshAll(Files As Variant)
Dim XL As Excel.Application, WB As Excel.Workbook
Dim con As Variant, File As Variant

Set XL = New Excel.Application

For Each File In Files
    Set WB = XL.Workbooks.Open(File)
    For Each con In WB.Connections
        con.Refresh
    Next

    WB.Close True
Next

XL.Quit

End Sub