excelvbapowerquery

How to set Read Only for Power Query Connection?


How can I force 'Read Only' in Power Query

Every day my Excel report joins information from several different sources, and saves the results in separate xlsx files. These xlsx files (sheets) are then being used as source for other reports.

But the problem is if anyone opens one of these other reports where Power Query Connection has been used. It will also keeps the source file busy for me to update. (by overwrite with my SaveAs Macro below)
The result is that none of the reports is up to date – as there is no easy way to set the Query or Connection to a Read Only on its source.

In earlier Excel versions users could select souse connection to Read Only like this :[Connecting to a workbook]https://i.sstatic.net/2L72p.jpg

My code for save the Sheets goes like this:

Sub Sap_Ordrer_SaveAs()
     
Dim wb As Workbook
Application.DisplayAlerts = False
     
    ' SaveAs File : Sap-Ordrer.xlsx
    Sheets("SAP-ordrer").Copy
    Set wb = ActiveWorkbook
    With wb
        .SaveAs GetWorkingPath() & "\Sap-Ordrer"
        .Close False
    End With
    Set wb = Nothing

    
Application.DisplayAlerts = True
End Sub


Solution

  • I will test if this will do the trick today - If this will release my xlsx source files for update I will call this answer as solved.

    Answer found here

    Dim conn As Variant
    
    For Each conn In ActiveWorkbook.Connections
        conn.OLEDBConnection.MaintainConnection = False
    Next conn
    

    More info can be found here: Microsoft learn

    Whola - This worked ! I will here include how it is used in my code:

    Sub TestEditWorkCenterQueries()
    ' Coded by Svein Arne Hylland 25.11.2022.
    ' This sub is to show how to change filters in all query
    ' In this case there is two queries one for 'Order' and one for 'Order Operations' - Bouth can filter on 'Main WorkCtr'.
    ' The WorkCenterArrey string will then be parsed from other sub in a form to change the filters on ThisWorkbook.Queries.
    ' Then each user can select what 'Main WorkCtr' they vant to include in their workbook.
    Dim WorkCenterArrey As String
    WorkCenterArrey = "'1338-XT','1338TOOL'"
    EditAllWorkbookFormuals (WorkCenterArrey)
    End Sub
    
    Sub EditAllWorkbookFormuals(SelectedWorkCenters As String)
    For Each q In ThisWorkbook.Queries
    If q.Name <> "ZIQ09" Then
     q.Formula = NewQuery(q.Formula, SelectedWorkCenters)
     With ThisWorkbook.Connections("Query - " & q.Name)
        .Refresh
        .OLEDBConnection.MaintainConnection = False
     End With
    End If
    Next
    End Sub
    
    Function NewQuery(MyQuery As String, WorkCenterArrey As String) As String
    ' Function recive the set Formula and returns the updated Formula with the new filter
    WorkCenterArrey = Replace(WorkCenterArrey, "'", "")
    ' Convert the string to arrey for filter
    Dim MyArr() As String
    MyArr = Split(WorkCenterArrey, ",")
    ' Do you manipulation here of your own MyQuery string
    'MsgBox MyQuery
    ' Use Select case, If, Inst, replace, and so on.
    ' .....
    Pos1 = InStr(1, MyQuery, "each ([Main WorkCtr] = ") ' Find Start position to be replaced
    If Pos1 > 0 Then Pos2 = InStr(Pos1, MyQuery, ")") ' Finds then End posistion to be replaced
    If Pos1 > 0 Then
        ToBeReplaced = Mid(MyQuery, Pos1, Pos2 - Pos1 + 1)
        'MsgBox ToBeReplaced
        ' Build up the new filter from Arrey
        NewFilter = "each ("
        For e = LBound(MyArr) To UBound(MyArr)
            NewFilter = NewFilter & "[Main WorkCtr] = """ & MyArr(e) & """ or "
        Next
        'Remove the last or statement - and add the parantese
        NewFilter = Left(NewFilter, Len(NewFilter) - 4) & ")"
        'MsgBox NewFilter
        
        ' Replace the filter
        MyQuery = Replace(MyQuery, ToBeReplaced, NewFilter)
    End If
    ' Parse the new string back to Sub as the NewQuery
    NewQuery = MyQuery
    End Function