Refresh Excel via Python

Giorgio Cozzolino

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!

Jirapongse

@Giorgio Cozzolino

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[1].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)