I am trying to update the external data link for a chart in Excel using python. The chart sits in workbook1.xlsm
and the data it references to update itself sits in external_workbook.xlsx
. The reason for the separation is the data has to be updated in workbook1.xlsm
periodically using python, which erases the chart if it's in workbook1.xlsm
.
I've looked at various solutions but none are working for me so far. The two solutions I've tried so far include (1) refreshing the workbook programmatically and (2) running a macro in the workbook to refresh it programmatically.
Code for (1):
import win32com.client as w3c
xlapp = w3c.gencache.EnsureDispatch('Excel.Application')
xlapp.Visible = 0
xlwb = xlapp.Workbooks.Open(r'{}\{}'.format(path, fname), False, True, None)
xlwb.RefreshAll() # Runs with no errors, but doesn't refresh
time.sleep(5)
xlwb.Save()
xlapp.Quit()
Code for (2):
# ***************** #
# Excel macro - I've verified the macro works when I have the worksheet open.
Sub Update_Links()
ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources
End Sub
# ***************** #
import win32com.client as w3c
xlapp = w3c.gencache.EnsureDispatch('Excel.Application')
xlapp.Visible = 0
xlwb = xlapp.Workbooks.Open(r'{}\{}'.format(path, fname), False, True, None)
xlwb.Application.Run("{}!Module1.Update_Links".format(fname)) # Runs with no errors, but doesn't refresh
xlwb.Save()
xlapp.Quit()
The series for my chart in Excel is
# External data link for Excel chart #
=SERIES(,'...path_to_external_file...[external_workbook.xlsx]Sheet1'!$A$2:$A$2000,
'...path_to_external_file...[external_workbook.xlsx]Sheet1'!$F$2:$F$2000,1)
Could anyone provide me with an alternative solution of how to make this work?
EDIT
So I tried something simpler to test this. I created a new sheet called temp
in workbook1.xlsm
and tried to write a random value to cell A1 using the code below. The temp sheet is still blank after running the code.
import win32com.client as w3c
import random
xlapp = w3c.gencache.EnsureDispatch('Excel.Application')
xlapp.Visible = 0
xlwb = xlapp.Workbooks.Open(r'{}\{}'.format(path, fname), False, True, None)
books = w3c.Dispatch(xlwb)
sheet_temp = books.Sheets('temp')
sheet_temp.Cells(1,1).Value = random.random()
xlwb.RefreshAll() # Runs with no errors, but doesn't refresh
time.sleep(5)
xlwb.Save()
xlapp.Quit()
I get no errors with the code and am following examples other people have posted online. Could someone point me to where I'm going wrong with this?
The answer is I needed to open the workbook the external_workbook.xlsx
prior to updating the workbook1.xlsm
, so the data could be refreshed.
The working code is as follows:
import win32com.client as w3c
import random
xlapp = w3c.gencache.EnsureDispatch('Excel.Application')
xlapp.Visible = 0
# ********************************* #
# New line that fixes it #
xlwb_data = xlapp.Workbooks.Open(r'{}\{}'.format(path, 'external_workbook.xlsx'), False, True, None)
# ********************************* #
xlwb = xlapp.Workbooks.Open(r'{}\{}'.format(path, 'workbook1.xlsm'), False, True, None)
books = w3c.Dispatch(xlwb)
sheet_temp = books.Sheets('temp')
sheet_temp.Cells(1,1).Value = random.random()
xlwb.RefreshAll() # Runs with no errors, but doesn't refresh
time.sleep(5)
xlwb.Save()
xlapp.Quit()