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

  • 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.


    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.


    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
    Moderator