Does the LSEG Workspace Excel Add-in provide a way to refresh TR formulas programmatically via Pytho

@Tatiannah

Thank you for reaching out to us.

Workspace Excel provides refresh macros, such as WorkspaceRefreshAll, which can be used to refresh Workspace formulas. For more information, please refer to this discussion:

Currently, we do not provide any Python libraries for controlling or managing Excel files. Clients may use third-party solutions, but these are outside the scope of our support.

Our recommended approach is to use the LSEG Data Library for Python to retrieve data and then use third-party libraries, such as xlwings, to update Excel workbooks. The following articles may be helpful:

@Tatiannah

I have tested it by creating a macro in the Excel file that calls the WorkspaceRefreshAll method.

Sub WSRefreshSheet()

    Application.Run "WorkspaceRefreshAll", True, 120000, ""

End Sub

Then, use the xlwings to call this macro and retrieve the updated values from an Excel cell (D3).

The Python code is:

import xlwings as xw
import time

with xw.App(visible=False, add_book=False) as app:
    wb = app.books.open('Book1.xlsm')

    refresh_macro = wb.macro('Sheet1.WSRefreshSheet')
    refresh_macro()    

    sheet = wb.sheets.active    

    for i in range(1,10):
        val1 = sheet['D3'].value 
        print(val1)
        refresh_macro()
        time.sleep(5)
    
    wb.save()
    wb.close()

The code works fine:

However, this method relies on the third party which is out of our support scope.

Hi Jirapongse,

Is it possible to call this WorkspaceRefreshAll method directly from a Python COM object using win32, instead of creating a macro in the file? Please see the sample below. Also, if that is not possible, would we need to create this macro for each sheet in Excel when there are multiple sheets in a file?

import win32com.client as client

excel = client.DispatchEx("Excel.Application")
wb = excel.Workbooks.Open(
Filename=str(workbook_path),
)
excel.Application.Run("WorkspaceRefreshAll")
time.sleep(30)

I tried the attached code and it doesn't work.

Actually, the WSRefreshSheet macro calls WorkspaceRefreshAll, so it refreshes all formulas in the workbook, not just those on the active worksheet. I apologise for the macro name, which may have caused some confusion.

For example, I have create Sheet2 that contains another formula and run the code. Calling that WSRefreshSheet macro will refresh both sheets.

import xlwings as xw
import time

with xw.App(visible=False, add_book=False) as app:
    wb = app.books.open('Book2.xlsm')

    refresh_macro = wb.macro('Sheet1.WSRefreshSheet')
    refresh_macro()    

    sheet1 = wb.sheets['Sheet1']    
    sheet2 = wb.sheets['Sheet2']  
    for i in range(1,10):
        val1 = sheet1['D3'].value 
        print(f"Sheet1: {val1}")
        val2 = sheet2['D3'].value 
        print(f"Sheet2: {val2}")
        refresh_macro()
        time.sleep(5)
    
    wb.save()
    wb.close()

Hi Jirapongse,

I tried your code, but it still isn't working. My macro name is WSRefreshWorkbook. I get the below error pop-up. I also checked the Excel UI while the code was running, and it looks like the Workspace add-in is not loading. There is no Workspace ribbon showing. When I open Excel manually, the Workspace ribbon is visible and the macro runs without any error, but the issue happens during automation. I also added time.sleep for more than 60 seconds, but it still doesn't work. What should I do to fix this?

jirapongse_lseg

This could be a problem when launching Excel from a script. The Workspace add-ins may not load correctly, or the user may not be logged in properly.

For this reason, I do not recommend using this approach.Our recommended solution is to use the LSEG Data Library for Python to retrieve data and then use third-party libraries, such as xlwings, to update Excel workbooks.

You can share a simple Excel file and script that you are using. I can test it on my machine.

I also shared my test Excel file and script.

test.zip

jirapongse_lseg

The code that use win32com.client looks like this:

import os
import win32com.client
import time

excel = win32com.client.Dispatch("Excel.Application")
excel.Visible = False

connect_retry = 1
max_connect_retry = 10

file_path = os.path.abspath("Book2.xlsm")
workbook = excel.Workbooks.Open(file_path)

for addin in excel.COMAddIns:
    print(f"Description: {addin.Description}")
    print(f"ProgId: {addin.ProgId}")
    print(f"Connected: {addin.Connect}")
    print("-" * 50)

