Running a Dynamics CRM query in Excel

Question

Hi, I'm trying to refresh a query extracted from Dynamic CRM in Excel. If I just run it I get a blank result. I realized that for having this refreshed properly I need to go to "queries and Connection", on the connection go to properties and on the "Definition" tab go to "Edit Query". At that moment looks like I connect to Dynamic CRM, then I click on "Cancel" and, with "Refresh All" the query is refreshed correctly. I tried to see how Excel understand that connection, but it only gives me this

    With ActiveWorkbook.Connections("Query_from_Microsoft_CRM")
        .Name = "Query_from_Microsoft_CRM"
        .Description = ""
    End With

and this is not what I need to automatize the macro as this sentence does nothing. Can I get some help on how this process should be translated into a VBA statement?

Thanks!

Ezequiel

Answer 1

Hi Ezequiel!

I'm sorry I have no solutions to ofer but to inform that I'm facing the very exact problem and looking for a solution. So if, by any chance, you've found a solution, sharing would be veeery nice. :)

Thanks and best regards,

David

Answer 2

I haven't done this.  Dynamics supports OData Web API. You can perform OData accesses in Excel VBA.  I don't know about version compatibility.  I'm pretty sure you can do OData access in PowerPivot but I haven't played with it.  You can automate PowerPivot in VBA.