CODEWITHSUNDEEP
excelvba
GeTee
GeTee

Reputation: 11

VBA Script to import text files into single excel spreadsheet

I'm looking to create a VBA button on a spreadsheet to import all text files into that spreadsheet.

I'd ideally like the filename written to column 1 and text file contents to column 2. The text files contain a single line of information.

I have the contents working below but can't figure out how to get the file name next to that.

    Sub ReadFilesIntoActiveSheet()
    Dim fso As FileSystemObject
    Dim folder As folder
    Dim file As file
    Dim FileText As TextStream
    Dim TextLine As String
    Dim Items() As String
    Dim i As Long
    Dim cl As Range

    ' Get a FileSystem object
    Set fso = New FileSystemObject

    ' get the directory you want
    Set folder = fso.GetFolder("C:\DataExport")

    ' set the starting point to write the data to
    Set cl = ActiveSheet.Cells(2, 2)

    ' Loop thru all files in the folder
    For Each file In folder.Files
        ' Open the file
        Set FileText = file.OpenAsTextStream(ForReading)

        ' Read the file one line at a time
        Do While Not FileText.AtEndOfStream
            TextLine = FileText.ReadLine

            ' Parse the line into | delimited pieces
            Items = Split(TextLine, "|")

            ' Put data on one row in active sheet
            For i = 0 To UBound(Items)
                cl.Offset(0, i).Value = Items(i)
            Next

            ' Move to next row
            Set cl = cl.Offset(1, 0)
        Loop

        ' Clean up
        FileText.Close
    Next file

    Set FileText = Nothing
    Set file = Nothing
    Set folder = Nothing
    Set fso = Nothing

End Sub

Upvotes: 0

Views: 136

Answers (1)

Kajkrow
Kajkrow

Reputation: 304

Yo,

I hope it is as you wanted it:

Sub ReadFilesIntoActiveSheet()

Dim fso As FileSystemObject
Dim folder As folder
Dim file As file
Dim FileText As TextStream
Dim TextLine As String
Dim Items() As String
Dim i As Long
Dim cl As Range

' Get a FileSystem object
Set fso = New FileSystemObject

' get the directory you want
Set folder = fso.GetFolder("C:\DataExport")

' set the starting point to write the data to
Set cl = ActiveSheet.Cells(2, 1)

' Loop thru all files in the folder
For Each file In folder.Files
    ' Write file-name
    cl.Value = file.Name

    ' Open the file
    Set FileText = file.OpenAsTextStream(ForReading)

    ' Read the file one line at a time
    Do While Not FileText.AtEndOfStream
        TextLine = FileText.ReadLine

        ' Parse the line into | delimited pieces
        Items = Split(TextLine, "|")

        ' Put data on one row in active sheet
        For i = 0 To UBound(Items)
            cl.Offset(0, 1 + i).Value = Items(i)
        Next

        ' Move to next row
        Set cl = cl.Offset(1, 0)
    Loop

    ' Clean up
    FileText.Close
Next file

Set FileText = Nothing
Set file = Nothing
Set folder = Nothing
Set fso = Nothing

End Sub

Upvotes: 2

Related Questions