How to set Read Only for Power Query Connection?-CodePudding

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

CodePudding user response：

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

CodePudding user response：

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