Syntax to be used for WorkbookQuery.Delete-CodePudding

Microsoft website introduces this command on their website below. It says parameter True can be used. But there is not syntax example.

Where can I add deleteconnection true parameter in the example below

Dim qwry As WorkbookQuery
For Each qwry in Active.Workbook.Queries
qwry.delete
Next

https://learn.microsoft.com/en-us/office/vba/api/excel.workbookquery.delete#return-value

Getting compile error

CodePudding user response：

Not sure exactly, but this old code should work

Sub DeleteEm()
Dim wc As WorkbookConnection
Dim wq As WorkbookQuery
On Error Resume Next
For Each wc In ThisWorkbook.Connections
    wc.Delete
Next
For Each wq In ThisWorkbook.Queries
    wq.Delete
Next
End Sub

CodePudding user response：

You get a compile error most likely because you typed Active.Workbook instead of ActiveWorkbook (ThisWorkbook can also be used).

Now for the DeleteConnection parameter, you can simply make the call like any other sub:

Dim qwry As WorkbookQuery
For Each qwry in ActiveWorkbook.Queries
qwry.Delete True
Next

Note that in cases where several parameters are optional, it can be useful to let VBA know which one(s) you are setting a value for rather than relying on their order in the Sub call. On your example, that would be:

Dim qwry As WorkbookQuery
For Each qwry in ActiveWorkbook.Queries
qwry.Delete DeleteConnection:=True
Next