cannot execute workbook queries without opening the queries panel interactively

I have a issue where the VBA code for Workbook queries do not work until I interactively only the Workbook Query. I know it sounds weird.

I have the following code that makes the Workbook Queries (Data > Show Queries) Panel visible. If I open the workbook and try this run this, I will get a run-time error.

Application.CommandBars("Workbook Queries").Visible = True

The error is:
run-time error '5': Invalid procedure call or argument.

Once I click the Data > Show Queries the VBA code will work successfully now. If I close Excel and reload that workbook, I have to do the same thing. VBA refreshing the Workbook Queries will be successfully only after clicking on Show Queries at least one time.

Anything seen this issue?

SSI Web versions 7 and earlier rely on the Microsoft Visual Basic Runtime. Please make sure the latest version of the runtime is installed on your machine.
If you continue to get Error 5, the error may be caused if the user's "Documents" (or "My Documents") folder is not available, such as when the folder is on a network location and the user is disconnected from the network.
You may check if the Documents folder is not on the local drive by going to a command prompt and typing the word "set" and hitting the enter key. This will display the environment variables for the machine. Look for the environment variable called "USERPROFILE". If this variable points to a drive that does not exist on the machine, this is indicative of the problem. We've also seen the problem arise with the environment variable "HOMESHARE".

Quote:

---

Originally Posted by JohnMalkov

SSI Web versions 7 and earlier rely on the Microsoft Visual Basic Runtime. Please make sure the latest version of the runtime is installed on your machine.
If you continue to get Error 5, the error may be caused if the user's "Documents" (or "My Documents") folder is not available, such as when the folder is on a network location and the user is disconnected from the network.
You may check if the Documents folder is not on the local drive by going to a command prompt and typing the word "set" and hitting the enter key. This will display the environment variables for the machine. Look for the environment variable called "USERPROFILE". If this variable points to a drive that does not exist on the machine, this is indicative of the problem. We've also seen the problem arise with the environment variable "HOMESHARE".

---

Our My Documents folders are on the network, but it is available. It's a network share.

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

Code:

---

Private Sub Workbook_Open()
    Call UpdatePowerQueries
End Sub

---

on a separated Module:

Code:

---

' 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

---