sheet1 = workbook.Worksheets('Sheet1')
sheet2 = workbook.Worksheets('Sheet2')
connected_text = sheet1.Cells(1,1).Text
while connected_text.startswith('Updated') == False and connect_retry <= max_connect_retry:
    print(connected_text)
    connect_retry = connect_retry+1
    time.sleep(5)
    connected_text = sheet1.Cells(1,1).Text

if connected_text.startswith('Updated') == True:
    print("Connected")        

    #print(f"{sheet1.Cells(1,1).Text}")
    macro_name = f"{os.path.basename(file_path)}!Sheet1.WSRefreshSheet"
    excel.Application.Run(macro_name)
    print(f"Sheet1 Cell Value: {sheet1.Cells(3, 4).Value}")
    print(f"Sheet2 Cell Value: {sheet2.Cells(3, 4).Value}")
    
print("### Close Workbook and Quit ###")
workbook.Close(False)
excel.Quit()

del sheet1
del sheet2
del workbook
del excel

Hi @jirapongse_lseg ,

Thank you. I believe the issue is related to loading the add-in. When using LSEG Data Library for Python to retrieve data and then working with third-party libraries such as xlwings , we lose the formulas and can no longer update the files manually. The script is below, and I have tried both win32com and xlwings. Is there a way to ensure that the workspace add-in is loaded correctly? C5 and C6 contain TR formulas. The process works when the Workspace add-in is loaded correctly, but the issue is specifically with loading the Workspace add-in consistently.

Can you run my win32com.client sample with the attached Books2.xlsm to retrieve data?

The sample code lists the loaded Excel Add-ins.

for addin in excel.COMAddIns:

    print(f"Description: {addin.Description}")

    print(f"ProgId: {addin.ProgId}")

    print(f"Connected: {addin.Connect}")

    print("-" * 50)

If the Workspace Add-ins is loaded properly, you will see this output.

--------------------------------------------------

Description: Workspace

ProgId: PowerlinkCOMAddIn.COMAddIn

Connected: True

--------------------------------------------------

Then, it checked the status of the Workspace Add-ins formula.

sheet1 = workbook.Worksheets('Sheet1')

sheet2 = workbook.Worksheets('Sheet2')

connected_text = sheet1.Cells(1,1).Text

while connected_text.startswith('Updated') == False and connect_retry <= max_connect_retry:

    print(connected_text)

    connect_retry = connect_retry+1

    time.sleep(5)

    connected_text = sheet1.Cells(1,1).Text


if connected_text.startswith('Updated') == True:

    print("Connected")        
...

The output is:

Not Signed In

Not Signed In

Not Signed In

Not Signed In

Not Signed In

Not Signed In

Retrieving...

Connected

After the Workspace Excel Add-ins is loaded properly, it can call the Excel macro and then retrieve data.

if connected_text.startswith('Updated') == True:
    print("Connected")        

    #print(f"{sheet1.Cells(1,1).Text}")
    macro_name = f"{os.path.basename(file_path)}!Sheet1.WSRefreshSheet"
    excel.Application.Run(macro_name)
    print(f"Sheet1 Cell Value: {sheet1.Cells(3, 4).Value}")
    print(f"Sheet2 Cell Value: {sheet2.Cells(3, 4).Value}")

Hi @Jirapongse ,

I tried downloading your files, but it appears that the macro is being blocked by the organization’s policy. I’ve attached a sample Workspace file; the original file will be much more complex, with many sheets and formulas.

While running the code I got the status of Workspace com add in to be connected = True but the Workspace ribbon was not showing in excel ui (verified by using visible=True). Modified the code based on my file.

Description: Workspace

      ProgId: PowerlinkCOMAddIn.COMAddIn

       Connected: True

    


import os

import win32com.client

import time


excel = win32com.client.Dispatch("Excel.Application")

excel.Visible = False


connect_retry = 1

max_connect_retry = 10


file_path = os.path.abspath(r"Workspace_test_file.xlsm")

workbook = excel.Workbooks.Open(file_path)


for addin in excel.COMAddIns:

    print(f"Description: {addin.Description}")

    print(f"ProgId: {addin.ProgId}")

    print(f"Connected: {addin.Connect}")

    print("-" * 50)


sheet1 = workbook.Worksheets('Sheet1')

