Refresh Excel Via Python Using Win32 Client

Aleniles
Aleniles
in Datastream

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 ?

Tagged:

Best Answer

Answers

  • Jirapongse
    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
    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
    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


Categories