Hi all, I have an Excel template which has a series of formulas leveraging RSearch to pull information about selected Bonds. I am now trying to transition everything onto Python, but as the RSearch functionality is not available on there yet my only option is to run the Excel template via Python, refresh it and gather the data to then manipulate on my Python machine. This it the line of code I am currently using to run a simple VBA macro. The VBA macro simply refreshes the selected sheet. This is done by leveraging the Xlwings library, and works for other instances of running Excel macros via Python:
master_wb = xw.Book(file_directory) Macro_Engine = master_wb.macro("Macros.Refresh_Inputs") Macro_Engine ()
This time though I get an error, specifically: The macro may not be available in this workbook or all macros may be disabled.
Important to note that the VBA macro run smoothly when executed from Excel. It seems like that the add-ins are not properly being added on. Is there any way to upload these via Python before running the Refresh? I tried adding on EikonOfficeShim.dll but did not prove successful. Code to upload add in below, with filepath being the refreshable Excel template, and add_in_path the :full path for EikonOfficeShim.dll:
import time import win32com def ek_addins_import (filepath, add_in_path): try: xlapp = win32com.client.DispatchEx('Excel.Application') xlapp.COMAddIns("PowerlinkCOMAddIn.COMAddIn").Connect = True except AttributeError: # Corner case dependencies. import os import re import sys import shutil # Remove cache and try again. MODULE_LIST = [m.__name__ for m in sys.modules.values()] for module in MODULE_LIST: if re.match(r'win32com\.gen_py\..+', module): del sys.modules[module] shutil.rmtree(os.path.join(os.environ.get('LOCALAPPDATA'), 'Temp', 'gen_py')) from win32com import client xlapp = win32com.client.DispatchEx('Excel.Application') xlapp.COMAddIns("PowerlinkCOMAddIn.COMAddIn").Connect = True time.sleep(2) xlapp.RegisterXLL(add_in_path) time.sleep(2) xlapp.Workbooks.Open(add_in_path) time.sleep(2) wb = xlapp.Workbooks.Open(filepath,None,False) xlapp.Visible = True wb_addin = (add_in_path)
Any suggestion super appreciated. Thanks!