Answered by:
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.
Tuesday, September 9, 2014 1:53 PM
Answers
-
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.
- Edited by ryguy72 Tuesday, September 9, 2014 6:46 PM
- Proposed as answer by George Hua Wednesday, September 10, 2014 3:37 AM
- Marked as answer by George Hua Tuesday, September 16, 2014 9:17 AM
Tuesday, September 9, 2014 6:46 PM
All replies
-
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.
Tuesday, September 9, 2014 2:04 PM -
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.
- Edited by ryguy72 Tuesday, September 9, 2014 6:46 PM
- Proposed as answer by George Hua Wednesday, September 10, 2014 3:37 AM
- Marked as answer by George Hua Tuesday, September 16, 2014 9:17 AM
Tuesday, September 9, 2014 6:46 PM -
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.Wednesday, September 10, 2014 3:37 AM