Power Query ForEach error

Started by stanl, June 04, 2018, 05:25:51 AM

Previous topic - Next topic

stanl

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:

Code (WINBATCH) Select

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

Code (WINBATCH) Select

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....
Code (WINBATCH) Select

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