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!
It works fine in my environment. I have used Tutorial 7 - RSearch as an example.
I added a macro to call the cmdRSearch_Click function.
Then, I modified the cmdRSearch_Click function to make sure that the myRSrchMgr is created properly.
Public Sub cmdRSearch_Click() Range("F14:F1000").ClearContents ' Just in case they're not already! ActiveCell.Select ' Instantiate the RSearch manager Set myRSrchMgr = CreateRSearchMgr() Do While myRSrchMgr Is Nothing Sleep 2000 myRSrchMgr = CreateRSearchMgr() Loop
Then, I used the xlwings to call the Macro2 in Module2.
import xlwings as xw master_wb = xw.Book("rsearch.xlsm") master_wb.sheets.range('c9').value = "Japan" Macro_Engine = master_wb.macro("Module2.Macro2") Macro_Engine ()
I got the same error if I used an invalid macro, such as Module2.Macro3.
com_error: (-2147352567, 'Exception occurred.', (0, 'Microsoft Excel', "Cannot run the macro ''RSearch.xlsm'!Module2.Macro3'. The macro may not be available in this workbook or all macros may be disabled.", 'xlmain11.chm', 0, -2146827284), None)