# sheet2 = workbook.Worksheets('Sheet2')

connected_text = sheet1.Cells(3,5).Text

while connect_retry <= max_connect_retry:

    print(connected_text)

    connect_retry = connect_retry+1

    time.sleep(5)

    connected_text = sheet1.Cells(3,5).Text


# if connected_text.startswith('Updated') == True:

    print("Connected")        


    #print(f"{sheet1.Cells(1,1).Text}")

    macro_name = f"{os.path.basename(file_path)}!Sheet1.WSRefreshWorkbook"

    excel.Application.Run(macro_name)

    print(f"Sheet1 Cell Value: {sheet1.Cells(3, 5).Value}")

    # print(f"Sheet2 Cell Value: {sheet2.Cells(3, 4).Value}")


print("### Close Workbook and Quit ###")

workbook.Close(False)

excel.Quit()

Workspace_test_file.zip

Unfortunately, things aren’t working as expected.

After making a few minor modifications, I was able to run the code successfully.

1.The macro name should be Module1.WSRefreshWorkbook.

    macro_name = f"{os.path.basename(file_path)}!Module1.WSRefreshWorkbook"

2. The Cells should be (5,3).

    print(f"Sheet1 Cell Value: {sheet1.Cells(5, 3).Value}")

The modified code is:

import os

import win32com.client

import time

excel = win32com.client.Dispatch("Excel.Application")

excel.Visible = False

connect_retry = 1

max_connect_retry = 10

file_path = os.path.abspath(r"Workspace_test_file.xlsm")

workbook = excel.Workbooks.Open(file_path)

for addin in excel.COMAddIns:

    print(f"Description: {addin.Description}")

    print(f"ProgId: {addin.ProgId}")

    print(f"Connected: {addin.Connect}")

    print("-" * 50)

sheet1 = workbook.Worksheets('Sheet1')

# sheet2 = workbook.Worksheets('Sheet2')

connected_text = sheet1.Cells(3,5).Text

while connect_retry <= max_connect_retry:

    print(connected_text)

    connect_retry = connect_retry+1

    time.sleep(5)

    connected_text = sheet1.Cells(3,5).Text

# if connected_text.startswith('Updated') == True:

    print("Connected")        

    #print(f"{sheet1.Cells(1,1).Text}")

    macro_name = f"{os.path.basename(file_path)}!Module1.WSRefreshWorkbook"

    excel.Application.Run(macro_name)

    print(f"Sheet1 Cell Value: {sheet1.Cells(5, 3).Value}")

    # print(f"Sheet2 Cell Value: {sheet2.Cells(3, 4).Value}")

print("### Close Workbook and Quit ###")

workbook.Close(False)

excel.Quit()


del sheet1

del workbook

del excel

The output looks like this:

Description: Microsoft Power Map for Excel

ProgId: ExcelPlugInShell.PowerMapConnect

Connected: False

--------------------------------------------------

Description: Microsoft Data Streamer for Excel

ProgId: MicrosoftDataStreamerforExcel

Connected: False

--------------------------------------------------

Description: Microsoft Azure Information Protection

ProgId: MSIP.ExcelAddin

Connected: True

--------------------------------------------------

Description: Inquire

ProgId: NativeShim.InquireConnector.1

Connected: False

--------------------------------------------------

Description: Workspace

ProgId: PowerlinkCOMAddIn.COMAddIn

Connected: True

--------------------------------------------------

Description: Microsoft Power Pivot for Excel

ProgId: PowerPivotExcelClientAddIn.NativeEntry.1

Connected: False

--------------------------------------------------


Connected

Sheet1 Cell Value: 271.86


Connected

Sheet1 Cell Value: 271.86


Connected

Sheet1 Cell Value: 271.86


Connected

Sheet1 Cell Value: 271.86


Connected

Sheet1 Cell Value: 271.86


Connected

Sheet1 Cell Value: 271.86


Connected

Sheet1 Cell Value: 271.86


Connected

Sheet1 Cell Value: 271.86


Connected

Sheet1 Cell Value: 271.86


Connected

Sheet1 Cell Value: 271.86

### Close Workbook and Quit ###

I am not sure why you could not see the Workspace tab in Excel. You may need to check the Workspace version and LSEG Workspace for Microsoft Office version.

Workspace version (Help → About)

