Urgent help needed to import multiple text files

[bubblerow]

I am sure this is painfully easy, but I simply can not work out how to get this working!

I want to import to a spreadsheet a number of separate text files in separate folders.

The spreadsheet has one column with the name of the text file (column A), and one column with the full path and name of the folder it is contained within (column B).

Is there a macro that will read these text files from within their respective folders and paste the data contained within into the next column (column C)

Eg macro would take [path to folder column B]+[text file name column A] = output of read file into column C

I have about 13000 folders all with similarly named files within and cant do this manually!

Thank you!

 

[id107]

Assuming that your files and folders data starts in row 2, this should work:

Sub ImportTextFiles()
 
    Dim lr As Long, i As Long, MyDir As String, MyFile As String
 
    lr = Range("A" & Rows.Count).End(xlUp).Row
 
    For i = 2 To lr
        MyFile = Range("A" & i).Value
        If Right(Range("B" & i).Value, 1) <> "\" Then
            MyDir = Range("B" & i).Value & "\"
        Else
            MyDir = Range("B" & i).Value
        End If
        If Dir(MyDir & MyFile) = "" Then
            Range("C" & i).Value = "file not found"
        Else
            With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & MyDir & MyFile, Destination:=Range("$C$" & i))
                .Name = Left(MyFile, Len(MyFile) - 4)
                .FieldNames = True
                .TextFilePlatform = 850
                .TextFileStartRow = 1
                .TextFileParseType = xlDelimited
                .TextFileTextQualifier = xlTextQualifierDoubleQuote
                .Refresh BackgroundQuery:=False
            End With
        End If
    Next i
 
End Sub

 

[bubblerow]

Thank you

That imports the data, but I think it is reading some sort of linebreaks as rather than putting the data neatly into column C it pastes it over three rows and then makes multiple columns (when I ended the macro running we were at column N with staggered data all over the place). Is there any way to remove the linebreaks when importing the data so it stays in one cell?

Thanks!!

 

[bubblerow]

To clarify it is now doing the following - on import, each of these files is only about 6 or 7 lines long, but have paragraph returns within them (to divide the text between product description & key features etc). Is there a way to force all the data to stay in a single cell rather than spacing itself out over multiple rows?

 

[bubblerow]

Is there some bit of code I can use to ignore the linebreaks?
Thank you

 

[bubblerow]

Argh, it is now driving me mad - I cant work out how to stop the lines breaking over several rows rather than going into a single cell

Please, someone give me a suggestion before I go mad

 

[bubblerow]

Still not solved this, any help appreciated

 

[bubblerow]

Does anyone know if there is a statement I can use as part of the macro import which will strip all the line breaks out? thank you

 

[bubblerow]

Bump in the hope someone can help me