Clear Cell contents That Has a Query

dantb

Active Member
Joined
Mar 20, 2002
Messages
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
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top