Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
remove MS Query from Excel | Excel Discussion (Misc queries) | |||
Remove Access query name title from Excel spreadsheet | Excel Discussion (Misc queries) | |||
Scan and remove column data query | Excel Discussion (Misc queries) | |||
Scan and remove column data query | Excel Worksheet Functions | |||
How to remove a query prompt in Excel 2003 | Excel Discussion (Misc queries) |