cannot execute workbook queries without opening the queries panel interactively

[arnelgp]

you can refresh your queries on the Workbook_Open event.
use the code here: Refresh Power Query With VBAThe Excelguru Blog

Private Sub Workbook_Open()
    Call UpdatePowerQueries
End Sub

on a separated Module:

' https://www.excelguru.ca/blog/2014/10/22/refresh-power-query-with-vba/
Public Sub UpdatePowerQueries()
    ' Macro to update my Power Query script(s)


    Dim lTest As Long, cn As WorkbookConnection
    On Error Resume Next
    For Each cn In ThisWorkbook.Connections
        lTest = InStr(1, cn.OLEDBConnection.Connection, "Provider=Microsoft.Mashup.OleDb.1", vbTextCompare)
        If Err.Number <> 0 Then
            Err.Clear
            Exit For
        End If
        If lTest > 0 Then cn.Refresh
    Next cn


End Sub