I am an accountant and very new to python...
I am trying to write a python script that will open an xlsm file, refresh external data links (connection to HFM Smartview), then save and close the file. I have already written a draft of the code that runs as I would like by simply writing the required data refresh operations in VBA, then triggering those macros using python.
My problem is that I cannot find a way to run this code in the background so that the xlsm file does not open visibly on my desktop while I am doing other work. The issue is that any time I open the xlsm using xlwings, the external links break as seen below. If I use os.startfile to open the xlsm, everything runs fine -- but as far as I understand, there is no option using os.startfile to set visible = False.
The text highlighted yellow should not be included in the formula to retreive the data. I have also tried to do a find and replace to remove this extra text in all the file, but the connection is still broken.
Any idea if I can ajust the python script or some setting in excel to prevent the links from breaking in this way when opening with xlwings? If I could avoid this issue and open with xlwings, I am aware that I could simply set visible = False and problem would be solved.
import xlwings as xw
import os
os.startfile(bs_close)
wb = xw.Book(bs_close)
wks = xw.sheets
#map macros saved in XLSM file
connect = wb.macro("Module2.SmartView_Connect")
refresh = wb.macro("Module3.RefreshAll")
connect() # run macro saved in xlsm file
refresh() # run macro saved in xlsm file
wb.save()
wb.app.quit()
