VB way to remove the query from a Query Table?

[Toby Erkson]

I have a workbook that uses a Query Table. When they click on a button the
workbook is saved, minus various commandbuttons, their VB code, and the
Workbook_Open code (thank you Chip Pearson website!). I also want to make
sure that the Query Table now becomes static, that it does NOT update.

I tried using the macro recorder to capture the code necessary to remove the
Query Table property of the dataset but that was a bust. I do have the
following code that successfully keeps the Query Table from updating itself in
the static workbook:
Sheets("sheetname").QueryTables("querytablename").EnableRefresh = False

Is there a VB way to basically clear the "Save query definition" checkbox in
the "External Data Range Properties" window of a Query Table?

Thanks

 

[Tom Ogilvy]

cells.copy
Cells.PasteSpecial paste:=xlValues

will remove the query table as leave the data.

If you have other formulas on the sheet you don't want to lose, then just do
the area of the query table

With Activesheet.querytables(1).ResultRange
.copy
.pasteSpecial paste:=xlValues
End With

 

[Air_Cooled_Nut]

Thanks Tom. I was thinking of the copy/paste method as well but I wa
hoping for a cleaner way (a method or property). I'll use what yo
suggested :-

 

[Dick Kusleika]

ACN

Sheet1.QueryTables(1).Delete

will delete the link, but not the data

http://www.dicks-clicks.com/excel/ExternalData4.htm#DeleteVBA

 

[Air_Cooled_Nut]

Dick, you da man! Worked great...and thanks for the link

--
Toby Erkson
Oregon, USA
Excel 2002 in Windows XP

ACN

Sheet1.QueryTables(1).Delete

will delete the link, but not the data

http://www.dicks-clicks.com/excel/ExternalData4.htm#DeleteVBA