How to set Read Only for Power Query Connection?

Question

How can I force 'Read Only' in Power Query

Or save the source file as 'Share Deny Write'?

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.stack.imgur.com/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

score 0 · Answer 1 · answered Nov 23 '22 at 14:01

0

No idea if this helps, but somewhere squirreled away I saved VBA code to kill powerquery connections. Not my circus, not my monkey, so can't answer questions on it

Dim qr As WorkbookQuery
On Error Resume Next
For Each qr In csvWrapperWB.Queries
    qr.Delete
Next
csvWrapperWB.Close Savechanges:=False

answered Nov 23 '22 at 14:01

horseyride

17,007
2
11
22

Does not solve my problem. Strange that Microsoft would make Connections block the source file for update. But thanks anyway. – Svein Arne Hylland Nov 24 '22 at 06:56

Svein Arne Hylland · Accepted Answer · 2022-11-25T09:54:39.573

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

How to set Read Only for Power Query Connection?

2 Answers2

Linked