Import Several Text Files and Copy/Paste One Row to One Cell?

Question

Hello experts.  I’ve got a small VBA script that loops through a bunch of text files and imports all into one single sheet in Excel.  You can see that I get the contents of the file, as well as the Folder and the File Name.  This works ok, except the contents of the file is being split into many cells off to the right.  I’m trying to figure out a way to get every row into one single column, like ColumnD.  How can I do that?

 

 

        With ActiveSheet

       

            ' Loop thru all files in the folder

            For Each File In Folder.Files

           

                ' Open the file as a workbook.

                Workbooks.OpenText File

               

                    'removes tabs

                    MyData = Replace(MyData, vbTab, "")

                    

                    'replaces tabs with LineFeed for wrapping

                    MyData = Replace(MyData, vbTab, Chr(10))

                    Vals = Split(MyData, vbCrLf)

               

                Set cl = .Cells(.Rows.Count, "A").End(xlUp)(2)

                ActiveSheet.UsedRange.Copy cl

                Range(cl, .Cells(.Rows.Count, "A").End(xlUp)).Offset(0, 1).Value = Folder

                Range(cl, .Cells(.Rows.Count, "A").End(xlUp)).Offset(0, 2).Value = File.Name

                ActiveWorkbook.Close False

            Next File

        End With

 

 

I’m trying to split it and recombine it, but it’s not working the way I want it to work.

---

Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

Answer 1

Oh, I see what's happening.  These text files are all tab delimited.  So, as soon as I open them in an Excel Sheet, all the data is already split out. 

Is there an easy way to take the tab delimited date, and copy/paste it so that one line goes into one cell?

---

Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

Answer 2

This worked fine.

        With ActiveSheet
        
            ' Loop thru all files in the folder
            For Each File In Folder.Files
            
               
                FilePath = File
                Open FilePath For Input As #1
                'j = 1
                While EOF(1) = False
                    'read the next line of data in the text file
                    Line Input #1, strLine
                    'print the data in the next empty row
                    LastRowA = Worksheets("Summary").Range("A1048576").End(xlUp).Row + 1
                    j = LastRowA
                    Cells(j, 1) = strLine
                    Cells(j, 2) = Folder
                    Cells(j, 3) = File
                    'increment the row counter
                    j = j + 1
                Wend
                Close #1

            Next File
        End With

Thanks to everyone who took a look!

---

Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

Answer 3

Hi ryguy72,

I'm glad that you have solved this issue.

Thanks for your contribution.

It would be helpful to other community memebers.

Regards,

George.

---

We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
Click HERE to participate the survey.