For a deeper look into our Eikon Data API, look into:

Overview |  Quickstart |  Documentation |  Downloads |  Tutorials |  Articles

question

Upvotes
Accepted
Giorgio Cozzolino avatar image
11 ●2 ●3 ●4

Refresh Excel via Python

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!

eikoneikon-data-apiworkspaceworkspace-data-apirefinitiv-dataplatform-eikonpythoneikon-com-api
icon clock
10 |1500

Up to 2 attachments (including images) can be used with a maximum of 5.0 MiB each and 10.0 MiB total.

2 Answers

Upvotes
Accepted
Jirapongse avatar image
38.1k ●71 ●35 ●53

@giorgio

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)

1609139706045.png (22.9 KiB)
1609139995185.png (38.7 KiB)
icon clock
10 |1500

Up to 2 attachments (including images) can be used with a maximum of 5.0 MiB each and 10.0 MiB total.

Upvotes
Giorgio Cozzolino avatar image
11 ●2 ●3 ●4

Thanks very much @jirapongse.phuriphanvichai. Is the template you show as screenshot (the one from which you are able to execute the RSearch) available anywhere in the Template Library?

icon clock
10 |1500

Up to 2 attachments (including images) can be used with a maximum of 5.0 MiB each and 10.0 MiB total.

Jirapongse avatar image Jirapongse

@giorgio

It is an excel example available here.

Giorgio Cozzolino avatar image Giorgio Cozzolino Jirapongse

@jirapongse.phuriphanvichai

thanks for sharing. While using the file I am encountering a compile error: The code in this project must be updated for use on 64-bit systems. Please review and update Declare statements and then mark them with the PtrSafe attribute.

This happens for the following lines:

Private Declare Function CreateReutersObject Lib "PLVbaApis.dll" (ByVal progID As String) As Object
 Public Declare Function CreateReutersObject Lib "PLVbaApis.dll" (ByVal progID As String) As Object

Would you be able to help? Thanks!

Jirapongse avatar image Jirapongse Giorgio Cozzolino

@giorgio

Sorry. I forgot to mention it.

You need to add PtrSafe after the Declare keyword.

Private Declare PtrSafe Function CreateReutersObject Lib "PLVbaApis.dll" (ByVal progID As String) As Object


Show more comments
Click below to post an Idea Post Idea