Remove Query Definition with Macro or VBA

[Paul Simpson]

I have created an excel template (.xlt) that includes a query definition to
retrieve data from our accounts system when a new spreadsheet is created
based on that template. I've created a macro that summarises the data and
formats it ready for printing, but the first thing I want the macro to do is
remove the query definition so that it can't be (accidentally) refreshed and
isn't saved with the excel spreadsheet (.xls). Using the record macro
feature as I clear the "Save query definition" tickbox on Data Range
Properties dialog doesn't create the necessary commands in the macro to do
this. Is there any other way?

Regards

 

[Rob van Gelder]

This deletes all querytables from ActiveSheet

Dim qtb As QueryTable

For Each qtb In ActiveSheet.QueryTables
qtb.Delete
Next

This approach will leave behind the named range for the querytable.

 

[Paul Simpson]

Rob

Thanks. Works a treat.

Paul