LSEG Workspace for Microsoft Office (Settings → Office → Update)

Hi @Jirapongse ,

It is showing status as True for Workspace

Description: Workspace
ProgId: PowerlinkCOMAddIn.COMAddIn
Connected: True

But I am receiving following error

I also checked the About status, and all statuses appear to be OK and green. The settings look fine as well.

Please note that I can update the file manually, and the Workspace add-in loads properly when I open the file manually. The issue occurs when I open these files using COM or xlwings, even though the COM add-in connection status is True.

I think the issue may be that the Workspace ribbon was not loaded correctly when the Python script launched the application.

However, on my machine, the Workspace ribbon is loaded correctly when running the same Python script.

This issue may be related to the way the Workspace Excel Add-in interacts with Microsoft Excel, which is beyond my scope of support.

Please contact the Workspace Excel Support team directly through LSEG Support to investigate whether this behaviour is a limitation or a known issue with Excel or the Workspace Excel Add-in, and to confirm whether this use case is officially supported.

When raising the support case, please include the URL of this discussion to help prevent the issue from being redirected back to this Q&A forum.

Hi @Jirapongse ,

Thank you for confirming. The issue occurs only when I open the Excel file through COM or xlwings. I can open the file manually, and the Workspace ribbon loads correctly in that case. Therefore, the problem occurs on my machine only when Excel is opened through Python win32com or xlwings. I will also discuss this with LSEG Excel Support.

Could you also verify whether this approach

excel.Application.Run("WorkspaceRefreshAll", True, 120000, "")

can replace creating macros, adding them to the Excel files, and calling them? I believe it should work if we allow some time for the formula update to take effect. Please validate this as well.

I think WorkspaceRefreshAll is not a macro in the Excel file. It is likely a function provided by the Workspace COM Excel Add-in, which means it cannot be called in the same way from Python.

Today, I installed a different version of Workspace (OpenFin) on my machine. Now, when I start Excel from the script, the Workspace ribbon is no longer loaded.

However, if I open the Excel file manually, the Workspace ribbon loads correctly.

Are you using the Workspace or Workspace OpenFin? This is my new LSEG Workspace version.

Hi @Jirapongse,

Here is the screenshot of my Workspace system status. Kindly check.

On my machine, the Workspace version is 1.26.831.

The Workspace COM add-ins version is 26.29.13 which is in C:\Program Files\Refinitiv\Refinitiv for Office\26.29.13\Office_x64 directory.

I’m not sure whether the version is related to this issue. Please contact the Workspace Support team to verify it.

Hi @Jirapongse,

I tested the code below on my side, and it is working fine. When I use subprocess to open Excel from its path instead of using DispatchEx, the Workspace add-in loads correctly. I also used excel.Application.Run("WorkspaceRefreshAll", True, 120000, "") instead of creating a macro, and that works as expected. Could you please test this approach on your system and let me know whether it works there as well? This will help confirm whether it is compatible with any version. Please see the image below.

Check below image

Check the code below

import time



from pathlib import Path

import win32com

import subprocess, time, os

import win32com.client

import pythoncom

from pywintypes import com_error



EXCEL = r"C:\Program Files\Microsoft Office\root\Office16\EXCEL.EXE"

XL_DONE = 0

WB = os.path.abspath("Workspace_test_file.xlsm")



def wait_for_excel(timeout=1200):

    """Wait until Excel registers in the Running Object Table."""

    deadline = time.time() + timeout

    while time.time() < deadline:

        try:

            return win32com.client.GetActiveObject("Excel.Application")

        except Exception:

            time.sleep(1)

    raise TimeoutError("Excel never appeared in the ROT")



def wait_until_ready(excel, timeout=1200):

    """Wait until Excel finishes starting up and stops rejecting COM calls."""

    deadline = time.time() + timeout

    while time.time() < deadline:

        try:

            if excel.Ready:          # False while Excel is still initialising

                return

        except Exception:

            pass                     # 'Call was rejected by callee' while busy

        time.sleep(1)

    raise TimeoutError("Excel never became ready")



def wait_for_workbook(excel, filename, timeout=120):

    """Wait until the workbook actually appears in the Workbooks collection."""

    deadline = time.time() + timeout

    while time.time() < deadline:

        try:

            return excel.Workbooks(filename)

        except Exception:

            time.sleep(5)

    raise TimeoutError(f"{filename} never opened")



