3

I am using the following code to refresh data in excel file which uses external add in for receiving data.

import sys, os, pandas as pd, numpy as np, time, win32com.client
import win32com.client as w3c

if __name__ == '__main__':
    your_file_path = r'C:\Book11.xlsx'
    for ii in np.arange(1, 10):
        xlapp = w3c.gencache.EnsureDispatch('Excel.Application')
        xlapp.Visible = 0
        xlwb = xlapp.Workbooks.Open(your_file_path, False, True, None)
        books = w3c.Dispatch(xlwb)

        xlwb.RefreshAll()  # Runs with no errors, but doesn't refresh
        xlapp.DisplayAlerts = False
        xlwb.Save()
        xlapp.Quit()

        df = pd.read_excel(your_file_path)  # updates should be applied
        print(df)
        time.sleep(20)

# Another version of code that I tried is following:
# xlapp = win32com.client.DispatchEx("Excel.Application")
# xlapp.Visible = True
# wb = xlapp.Workbooks.Open(your_file_path)
# wb.RefreshAll()
# xlapp.CalculateUntilAsyncQueriesDone()
# xlapp.DisplayAlerts = False
# wb.Save()
# xlapp.Quit()

However, the file doesn't refresh. In fact it looks like the following:

enter image description here

On the other hand if I just open the file on desktop using mouse clicks, I see the data as expected.

enter image description here

Zanam
  • 4,607
  • 13
  • 67
  • 143

1 Answers1

0

  1. Are you running this as a macro?

  2. Is refresh in bg property is false for all connections?

Things to try:

a) Calculate ActiveWorkbook.RefreshAll instead of wbRefresh.RefreshAll

b) Unchecking "enable background refresh" (uncheck to disable the background refresh)

tariksalay
  • 367
  • 1
  • 2
  • 9
  • No this doesnt work. I need a code which can be replicated so that I can accept you answer and award the bounty. – Zanam Jul 11 '20 at 16:59