Looking into using WB to automate some Power Query (Excel 2016) activity. Ultimately would like to be able to create a data model from scratch, including entering M code and automating DAX. For starters I have a data model with 4 queries [based on the Microsoft FoodMart .csv files]. After loading the datamodel .xlsx file I run a simple ForEach loop:
ForEach cn In oXL.Activeworkbook.Connections
If StrIndex(cn.OLEDBConnection.Connection, "Provider=Microsoft.Mashup.OleDb.1",0,@FWDSCAN) >0
Display(2,"Power Query Connection",cn.OLEDBConnection.Connection)
cn.Refresh()
Endif
Next
which works perfectly for the 4 queries then errors before exiting the loop. (see attached). I do not want to use refreshall() as there are known timeout issues. Also don't want to turn errormode off. What I did notice is when adding
Message("Connections",oXL.Activeworkbook.Connections.Count)
it returns 5, as there is a connection called DataModel which has no description or properties associated with it. Not sure if it would always be the last one so a for n=1 to count-1 could fail especially if other connections not associated with the data model were present. So I guess I am asking for suggestions on how to trap for this in the original ForEach loop.
[EDIT]
Fixed by adding another if....
ForEach cn In oXL.Activeworkbook.Connections
If StrIndex(cn.name, "Query - ",0,@FWDSCAN) >0
If StrIndex(cn.OLEDBConnection.Connection, "Provider=Microsoft.Mashup.OleDb.1",0,@FWDSCAN) >0
Display(2,"Power Query Connection",cn.OLEDBConnection.Connection)
cn.Refresh()
Endif
Endif
Next