Importing many text files from same folder

[Nophys]

Hi all,

I have a macro which currently imports a text file, delimits it, and copies specific data from it to add to a summary table of data. As it stands, I have to open each file individually for the macro to be able to pull the data out, and depending on the project, there could be as many as 100 .txt files that I would need to open. All of these text files are in a folder together, and I was wondering if there was some way to have excel loop through all the text files in the folder and import them.

I am currently using this code to assign the .txt file to the variable 'caseName'

caseName = Application.GetOpenFilename("Text Files(*.txt), *.txt", , _

"Select Case File")​

If caseName = False Then

Exit Sub​

Else

' delimiting and copying code here​

So basically, I want the user to be able to select the folder using a browser and have the macro loop through the text files and assign them, one by one, to 'caseName'.

The import currently creates a new spread sheet from caseName, copies the data from specific locations (these never change as the .txt file is always in the same format) to the summary table, then deletes the created spread sheet once the necessary information is acquired.

Any help would be appreciated, and please let me know if you need more information or if there is confusion about what I am trying to accomplish.

Thanks

 

[patel45]

Sub copyAllWbInFolderToSheets1()
    Dim cel As Range
    Dim FileName, ws As Worksheet
    Dim rng As Range
    Dim destWB As Workbook
    Dim pPath As String
    Dim ShellApp As Object
    pPath = "D:\test" ' <<<<<<< to be changed
    I = 1
    Set destWB = ActiveWorkbook
    With CreateObject("scripting.filesystemobject")
      For Each FileName In .GetFolder(pPath).Files
        If InStr(FileName, ".txt") > 0 Then ' <<<<< for txt files
          With Workbooks.Open(FileName)
            .Sheets(1).Copy destWB.Sheets(I)
            I = I + 1
            .Close True
          End With
        End If
      Next
    End With
End Sub