使用Python更新Excel电子表格中的链接 [英] Update Links in for Excel Spreadsheet Using Python

问题描述

我正在Python中运行模拟，生成的输出需要由建模人员直接在其Excel工作簿中使用.我已经生成了将代码直接输出到他们的excel电子表格模板的代码.我生成的将数据直接输出到其模板的代码很好，但是我遇到的问题是建模器具有一系列链接"在一起的工作簿.如果我将数据插入他们的电子表格，则该工作簿的链接不会更新，除非用户实际打开该工作簿的编辑链接"->更新值".如果只有一个工作簿，则用户可以轻松打开工作簿.实际上，将有超过100个工作簿需要更新链接.不幸的是，在链接工作簿时，我无能为力改变建模者的方法-我唯一能做的就是适应他们的方法.

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 data into their excel spreadsheet template. The code I have generated to output the data directly to their template is fine, but the problem I am running into is that the modeler has a series of workbooks that are "linked" together. If I insert my data into their spreadsheet, the links to that workbook do no update unless the user physically opens the workbook to "Edit Links" -> "Update Values". If there was one workbook, then the user can simply open the workbook with no problem. In reality, there will be over 100 workbooks that need the links updated. Unfortunately, there is nothing I can do to change the modeler's approach in linking workbooks -- the only thing I can do is accommodate their approach.

我的目标是创建一个Python解决方案，该解决方案将允许我1)生成模拟数据，2)将生成的数据插入建模者的工作簿中，以及3)更新工作簿之间的所有链接.最终，为了简化程序，我希望能够在一个端到端的python程序中完成所有这三个操作.我已经解决了(1)和(2)，并且我有解决方案(3)几乎可以解决.我生成了以下功能脚本:

My goal is to create a Python solution that will allow me to 1) Generate the simulated Data, 2) Insert my generated data into the modeler's workbook, and 3) Update all of the links between workbooks. Ultimately, in order to be streamlined, I want to be able to do all three in one end-to-end python program. I have solved (1) and (2), and I have a solution for (3) that almost works. I have generated the following functional script:

from win32com.client import Dispatch
import pandas as pd
from openpyxl import load_workbook
import os
import time

def run_macro(workbook_name, vba_sub, com_instance):
    wb = com_instance.workbooks.open(workbook_name)
    wb.RefreshAll()
    xl_module = wb.VBProject.VBComponents.Add(1)
    xl_module.CodeModule.AddFromString(vba_sub.strip())
    com_instance.Application.Run('UpdateLinkValues')
    wb.Save()
    wb.Close()

    return True

def main():
    dir_root  = ("C:\\Model_Spreadsheets")

    vba_sub = \
        '''
        sub UpdateLinkValues()
            Application.AskToUpdateLinks = False
            ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources
        end sub
        '''

    xl_app = Dispatch("Excel.Application")
    xl_app.Visible = False
    xl_app.DisplayAlerts = False

    for root, dirs, files in os.walk(dir_root):
        for fn in files:
            if fn.endswith(".xlsx") and fn[0] is not "~":
                run_macro(os.path.join(root, fn), vba_sub, xl_app)
    xl_app.Quit()


if __name__ == "__main__":
    main()

此脚本确实很接近我要寻找的正确解决方案，但是我遇到了一个看似随机"的VBA错误:

This script is really close to the correct solution I am looking for, but I run into a VBA error seemingly 'randomly':

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

每次尝试运行此脚本时都会出现此错误，但是不会在同一工作簿上每次都发生-有时，它会在第一个工作簿上发生，有时在第15个工作簿上，等等...

This error does appear each time I try to run this script, but it does not occur for the same workbook each time -- sometimes, it occurs on the first workbook, sometimes on the 15th, etc...

我可以选择在VBA中进行调试，并且可以继续进入下一个工作簿的唯一方法是将宏更改为

I have an option to debug in VBA, and the only way that I can continue on to the next workbook is if I change the macro to

sub UpdateLinkValues()
    Application.AskToUpdateLinks = False
end sub

如果我运行此宏并退出调试，程序将继续运行，直到再次遇到相同的错误.我的第一个想法是，在我打开工作簿与尝试运行宏之间可能存在时间问题.我发现一种解决方法是可以更改宏和应用程序的可见性:

if I run this macro and exit debug, the program will continue to run until it encounters the same error again. My first thought was that maybe there is a timing issue between me opening the workbook and trying to run the macro. A workaround that I have found is that I can change the macro and the app visibility:

vba_sub = \
    '''
    sub UpdateLinkValues()
        Application.AskToUpdateLinks = False
    end sub
    '''

和

xl_app.Visible = True

这很好，但是我不喜欢打开和关闭每个工作簿，因为这需要很长时间.我的问题是，有人知道为什么会出现此运行时错误吗?或者，也许有人知道如何在Python中拦截此运行时错误作为例外吗?如果我可以将此错误作为python的例外进行拦截，那么我可以将替代解决方案用于这些特定的工作簿.

This works fine, but I am not a fan of having each of the workbooks open and close because it takes a long time. My question is, does anyone know why this run-time error is coming up -- with a solution? Or perhaps, does anyone know how to intercept this run-time error in Python as an exception? If I can intercept this error as an exception in python, then I could use my alternative solution for those particulars workbooks.

提前谢谢！

推荐答案

考虑让Python直接运行方法

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.

在UpdateLink()下面包裹在try/except/finally块中，以防工作簿没有 LinkSources 将返回一个 Empty 值，引发COM异常，这是您收到的错误消息:

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:

对象'_workbook'的运行时错误'1004'方法'updatelink'方法 失败

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

在释放CPU资源后，还请确保取消初始化对象(在VBA中也是一种最佳做法:Set wb = Nothing)，否则它们将保留为后台进程，直到进行垃圾回收为止.

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.

def run_macro(workbook_name, com_instance):
    wb = com_instance.workbooks.open(workbook_name)
    com_instance.AskToUpdateLinks = False
    try:
       wb.UpdateLink(Name=wb.LinkSources())

    except Exception as e:
       print(e)

   finally:
       wb.Close(True)
       wb = None    
    return True

def main():
    dir_root  = ("C:\\Model_Spreadsheets")

    xl_app = Dispatch("Excel.Application")
    xl_app.Visible = False
    xl_app.DisplayAlerts = False

    for root, dirs, files in os.walk(dir_root):
        for fn in files:
            if fn.endswith(".xlsx") and fn[0] is not "~":
                run_macro(os.path.join(root, fn), xl_app)
    xl_app.Quit()
    xl = None

除了-尽管默认情况下VBA随Excel和MS Office应用程序一起提供，但实际上它是一个单独的组件.要进行检查，请在VBA IDE中的工具\引用"下，您将看到VBA是第一个选中的项目，没有内置内容.实际上，VBA确实可以完成您在Python中所做的事情:为Excel对象库创建COM接口.因此，从某种意义上说，VBA与Excel和Python息息相关！

这篇关于使用Python更新Excel电子表格中的链接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！