VBA: DSGRID formula does not work after file is opened using VBA

f.van.dorp
f.van.dorp
in Eikon COM

Hi,

I am using the following VBA macro

Sub OpenFXRates()
Dim RetVal
Dim FilePath As String

FilePath = """[FilePath]"""

RetVal = Shell("C:\Program Files (x86)\Microsoft Office\Office14\EXCEL.EXE " & FilePath, vbMaximizedFocus)

End Sub

The file that is opened in a new window contains the following formula:

=DSGRID("MLEJREE","RI","-1D","","D","RowHeader=true;ColHeader=true;Code=true;DispSeriesDescription=false;YearlyTSFormat=false;QuarterlyTSFormat=false","")

This gives a #NAME? error even though all TR systems are up and running. Once I close the file and reopen it manually the file runs perfectly.

Any idea what causes this error?

Kind regards,

Floris

Tagged:

Best Answer

  • Alex Putkov.1
    Alex Putkov.1
    Answer ✓

    Hi @f.van.dorp

    This is because Excel opens the workbook before it loads Thomson Reuters Eikon COM add-in (or any other COM add-on for that matter). So at the time the workbook is opened DSGRID worksheet function provided by Eikon Excel add-in is not available and Excel returns #NAME?. Assuming that you enabled automatic sign in for Eikon Excel in Settings - Sign in, and set "Automatically sign me in when" to "I start Microsoft Office", and assuming Excel is in autocalc mode, DSGRID function should resolve and return the data once the add-ins finish loading and once Eikon Excel add-on finishes signing in.

Categories