Update Links In For Excel Spreadsheet Using Python

I am running simulations in Python that generate output that need to be directly consumed by a modeler in their excel workbooks. I have generated code that will directly output my

Solution 1:

Consider having Python directly run the method UpdateLink with the COM objects you initialize, namely the xl_app and wb objects. No need to build a macro in each workbook and then call it.

Below UpdateLink() is wrapped in a try/except/finally block in case workbook has no links as LinkSources will return an Empty value, raising a COM exception, the very error you receive:

run-time error '1004' method 'updatelink' method of object '_workbook' failed

Also be sure to uninitialize objects (a good best practice in VBA too: Set wb = Nothing) after use to free CPU resources else they remain as background processes until garbage collection.

defrun_macro(workbook_name, com_instance):
    wb = com_instance.workbooks.open(workbook_name)
    com_instance.AskToUpdateLinks = Falsetry:
       wb.UpdateLink(Name=wb.LinkSources())

    except Exception as e:
       print(e)

   finally:
       wb.Close(True)
       wb = NonereturnTruedefmain():
    dir_root  = ("C:\\Model_Spreadsheets")

    xl_app = Dispatch("Excel.Application")
    xl_app.Visible = False
    xl_app.DisplayAlerts = Falsefor root, dirs, files in os.walk(dir_root):
        for fn in files:
            if fn.endswith(".xlsx") and fn[0] isnot"~":
                run_macro(os.path.join(root, fn), xl_app)
    xl_app.Quit()
    xl = None

Aside - though VBA ships by default with Excel and MS Office applications, it is actually a separate component. To check, under Tools \ References in VBA IDE, you will see VBA is the first checked item, nothing built-in. In fact, VBA does exactly what you are doing in Python: making a COM interface to the Excel Object Library. So in a sense VBA is just as related to Excel and Python is!