Consulting

Results 1 to 4 of 4

Thread: cannot execute workbook queries without opening the queries panel interactively

  1. #1

    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?

  2. #2
    VBAX Newbie JohnMalkov's Avatar
    Joined
    Dec 2021
    Location
    United Kingdom
    Posts
    1
    Location
    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".

  3. #3
    Quote Originally Posted by JohnMalkov View Post
    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.

  4. #4
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •