Delete Query Tables Macro Code

  • Hello everyone,

    I am currently working on a project that uses Excel to parse a .txt document. Its working quite well for me. It functions by having two worksheets. In the first worksheet I use the "import external data" menu to import my .txt file. In another worksheet I have set up fields that show only the important information from the .txt file and leave the junk behind.

    I’m looking to improve the functionality of this by adding a button to automatically clear the data in the first worksheet so that new data can be added quickly.

    I have searched the forum and found a couple of threads on "QueryTables". After reading up on those, I have made a simple button with the following code.


    After I load a .txt file and parse it using the formulas set up, I copy my needed information and then press this button. The cells clear, and the QueryTables are "reset" (maybe not the right word). Now a different .txt file can be imported and the process starts again.

    The problem I am having is that. If there is no "QueryTables.Item(1)" to delete, I get a run time error. (Run-time error '9': Subscript out of range).

    Would anyone know how to make my button conditional to having a "QueryTable" active? I.E. If I press it when there is no data loaded it doesn't do anything or give me that error.


  • Re: Deleting Querry Tables Only When They Are Active


    and welcome to the forum


    On Error GoTo 10
    End Sub



  • Re: Deleting Querry Tables Only When They Are Active

    Use code tags! (I suspect you will be banned by the time you read this though)

    It depends how good you want to be.
    You could simply add:

    on error resume next

    At the beginning of your macro, this effectively ignores errors and doesn't process the lines which cause them.

    More properly, clear the cells and delete the query tables:

    Dim qTable as QueryTable
    For Each qTable In Sheets("sheet1").QueryTables
    Next qTable


  • Re: Deleting Querry Tables Only When They Are Active

    Thanks Robert and Charlie,

    That worked very well. Its simple and I like it. I find myself using complex things because I don't know how to do it any other way. If I were a bit better using Perl I would just make my own text parser, but I'm not, so I'm doing it the hard way.

    Thanks for the heads up on the code tags. First time using these forums and I was concentrating so hard on titling my post correctly that I totally forgot about them. I have edited my post to have them now. Thanks!

    As for your solution,
    I will be trying it sometime soon because I really want to learn more about excel and VB. Your right, it does depend on how good I want to be. With the limited VB that I know your code looks like its going work great for my uses as well.

    Thank you both,

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!