question

Upvotes
Accepted
f.van.dorp avatar image
1 ●1 ●1 ●2

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

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

eikoneikon-com-apivba
icon clock
10 |1500

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

1 Answer

Upvotes
Accepted
Alex Putkov.1 avatar image
39.2k ●75 ●11 ●27

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.

icon clock
10 |1500

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

Click below to post an Idea Post Idea