Posts by hatman555

    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

    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