def main(): FILE_PATH = sys.argv[1] assert os.path.exists(FILE_PATH) # Use Excel 2016 library; change/remove if not using 2016 gencache.EnsureModule('{00020813-0000-0000-C000-000000000046}', 0, 1, 9) # Create instance of Excel xl = DispatchEx('Excel.Application') # Set application properties xl.Visible = 0 xl.DisplayAlerts = False # Create workbook object workbook = xl.Workbooks.Open(FILE_PATH) # Loop to see if workbook is ready to proceed workbook_ready = False tries = 0 while not workbook_ready: try: workbook.Activate() except Exception: sleep(1) tries += 1 if tries == 10: break else: workbook_ready = True """For silent refresh operations, use the FastCombine property in conjunction with the Application.DisplayAlerts property, set to False""" workbook.Queries.FastCombine = True # Common connection types; add more if needed connection_types = { '1_ODBC': 2, '2_OLEDB': 1, '3_MODEL': 7, '4_WORKSHEET': 8 } # Refresh connections in a specific order for connection_type in sorted(connection_types.keys()): refresh_connection(excel=xl, workbook=workbook, connection_type=connection_types[connection_type]) # Disable events while caches refresh xl.EnableEvents = False refresh_pivot_caches(excel=xl, workbook=workbook) xl.EnableEvents = True # Calculate any necessary calculations in the workbook xl.Calculate() # Close workbook object workbook.Close(True) # Close Excel instance xl.Quit() # Remove Excel instance del xl