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.


    Code
    Sheets("Sheet1").Cells.Clear
Sheets("Sheet1").QueryTables.Item(1).Delete


    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.


    Thanks,
    Dave

    Re: Deleting Querry Tables Only When They Are Active


    Hi


    and welcome to the forum


    try


    Code
    On Error GoTo 10
Sheets("Sheet1").Cells.Clear
Sheets("Sheet1").QueryTables.Item(1).Delete
10
End Sub


    HTH


    Robert

    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:

    Code
    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:

    Code
    Dim qTable as QueryTable
Cells.ClearContents
For Each qTable In Sheets("sheet1").QueryTables
    qTable.Delete
Next qTable


    HTH

    Re: Deleting Querry Tables Only When They Are Active


    Thanks Robert and Charlie,


    Rob,
    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.


    Charlie,
    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,
    Dave

Participate now!

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

Register Yourself Login