with - trabajar excel en python

Actualizar enlaces en hoja de cálculo de Excel con Python

Question

Estoy ejecutando simulaciones en Python que generan resultados que deben ser consumidos directamente por un modelador en sus libros de trabajo de Excel. He generado código que generará directamente mis datos en su plantilla de hoja de cálculo de Excel. El código que he generado para generar los datos directamente a su plantilla está bien, pero el problema con el que me estoy encontrando es que el modelador tiene una serie de libros de trabajo que están "vinculados" entre sí. Si inserto mis datos en su hoja de cálculo, los enlaces a ese libro de trabajo no se actualizan a menos que el usuario abra físicamente el libro de trabajo para "Editar enlaces" -> "Actualizar valores". Si había un libro de trabajo, entonces el usuario simplemente puede abrir el libro de trabajo sin ningún problema. En realidad, habrá más de 100 libros de trabajo que necesitan que los enlaces estén actualizados. Lamentablemente, no hay nada que pueda hacer para cambiar el enfoque del modelador al vincular los libros de trabajo: lo único que puedo hacer es adaptar su enfoque.

Mi objetivo es crear una solución de Python que me permita 1) Generar los datos simulados, 2) Insertar mis datos generados en el libro de trabajo del modelador, y 3) Actualizar todos los enlaces entre los libros de trabajo. En última instancia, para ser optimizado, quiero poder hacer los tres en un programa de punta a punta de python. He resuelto (1) y (2), y tengo una solución para (3) que casi funciona. He generado el siguiente script funcional:

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()

Este script está realmente cerca de la solución correcta que estoy buscando, pero me encuentro con un error de VBA aparentemente ''aleatoriamente'':

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

Este error aparece cada vez que intento ejecutar este script, pero no ocurre para el mismo libro de trabajo cada vez; a veces, aparece en el primer libro de trabajo, a veces el día 15, etc.

Tengo una opción para depurar en VBA, y la única forma en que puedo continuar con el próximo libro de trabajo es si cambio la macro a

sub UpdateLinkValues()
    Application.AskToUpdateLinks = False
end sub

si ejecuto esta macro y salgo de la depuración, el programa continuará ejecutándose hasta que vuelva a encontrar el mismo error. Lo primero que pensé fue que tal vez haya un problema de tiempo entre la apertura del libro de trabajo y el intento de ejecutar la macro. Una solución alternativa que he encontrado es que puedo cambiar la macro y la visibilidad de la aplicación:

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

y

xl_app.Visible = True

Esto funciona bien, pero no soy partidario de tener todos los libros de trabajo abiertos y cerrados porque lleva mucho tiempo. Mi pregunta es, ¿alguien sabe por qué está surgiendo este error en tiempo de ejecución, con una solución? O quizás, ¿alguien sabe cómo interceptar este error en tiempo de ejecución en Python como una excepción? Si puedo interceptar este error como una excepción en Python, entonces podría usar mi solución alternativa para esos libros de datos particulares.

¡Gracias por adelantado!

Answer 1

Considere que Python ejecute directamente el método UpdateLink con los objetos COM que inicializa, es decir, los objetos xl_app y wb . No es necesario crear una macro en cada libro de trabajo y luego llamarlo.

Debajo de UpdateLink() se UpdateLink() un bloque try/except/finally en caso de que el libro de trabajo no tenga enlaces, ya que LinkSources devolverá un valor Empty , generando una excepción COM, el mismo error que recibes:

error de tiempo de ejecución ''1004'' método ''updatelink'' método del objeto ''_workbook'' fallido

También asegúrese de desinicializar los objetos (una buena práctica recomendada en VBA también: Set wb = Nothing ) luego de usarlos para liberar recursos de la CPU o permanecer como procesos en segundo plano hasta la recolección de basura.

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

Aparte, aunque VBA se envía por defecto con las aplicaciones de Excel y MS Office, en realidad es un componente separado. Para verificar, en Herramientas / Referencias en VBA IDE, verá que VBA es el primer elemento marcado, no tiene nada incorporado. De hecho, VBA hace exactamente lo que está haciendo en Python: crear una interfaz COM para la Biblioteca de objetos de Excel. Entonces, en cierto sentido, VBA está relacionado con Excel y con Python.