question

Upvotes
Accepted
30 4 6 13

Refresh Excel Via Python Using Win32 Client

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 ?

python#technologyexceldatastream-for-officewin32
icon clock
10 |1500

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

@Aleniles

Hi,

Thank you for your participation in the forum.

Are any of the replies below satisfactory in resolving your query?

If yes please click the 'Accept' text next to the most appropriate reply. This will guide all community members who have a similar question.

Otherwise please post again offering further insight into your question.

Thanks,

AHS

Upvote
Accepted
78.8k 250 52 74

@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.


icon clock
10 |1500

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

Upvotes
78.8k 250 52 74

@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

icon clock
10 |1500

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

Upvotes
30 4 6 13

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.


icon clock
10 |1500

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

Upvotes
30 4 6 13

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


icon clock
10 |1500

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

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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