def com_retry(fn, attempts=120, delay=1.0):

    """Retry a COM call while Excel is busy."""

    last = None

    for _ in range(attempts):

        try:

            return fn()

        except com_error as e:

            if e.hresult not in RETRYABLE:

                raise

            last = e

            time.sleep(delay)

    raise last


def wait_until_calculated(excel, sheet=None, ranges=(),

                          stable_samples=5, interval=5, timeout=600):

    """Wait for Excel calc + async queries + (optionally) specific cells to resolve."""

    deadline = time.time() + timeout

    consecutive = 0


    while time.time() < deadline:

        try:

            # 1. Excel itself responsive

            if not excel.Ready:

                consecutive = 0

                time.sleep(interval)

                continue

            print(f"Excel is ready. Calculation state: {excel.CalculationState}")

            # 2. Drain outstanding async UDF queries (TR)

            excel.CalculateUntilAsyncQueriesDone()


            # 3. Calculation engine idle

            if excel.CalculationState != XL_DONE:

                consecutive = 0

                time.sleep(interval)

                continue


            # 4. No placeholders left in the cells we care about

            # if sheet is not None and ranges:

            #     if any(is_pending(v)

            #            for addr in ranges

            #            for v in _flatten(sheet.Range(addr).Value)):

            #         consecutive = 0

            #         time.sleep(interval)

            #         continue


            # # 5. Require the idle state to persist

            # consecutive += 1

            # if consecutive >= stable_samples:

            #     return True

            return True


        except Exception:

            # COM rejects calls while Excel is busy — that itself means "not ready"

            consecutive = 0


        time.sleep(interval)


    return False



def wait_for_workspace(excel, timeout=1200):

    """

    Wait until the add-in has registered WorkspaceRefreshAll.

    Calling it is the only reliable readiness test — Connect=True lies.

    """

    deadline = time.time() + timeout

    last = None

    while time.time() < deadline:

        try:

            excel.Application.Run("WorkspaceRefreshAll", True, 120000, "")

            return

        except Exception as e:

            last = e

            time.sleep(6)

    raise TimeoutError(f"WorkspaceRefreshAll never became available: {last}")



pythoncom.CoInitialize()

try:

    # Normal launch — no -Embedding, so add-ins load exactly as they do

    # when you double-click Excel yourself.

    subprocess.Popen([EXCEL, WB])


    excel = wait_for_excel()

    wait_until_ready(excel)

    wb = wait_for_workbook(excel, os.path.basename(WB))

    wait_for_workspace(excel)          # also performs the first refresh

    everything_loaded_success = wait_until_calculated(excel, sheet=wb.Worksheets(1), ranges=("C5:C12", "D3"), stable_samples=5, interval=5, timeout=600)



    print("Everything is up.")

    time.sleep(30)  # Give the add-in time to finish its first refresh

    RETRYABLE = {

        -2147418111,  # RPC_E_CALL_REJECTED   "Call was rejected by callee"

        -2147417846,  # RPC_E_SERVERCALL_RETRYLATER  "Application is busy"

        -2147417851,  # RPC_E_SERVERFAULT

        }



    sheet = com_retry(lambda: wb.Worksheets("Sheet1"))


    t0 = time.perf_counter()

    deadline = 600  # seconds

    try:

        ret = excel.Application.Run("WorkspaceRefreshAll", True, 120000, "")

        if wait_until_calculated(excel, sheet=wb.Worksheets("Sheet1"), ranges=("C5:C12", "D3"), stable_samples=5, interval=5, timeout=600):

            print("Excel finished calculating after WorkspaceRefreshAll.")

            print(f"returned {ret!r} in {time.perf_counter()-t0:.3f}s")

            # time.sleep(30)

    except Exception as e:

        print(f"raised after {time.perf_counter()-t0:.3f}s: {e}")







    wb.Save()


    try:

        c4_to_c12_values = wb.Worksheets("Sheet1").Range("C4:C12").Value

        print(f"Saved workbook C4:C12 values: {c4_to_c12_values}", flush=True)

    except Exception as e:

        print(f"Could not read saved workbook C4:C12 values: {e}", flush=True)


    wb.Close(SaveChanges=False)

    excel.Quit()


finally:

    pythoncom.CoUninitialize()