OK, let's talk about design issues. Treat this as my "brain dump" on a narrow design question, intended to help you see how to approach this sort of thing.
You import stuff to a table and then append it to a main table and get rid of the leftovers. OK, perfectly understandable. But where the question comes into play is this:
Between the time that you import data and the time that you do the append, what else happens? That is where I have a "concept gap" and don't know the purpose of going through the process here. Are you doing some filtration? Some reformatting? Are you checking for errors? What is happening to the data in the temporary tables before you do that final append?
Second part of that: Your code is importing multiple tables at a time (I saw four at a time in an earlier post). Why do you need that many tables simultaneously resident? Is there a comparison step here? A reconciliation? An elimination of duplicates? What happens that you need so many tables at once? If there is no action that involves having multiple tables open at once for comparison, cross-table filtering, etc. then use ONE temp table and do these linearly. There is NO advantage (and potentially a source of confusion) to having multiple tables open when you don't do anything with them in aggregate.
These kinds of questions would be answered by having a specific design goal in mind. An incredibly important part of any project design is to know BEFORE YOU WRITE THE FIRST LINE OF CODE (or before you create the first form) - what are you doing? And you need to be able to answer that in some detail. Think of the design as a "road map" and you are on a journey. Without a road map, how will you ever know that you have reached your destination? For ALL of my own major projects, the very first thing I ever did was use WORD (not Excel or Access) to build a "design bible" that answered all of the important questions and presented all of the important goals - including criteria for knowing that I had actually REACHED the goals.
There is an old carpenter's saying that my father-in-law used to say regarding due diligence on any work project: Measure twice, cut once. We on this forum ALL know in the most personal and intimate detail that there is this incredible sense of the need to make things happen, to show progress, to get results. But we have to learn to counterbalance this urge with the incredibly important idea to prevent ourselves from having too many mistakes that will require rework. And like another old saying, "If you don't have enough time to do it right the first time, how will you EVER find enough time to fix it when it breaks?"
Having a design in hand (and paper is irreplaceable for this purpose), you can tell where you are going and can tell when something is a misstep. It saves your sanity. Almost literally!
Now, having said all of that, let me commend you for something.
I have seen some of my colleague on daily basis spends almost 1 to 2 hours on daily basis first to convert the text file to excel and then balance the excel and then balance with other docs. This is prone to errors due to the value involved in the field.
Bravo and kudos to you for recognizing that you have this absolutely phenomenal resource on your desktop that can help you do your job better! That machine wasn't bought as a paperweight; it was bought as an office productivity tool and YOU have seen a way to improve your productivity.
I will also point out that your potentially improved job performance WILL NOT HURT YOU ONE BIT at your next performance review.
Just be warned: Seeing how to make things better - and then actually doing something about it - is how you get promoted and get thrown into the arena to tame many wild office beasts, not just the one you have in front of you right now. But if that idea appeals to you, go for it!
