Skip to main content
 首页 » 编程设计

python之使用 Python 更新 Excel 电子表格中的链接

2025年02月15日6傻小

我在 Python 中运行模拟,生成的输出需要由建模人员在他们的 Excel 工作簿中直接使用。我已经生成了代码,可以将我的数据直接输出到他们的 Excel 电子表格模板中。我生成的将数据直接输出到他们的模板的代码很好,但我遇到的问题是建模器有一系列“链接”在一起的工作簿。如果我将我的数据插入到他们的电子表格中,该工作簿的链接不会更新,除非用户实际打开工作簿以“编辑链接”->“更新值”。如果只有一个工作簿,那么用户可以毫无问题地简单地打开工作簿。实际上,将有 100 多个工作簿需要更新链接。不幸的是,我无法改变建模者链接工作簿的方法——我唯一能做的就是适应他们的方法。

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

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 错误:

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

每次我尝试运行此脚本时都会出现此错误,但不会每次都出现在同一个工作簿上——有时,它会出现在第一个工作簿上,有时会出现在第 15 个工作簿上,依此类推...

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

sub UpdateLinkValues() 
    Application.AskToUpdateLinks = False 
end sub 

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

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

和

xl_app.Visible = True 

这很好用,但我不喜欢打开和关闭每个工作簿,因为这需要很长时间。我的问题是,有谁知道为什么会出现这个运行时错误——有解决方案吗?或者,有谁知道如何将 Python 中的这个运行时错误作为异常拦截?如果我可以将此错误作为 python 中的异常进行拦截,那么我可以对这些细节工作簿使用我的替代解决方案。

提前致谢!

请您参考如下方法:

考虑让 Python 直接运行方法 UpdateLink使用您初始化的 COM 对象,即 xl_app 和 wb 对象。无需在每个工作簿中构建一个宏然后调用它。

UpdateLink() 下方包含在 try/except/finally block 中,以防工作簿没有链接,如 LinkSources将返回一个 Empty 值,引发 COM 异常,即您收到的错误:

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

同时确保在使用后取消初始化对象(在 VBA 中也是一个很好的最佳实践:Set wb = Nothing)以释放 CPU 资源,否则它们将作为后台进程保留直到垃圾回收。

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 接口(interface)。所以在某种意义上,VBA 与 Excel 和 Python 是一样的!