Deleting Queries and Connections not working in O365 version

[KimP]

Hi,

I've gotten to the point I'm spending way too much time trying to solve this myself so I'm hoping for some expert help.

I have a workbook that uses Power Query to allow users to build a report.
Macros to allow them to refresh the data and save the report page of the workbook to a destination folder as a new workbook. All of this works as intended, the part I'm having issues with is the tidy up that goes on when the SaveReport macro is run.

The main issue I'm having is making sure all queries and connections are deleted from the new workbook.
The new workbook is created with:

ThisWorkbook.Sheets("Main").Copy

Then I try and delete queries and connections:

    With ActiveWorkbook
        .SaveAs Filename:=FPath & "/" & FName, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
            
            
            'remove reference to comments
            Range("Comments").Select
            Selection.Copy
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=False, Transpose:=False
            Application.CutCopyMode = False
        
       
        DeleteQueries
        RemoveConnections
        
        .Save
        '.Close SaveChanges:=False
        
    End With
    
    Application.CopyObjectsWithCells = True
    Application.DisplayAlerts = True

The DeleteQueries and RemoveConnections referenced in the code above have been varying ways of trying to achieve this from the research I have done.
Some of these are listed below.

' check if a given connection exists in the given workbook
Function ConnExists(name$, Optional wb As Workbook) As Boolean
If wb Is Nothing Then Set wb = ActiveWorkbook
On Error Resume Next
ConnExists = CBool(Len(wb.Connections(name).name))
End Function

'Delete all queries in the active workbook
Public Sub DeleteQueries()
Dim q
For Each q In ActiveWorkbook.Queries
    If ConnExists(q.name) Then DeleteQueryConn (q.name)    
    q.Delete
Next
End Sub

For i = 1 To ActiveWorkbook.Connections.Count
If ActiveWorkbook.Connections.Count = 0 Then Exit Sub
ActiveWorkbook.Connections.Item(i).Delete
i = i - 1
Next i

        
On Error Resume Next        
'remove external connections from report
        For Each connection In .Connections
            connection.Delete
            Next

        
'remove queries from report - this is needed so O365 versions of Excel work correctly with this script
        For Each query In .Queries
            query.Delete
            Next

Dim pq As Object
For Each pq In ActiveWorkbook.Queries
    pq.Delete
Next

Everything works correctly for users with Excel 2016 (for pretty much all of the variations tried) but not for O365 version of Excel.

I've noticed that the connections seem to be removed successfully in O365 version and also some of the queries but some of the queries remain.
I'm wondering if I need to remove links first or whether the queries need to be deleted in a certain order?

Any help is greatly appreciated.

Thanks,
Kim

[Bob Phillips]

Kim,

If a query references another query, you will need to delete the referenced query first (although that is true in 20916 as well).

[Bob Phillips]

I have just looked at my app that deletes PQ queries, and I delete the connections as well

    
            For Each mpConn In wb.Connections
                
                If Not mpConn.InModel Then
                    
                    If Not IsNull(mpConn.OLEDBConnection) Then
                    
                        mpConnString = mpConn.OLEDBConnection.Connection
                        If InStr(1, mpConnString, CONNECTION_STRING_PROVIDER) > 0 And _
                           InStr(1, mpConnString, Replace(CONNECTION_STRING_LOCATION, PLACEHOLDER_QUERY_NAME, .QueryName)) > 0 Then
                        
                            mpConn.Delete
                        End If
                    End If
                ElseIf InStr(1, mpConn.Name, CONNECTION_NAME_PREFIX & .QueryName) > 0 Then
                
                    mpConn.Delete
                End If

The variables are

CONNECTION_STRING_PROVIDER = "Provider=Microsoft.Mashup.OleDb.1;"
CONNECTION_STRING_LOCATION As String = "Location=" & PLACEHOLDER_QUERY_NAME
CONNECTION_NAME_PREFIX is a prefix I add as I create the query through VBA

[KimP]

If a query references another query, you will need to delete the referenced query first (although that is true in 20916 as well).

Which makes sense except 2016 doesn't seem to care and all queries are removed.
O365 only some removed but I haven't been able to see a pattern in the ones that are left behind.

