Importing Multiple Text Files at once

[joyjam]

I want to import a batch of text files.
I have no idea how to do this, and I don't know any of this VBA stuff.
Please Help!

 

[Brothwood]

Try this instead. The code worked OK on my machine before but now I've changed it to specify a text-tab delimited file (rather than let Excel guess what it is). This brings all the text files onto one sheet.

Let me know how you get on,

Dan

Sub GetTextFiles()
Dim lngCounter As Long, wbText As Workbook

On Error GoTo ErrHandler

Application.DisplayAlerts = False
Application.ScreenUpdating = False

With Application.FileSearch
    .NewSearch
    .FileType = msoFileTypeAllFiles
    .LookIn = "C:temptext files"  'Change this to your folder name
    .Execute
    For lngCounter = 1 To .FoundFiles.Count
        If Right(.FoundFiles(lngCounter), 4) = ".txt" Then
            Workbooks.OpenText Filename:=.FoundFiles(lngCounter), tab:=True, DataType:=xlDelimited
            ActiveSheet.UsedRange.Copy
            ActiveWorkbook.Close False
            Range("A" & ActiveSheet.UsedRange.Rows.Count + 1).PasteSpecial
        End If
    Next lngCounter
End With


Application.DisplayAlerts = True
Application.ScreenUpdating = True

Exit Sub

ErrHandler:
Application.ScreenUpdating = True
MsgBox Err.Description, vbExclamation, "Ooops, an error occurred"

End Sub

Sorry to Bump this, but i am trying to do this and cant seem to get it to work, i have ~8000 files i need to dump into a single sheet.

I have tried this code but i am getting an error out the gate, not sure if i have done something wrong or if this is outdated and wont work with newer versions of excel

Any help would be much appreciated.

Regards
Brothwood

 

[smitesh]

Hi Dan, I am facing the error,

"Object doesn't support this Action"

can you help me on this

Try this instead. The code worked OK on my machine before but now I've changed it to specify a text-tab delimited file (rather than let Excel guess what it is). This brings all the text files onto one sheet.

Let me know how you get on,

Dan

Sub GetTextFiles()
Dim lngCounter As Long, wbText As Workbook

On Error GoTo ErrHandler

Application.DisplayAlerts = False
Application.ScreenUpdating = False

With Application.FileSearch
    .NewSearch
    .FileType = msoFileTypeAllFiles
    .LookIn = "C:temptext files"  'Change this to your folder name
    .Execute
    For lngCounter = 1 To .FoundFiles.Count
        If Right(.FoundFiles(lngCounter), 4) = ".txt" Then
            Workbooks.OpenText Filename:=.FoundFiles(lngCounter), tab:=True, DataType:=xlDelimited
            ActiveSheet.UsedRange.Copy
            ActiveWorkbook.Close False
            Range("A" & ActiveSheet.UsedRange.Rows.Count + 1).PasteSpecial
        End If
    Next lngCounter
End With


Application.DisplayAlerts = True
Application.ScreenUpdating = True

Exit Sub

ErrHandler:
Application.ScreenUpdating = True
MsgBox Err.Description, vbExclamation, "Ooops, an error occurred"

End Sub

 

[Fennek]

Hallo,

from the old times of DOS there is a command to combine many files into one. It is very fast:

copy *.txt all.txt

The file "all.txt" xl can import with the usual procedure.

regards