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