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