Clear Cell contents That Has a Query
Upcoming Power Excel Seminars
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Clear Cell contents That Has a Query

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Location
    Arkansas
    Posts
    358
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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

  2. #2
    MrExcel MVP Damon Ostrander's Avatar
    Join Date
    Feb 2002
    Location
    Denver, Colorado USA
    Posts
    4,240
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default


    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
    LinkedIn Profile http://www.linkedin.com/pub/damon-ostrander/7/79/a93
    AllExperts Profile http://www.allexperts.com/ep/1059-30...-Ostrander.htm

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sydney, Australia
    Posts
    2,936
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    Arkansas
    Posts
    358
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Location
    Arkansas
    Posts
    358
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  6. #6
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sydney, Australia
    Posts
    2,936
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    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

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com