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

[StlSmiln]

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, but the two main macros I've been working with are:

List Files in a Folder
Import All Txt Files 2007 (the last major block of code from 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:

Sub ListMyFiles(mySourcePath, IncludeSubfolders)
    Set MyObject = New Scripting.FileSystemObject
    Set mySource = MyObject.GetFolder(mySourcePath)
    On Error Resume Next
    [B]If InStr(MyFile.Path, ".txt") <> 0 Then[/B]
        For Each MyFile In mySource.Files
       
            iCol = 2
            Cells(iRow, iCol).Value = MyFile.Path
            iCol = iCol + 1
            Cells(iRow, iCol).Value = MyFile.Name
            iCol = iCol + 1
            Cells(iRow, iCol).Value = MyFile.Size
            iCol = iCol + 1
            Cells(iRow, iCol).Value = MyFile.DateLastModified
            iRow = iRow + 1
         Next
    End If
    Columns("C:E").AutoFit
    If IncludeSubfolders Then
        For Each mySubFolder In mySource.SubFolders
            Call ListMyFiles(mySubFolder.Path, True)
        Next
    End If
End Sub

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.

 

[taurean]

Welcome to MrExcel.

You have cross posted this on VBAX here. And you are in good hands.

Please read this article.
http://www.excelguru.ca/content.php?184

 

[StlSmiln]

Thanks, and thanks for the netiquette! I didn't know there was a specific way of cross-posting, but I get it.

 

[taurean]

It happens when you are a new member. Keep visiting here, you'll surely get good help and advice on Excel usage and issues.