Refresh Excel Via Python Using Win32 Client

Aleniles

Hello, I have built this code in python - as a mere example - to open a specific Excel file using the Win32.client where I need to add a specific DSGRID code to fetch some data in Datastream with the COM addin.

#
# Open an existing workbook
#
import win32com.client as win32

# Set-up a Connection With Excel
excel = win32.gencache.EnsureDispatch('Excel.Application')

# Open An Existing File
wb = excel.Workbooks.Open(r'C:\Users\anilo\Downloads\add_a_workbook.xlsx')

# Select A Sheet And Write In A Cell
ws = wb.Worksheets("Sheet1")
ws.Cells(1, 1).Value = '=@DSGRID("PCH#(S&PCOMP,1M)"," ","-2Y","","M","RowHeader=true;ColHeader=true;DispSeriesDescription=false;YearlyTSFormat=false;QuarterlyTSFormat=false","")'

# Activate COM Addins
excel.COMAddIns("PowerlinkCOMAddIn.COMAddIn").Connect = True

# Refresh all data connections
wb.RefreshAll()

# Show Excel
excel.Visible = True

# Quit Excel
excel.Application.Quit()

Unfortunately the code is not able to refresh the sheet (and thus then potentially save & close the file). Is there any possibility to refresh a DSGRID code via Python using the win32com ?

Jirapongse

@Aleniles

You can use Datastream Web Service which supports Python (DatastreamPy). You can use it with Excel by using xlwings.

For more information regarding xlwings, please refer to this article.

Jirapongse

@Aleniles

Thanks for reaching out to us.

I found the document in the previous discussion.

The code looks like this:

DFOCOMAddIn = excel.COMAddIns["DFOAddInExcel2010"]
DFOCOMAddIn.Object.RefreshWorkbook()

I hope that this information is of help

Aleniles

Uhm,

I think those commands doesn't work anymore.

I tried again this morning and now the code works with the previous

excel.COMAddIns("PowerlinkCOMAddIn.COMAddIn").Connect = True

I had to add "sleep" to wait for the update before saving. By the way, I realised you could also run a macro, so basically you can update the sheet from that one (see https://www.youtube.com/watch?v=qyy7xUnK5lg)

This is the final working example:

#
# Open an existing workbook, add a DS Formula, Update And Save
#
import win32com.client as win32
import time

# Set-up a Connection With Excel
excel = win32.gencache.EnsureDispatch('Excel.Application')

# Open An Existing File
wb = excel.Workbooks.Open(r'C:\Users\anilo\Downloads\add_a_workbook.xlsx')

# Show Excel
excel.Visible = True

# Select A Sheet And Write In A Cell
ws = wb.Worksheets("Sheet1")
ws.Cells(1, 1).Value = '=@DSGRID("PCH#(S&PCOMP,1M)"," ","-2Y","","M","RowHeader=true;ColHeader=true;DispSeriesDescription=false;YearlyTSFormat=false;QuarterlyTSFormat=false","")'

# Activate COM Addins
excel.COMAddIns("PowerlinkCOMAddIn.COMAddIn").Connect = True

# Refresh all data connections
# excel.COMAddIns["DFOAddInExcel2010"].Object.RefreshWorkbook()
# wb.RefreshAll()

# Wait for the update before saving
time.sleep(10)

# Save All - Change this to False if you don't want it to be printed out
wb.Close(SaveChanges=True)

# Quit Excel
excel.Application.Quit()

I hope it helps for someone else.

Aleniles

Btw, do you know if there is a way to connect directly to the Datastream or Eikon Api using the Win32 method in Jupyter Notebook for example? Like writing a code like this:

c = win32com.client.Dispatch("Datastream.Connection")

And then some other commands to fetch series directly