Noob 4 Help - Macro to LIST ALL FILES IN FOLDER and then IMPORT ALL LISTED FILES

Hi All,

I can be lengthy in my effort to try to be clear so I've tried to emphasized the most important parts for faster reading by those that just want to get to it. I would really appreciate any help you can give me as I'm VERY FRUSTRATED at this point. [Disclaimer: Last several days have been my first trying to work with VBA (beyond recording/using recorded macros).]

Ultimately, I'm trying to create a 2-sheet workbook to accomplish the following (using Excel 2007 and 2010):

Sheet 1: create a list of all .txt files within a directory and it's subdirectories.

Sheet 2: Import the entirety of the first file listed on Sheet 1 into Sheet 2, and then import and append each of the remaining files on Sheet 1 to Sheet 2. All files after the first should start on row 2 of each file so not to repeat the header row, however I would like to change the formatting of the first line that starts a new file (or some other means of flagging the start of a new file). All text files have the same field settings and structure.

I've been trying to splice various parts of different macros together but the main two that I've been using are:

1. 21. VBA Tip on ExcelExperts dot com (Sorry, I can't give links in the forum yet)

1. The "Import_All_Text_Files_2007" subroutine given on Ozgrid dot com forum (thread = 155350) (the last code by Rushti on 6/24/2011)

I love how the "list files" works except I can't seem figure out how to modify for just a specific file type--I believe I'm nesting conditionals incorrectly or something. This is where I'm at with that one:

Please Login or Register  to view this content.

And then with the second one I can't show you any of what I've modified...I'm frustrated as hell at this point, but for all of your amusement I'll summarize my day (it may remind you of when you were new at this)..."suddenly" the second macro stopped working this morning (neither the original or any of the versions I'd been modifying)...kept running it, but although it seemed to "run" it didn't do anything. I tried a bunch of things that my novice brain could come up with and searched the web for answers, but several hours went by and I still couldn't figure it out.

My totally VBA-ignorant brain came up with some genius hypotheses , "Maybe I've used the same variable in multiple modules, or created too many variations of the modules, and have confused the system....Or maybe I've screwed something up with the way that VBA modules relate/interact with each other while trying to splice them together." With that in mind, I decided to take the zen approach in lieu of pulling my hair out .

I did what anyone else in my position would do having exhausted all of my options...I took a deep breath and started over. That is, I ERASED ALL OF THE MODULES, INCLUDING MY OWN EFFORTS OVER THE PAST THREE DAYS!!!

I pasted in fresh copies of the subroutines I'd found on the web, and held my breath as I ran the module that had suddenly stopped working after my modifications. It didn't work. New panic. Took me only a moment to realize that in my haste I hadn't entered the directory path as needed. Relief--dodged a bullet. Ran it again. Nothing. What the hell? It didn't come up with any errors, but it also didn't seem to do anything. Spent another hour trying to figure out if I'd copied the wrong macro off the web......finally defeated , I spent about 30 minutes writing a different very long question in this forum about what the hell I could have done to stop these macros from working...I was about to post it when I realized...I'd input the wrong directory path. And the worst part? I NOW also realize that was the problem this morning--BEFORE I'D ERASED ALL OF MY WORK!!! HOW F'ING STUPID!!!

I'd used the path that I'd been testing the 1st module with, which will loop through folders and subfolders, but the second one will only import files from the main folder and all of my .txt files are inside subdirectories--there are no actual files in the main directory so it was returning nothing. HOLY S@*T! Did I really let my frustration make me that careless/stupid?!?

All of this considered, I know that what I'm trying to accomplish would be an easy task for you experts so any help would be much appreciated!!! Even if it's just an encouraging pointer.

Here's one I wrote a while back.
http://www.ozgrid.com/forum/showthread.php?t=157939

Please Login or Register  to view this content.

This will excludes following files:
Hidden file
System file
Hidden System file
Hidden Archived file

Thank you, THANK YOU Jindon!!! I'm going to check it out now--may have questions for you tomorrow if that's okay.

Much thanks!

I've just been made aware of cross-posting netiquette so in that spirit...I'll call my post in VBAX my "original post". I am just trying to access as many experts as I could, but please respond to my VBAX thread.

I'm still trying to get the second part of this Macro accomplished...although I'm still trying to understand Jindon's. Thank you all!

Jindon,

It appears that this module is only searching through immediate subfolders? Can it be modified to search more levels of subfolders?

No, it should search to the last level of subfolders in the specific folder.

I have just tested and it is working.