Clear Cell contents That Has a Query

dantb · Apr 4th, 2002, 01:12 PM

Hi all, trying to have a query delete on workbook close. What is happining is I have a cmd button for retriving a Quiry,but If I re-open the workbook and click the button again, I have double data, so I thought I could just delete the query on the page befroe closing. This is what I have,it hangs on this line
(Selection.QueryTable.Delete)

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Range("B2:I1118").Select
Selection.ClearContents
Selection.QueryTable.Delete
Sheets("Sheet2").Select
Range("D8").Select
End Sub

Damon Ostrander · Apr 4th, 2002, 01:36 PM

Hi Dantb,

It is failing because a QueryTable object is not a sub-object of a range of cells. It is a sub-object of a worksheet. If you only have one QueryTable object on the worksheet, the following should work:

[B2:I1118].ClearContents
ActiveSheet.QueryTables(1).Delete
Sheets("Sheet2").[D8].Select

If there are multiple QueryTables and you are not sure of the index number of the one you want to delete, you can delete it by name (assuming you know it):

ActiveSheet.QueryTables("PartsListQuery").Delete

dk · Apr 4th, 2002, 01:37 PM

Hi,

Rather than deleting your query why don't you just put this code in your command button?

Sheets("YoursheetName").QueryTables(1).Refresh

Please let me know if I've missed the point.

Regards,
Dan

dantb · Apr 4th, 2002, 01:42 PM

Didnt know that about the Objects and sub_object. Always nice to come in here and learn something. Thanks again for your time in this mater,The VB works great !, Thanks again Dan

dantb · Apr 4th, 2002, 01:53 PM

Well DK, this is what is happining. When I click the cmd button, It pulls in the query. I close the workbook out, re-open the workbook, the query is still there,even thou I picked to remove on closing (In the Query). And Then when I re-click the cmd button again, It shifts the first query to the right,and puts the new on the left, so now I have duplicate data. I could fill a page up quick that way.

dk · Apr 4th, 2002, 02:33 PM

Hello Dan,

I'm not sure what's wrong with your code. I have several workbooks at work which refresh queries through VBA using a similar method to the one I posted and they've worked fine for months. Can you post your command button code?

Regards,
Dan

Apr 4th, 2002, 01:36 PM	#2
Damon Ostrander MrExcel MVP Join Date: Feb 2002 Location: Denver, Colorado USA Posts: 4,014	Hi Dantb, It is failing because a QueryTable object is not a sub-object of a range of cells. It is a sub-object of a worksheet. If you only have one QueryTable object on the worksheet, the following should work: [B2:I1118].ClearContents ActiveSheet.QueryTables(1).Delete Sheets("Sheet2").[D8].Select If there are multiple QueryTables and you are not sure of the index number of the one you want to delete, you can delete it by name (assuming you know it): ActiveSheet.QueryTables("PartsListQuery").Delete __________________ Keep Excelling. Damon VBAexpert Excel Consulting (My other life: http://damonostrander.com )

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 4th, 2002, 01:12 PM	#1
dantb Board Regular Join Date: Mar 2002 Location: Arkansas Posts: 358	Hi all, trying to have a query delete on workbook close. What is happining is I have a cmd button for retriving a Quiry,but If I re-open the workbook and click the button again, I have double data, so I thought I could just delete the query on the page befroe closing. This is what I have,it hangs on this line (Selection.QueryTable.Delete) Private Sub Workbook_BeforeClose(Cancel As Boolean) Range("B2:I1118").Select Selection.ClearContents Selection.QueryTable.Delete Sheets("Sheet2").Select Range("D8").Select End Sub

Apr 4th, 2002, 01:37 PM	#3
dk MrExcel MVP Join Date: Feb 2002 Location: Sydney, Australia Posts: 2,908	Hi, Rather than deleting your query why don't you just put this code in your command button? Sheets("YoursheetName").QueryTables(1).Refresh Please let me know if I've missed the point. Regards, Dan

Apr 4th, 2002, 01:42 PM	#4
dantb Board Regular Join Date: Mar 2002 Location: Arkansas Posts: 358	Didnt know that about the Objects and sub_object. Always nice to come in here and learn something. Thanks again for your time in this mater,The VB works great !, Thanks again Dan

Apr 4th, 2002, 01:53 PM	#5
dantb Board Regular Join Date: Mar 2002 Location: Arkansas Posts: 358	Well DK, this is what is happining. When I click the cmd button, It pulls in the query. I close the workbook out, re-open the workbook, the query is still there,even thou I picked to remove on closing (In the Query). And Then when I re-click the cmd button again, It shifts the first query to the right,and puts the new on the left, so now I have duplicate data. I could fill a page up quick that way.

Apr 4th, 2002, 02:33 PM	#6
dk MrExcel MVP Join Date: Feb 2002 Location: Sydney, Australia Posts: 2,908	Hello Dan, I'm not sure what's wrong with your code. I have several workbooks at work which refresh queries through VBA using a similar method to the one I posted and they've worked fine for months. Can you post your command button code? Regards, Dan