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
