PDA

View Full Version : [SOLVED] Deleting Queries and Connections not working in O365 version



KimP
08-04-2019, 04:08 PM
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
08-05-2019, 01:26 AM
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
08-05-2019, 06:31 AM
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
08-05-2019, 12:29 PM
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
08-05-2019, 02:01 PM
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
08-05-2019, 02:40 PM
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
08-06-2019, 01:36 PM
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:

24766

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

24767

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
12-03-2019, 02:05 PM
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
12-05-2019, 05:42 AM
I don't understand how that addresses your original problem, deleting the queries and connections.