VB way to remove the query from a Query Table?
 

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
--
Toby Erkson
Oregon, USA
Excel 2002 & 2003 in Windows XP

VB way to remove the query from a Query Table?
 

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




--
Regards,
Tom Ogilvy

"Toby Erkson" wrote in message
...
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
--
Toby Erkson
Oregon, USA
Excel 2002 & 2003 in Windows XP

VB way to remove the query from a Query Table?
 

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

--
Air_Cooled_Nu

-----------------------------------------------------------------------
Air_Cooled_Nut's Profile: http://www.excelforum.com/member.php...fo&userid=1573
View this thread: http://www.excelforum.com/showthread.php?threadid=27333

VB way to remove the query from a Query Table?
 

ACN

Sheet1.QueryTables(1).Delete

will delete the link, but not the data

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


--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

"Air_Cooled_Nut" wrote in
message ...

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


--
Air_Cooled_Nut


------------------------------------------------------------------------
Air_Cooled_Nut's Profile:
http://www.excelforum.com/member.php...o&userid=15730
View this thread: http://www.excelforum.com/showthread...hreadid=273339

VB way to remove the query from a Query Table?
 

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

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

"Dick Kusleika" wrote in message
...
ACN

Sheet1.QueryTables(1).Delete

will delete the link, but not the data

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


--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

"Air_Cooled_Nut" wrote in
message ...

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


--
Air_Cooled_Nut


------------------------------------------------------------------------
Air_Cooled_Nut's Profile:
http://www.excelforum.com/member.php...o&userid=15730
View this thread: http://www.excelforum.com/showthread...hreadid=273339