If it is the order that is the problem, is there an easy way to define the order to delete them without explicitly naming every query?

[Bob Phillips]

Not that I know of, the object model exposes the connection, but not details of the queries within Power Query. It is somewhat lacking in my view.

You could try two passes of the queries, first gets rid of referenced queries, the second will get the rest.

[KimP]

Ok, so I took the troubleshooting back to basics.
Using Excel 2016, I manually copied the sheet to a new workbook, using the 'Move or Copy' in the right click context menu.

It turns out, in 2016 none of the queries are copied when this is done, so my earlier assumption that the 'DeleteQueries' routine was working in 2016 was incorrect.
The removing connections works as intended in both versions.

If I could figure out why 2016 doesn't include the queries when copying and replicate that in O365 it would solve my problem.
Alternately, I will need to try the suggestion of two passes or specifying the order for deleting the queries.

[KimP]

I have upgraded and am now using Excel O365 ProPlus (version 1907).
I have managed to get most of the issues resolved.

The main part of the method, breaks external links, removes links to all tables, removes queries (some fail), removes workbook connections, removes queries again (this seems to get the rest of them) and then saves and closes.
Of interest, when the worksheet copies from the original workbook, only some of the Power Query queries are copied.

This brings me to the next issue, after the macro has run and appears to remove everything, saves and closes. When I reopen the saved workbook I get this error:

Image 1.jpg

The resulting log file doesn't mean much to me.

Image 3.jpg

When looking in the 'Queries and Connections' pane now, there are 44 queries that were not originally copied (or visible). I think this is part of the cause of this error but I'm guessing at this point.

Has anyone else experienced this behaviour and no how to get around it?
Being presented with this error message when opening these workbooks is not a suitable experience for customers to have to deal with.

My ideal scenario would be having none of the queries, connections, links etc. copied to the new workbook in the first place.
At this point I'll take any suggestions for reproducing the content of the main sheet into a new workbook as long as the layout and conditional formatting remain.

[KimP]

Due to change in requirements I have taken a different approach to this. I have ended up exporting a range and chart as a png. Posting code below in case it is useful to anyone else.

Sub SaveAsPNG()
 
    Dim FName           As String
    Dim FPath           As String
    Dim Path As Range
    
    Set Path = Worksheets("Main").Range("FolderPath")
    FPath = Path & "\"
    FName = Format(Date, "yyyymmdd") & "_FileName.png"
    FPN = FPath & FName
    
        
    'ExportPNG
        
    Dim pic_rng As Range
    Dim ShTemp As Worksheet
    Dim ChTemp As Chart
    Dim PicTemp As Picture
    
    Application.ScreenUpdating = False
    
    ActiveSheet.Shapes.Range(Array("Warning")).Select
    Selection.ShapeRange.IncrementLeft 1626.3529133858
    
    Set pic_rng = Worksheets("Main").Range("Report")
    Set ShTemp = Worksheets.Add
    Charts.Add
    ActiveChart.Location Where:=xlLocationAsObject, Name:=ShTemp.Name
    Set ChTemp = ActiveChart
    pic_rng.CopyPicture Appearance:=xlScreen, Format:=xlPicture
    ChTemp.Paste
    Set PicTemp = Selection


    With ChTemp.Parent
        .Width = PicTemp.Width * 2
        .Height = PicTemp.Height * 2
    End With
    
    PicTemp.Width = ChTemp.Parent.Width - 16
    PicTemp.ShapeRange.IncrementLeft ((ChTemp.Parent.Width - PicTemp.Width) / 2)
    PicTemp.ShapeRange.IncrementTop ((ChTemp.Parent.Height - PicTemp.Height) / 2)
    
    ActiveWindow.Zoom = 200
    ChTemp.Export Filename:=FPN                  
    ActiveWindow.Zoom = 100
    
    Application.DisplayAlerts = False
    ShTemp.Delete
    Application.DisplayAlerts = True
    
    ActiveSheet.Shapes.Range(Array("Warning")).Select
    Selection.ShapeRange.IncrementLeft -1626.3529133858
    
    Application.ScreenUpdating = True
    
   
End Sub

[Bob Phillips]

I don't understand how that addresses your original problem, deleting the queries and connections.