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!
Best Answer
-
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()
LoopThen, 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)
0
Answers
-
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?
0 -
-
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!
0 -
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
0 -
Excellent thank you. On a final note, where can I find other Search Criteria? I am specifically looking for those related to Bonds. Thanks again.
0 -
It should be similar to the RSearch function in the Eikon Excel.
Please directly contact the Eikon support team via MyRefinitiv for other Search Criteria.
0
Categories
- All Categories
- 6 AHS
- 39 Alpha
- 162 App Studio
- 4 Block Chain
- 5 Bot Platform
- 17 Connected Risk APIs
- 47 Data Fusion
- 30 Data Model Discovery
- 608 Datastream
- 1.3K DSS
- 577 Eikon COM
- 4.9K Eikon Data APIs
- 7 Electronic Trading
- Generic FIX
- 7 Local Bank Node API
- Trading API
- 2.7K Elektron
- 1.3K EMA
- 236 ETA
- 519 WebSocket API
- 33 FX Venues
- 10 FX Market Data
- 1 FX Post Trade
- 1 FX Trading - Matching
- 12 FX Trading – RFQ Maker
- 5 Intelligent Tagging
- 2 Legal One
- 20 Messenger Bot
- 2 Messenger Side by Side
- 9 ONESOURCE
- 7 Indirect Tax
- 59 Open Calais
- 264 Open PermID
- 39 Entity Search
- 2 Org ID
- PAM
- PAM - Logging
- 8.4K Private Comments
- 6 Product Insight
- Project Tracking
- ProView
- ProView Internal
- 20 RDMS
- 1.4K Refinitiv Data Platform
- 370 Refinitiv Data Platform Libraries
- 3 Refinitiv Due Diligence
- LSEG Due Diligence Portal API
- 3 Refinitiv Due Dilligence Centre
- Rose's Space
- 1.1K Screening
- 18 Qual-ID API
- 13 Screening Deployed
- 23 Screening Online
- 10 World-Check Customer Risk Screener
- 990 World-Check One
- 44 World-Check One Zero Footprint
- 45 Side by Side Integration API
- Test Space
- 3 Thomson One Smart
- 1.2K TR Internal
- Global Hackathon 2015
- 2 Specialists Who Code
- 10 TR Knowledge Graph
- 150 Transactions
- 142 REDI API
- 1.7K TREP APIs
- 4 CAT
- 21 DACS Station
- 117 Open DACS
- 1.1K RFA
- 103 UPA
- 172 TREP Infrastructure
- 224 TRKD
- 886 TRTH
- 5 Velocity Analytics
- 5 Wealth Management Web Services
- 60 Workspace SDK
- 9 Element Framework
- 5 Grid
- 13 World-Check Data File
- Yield Book Analytics
- 46 中文论坛