Reading text files with specific prefix

Question

I have a folder with lots of text files each containing (but in random order) :

A = ...
B = ...
C = ...

Now I would like to import these text files into an excel-spreadsheet, where each of the prefixes is organized in the colums, and the files are listed as rows

Example: 2 files

File 1:

A = 1     
B = 2
C = 3

File 2:

A = 4     
B = 5
C = 6

I would the excel to look like :

NR / A / B / C

1 / 1 /2 /3

2 / 4/ 5 /6

I am still learning VB, and this is just a bit over the top for me.

I have found a macro like this:

Sub Read_Text_Files()
Dim sPath As String, sLine As String
Dim oPath As Object, oFile As Object, oFSO As Object
Dim r As Long

'Files location
sPath = "C:\Test\"

r = 1
Set oFSO = CreateObject( _
"Scripting.FileSystemObject")

Set oPath = oFSO.GetFolder(sPath)
Application.ScreenUpdating = False

For Each oFile In oPath.Files
If LCase(Right(oFile.Name, 4)) = ".txt" Then

Open oFile For Input As #1 ' Open file for input.
Do While Not EOF(1) ' Loop until end of file.

Input #1, sLine ' Read data
If Left(sLine, 1) = "A=" Then 'Now i need to write this to the first column of that row
If Left(sLine, 1) = "B=" Then 'For the second column.

Range("A" & r).Formula = sLine ' Write data line

r = r + 1
Loop
Close #1 ' Close file.

End If
Next oFile

Application.ScreenUpdating = True
End Sub

Brettdj's great example about reading files from folder here ..http://stackoverflow.com/questions/10380312/loop-through-files-in-a-folder-using-vba — Sathish Kothandam, Apr 10 '14 at 14:22

score 1 · Answer 1 · edited May 23 '17 at 10:33

Do you know how to open files in VBA for reading using syntax like Open and Line Input?

If not, read this: https://stackoverflow.com/a/11528932/2832561 I found this by googling for "VBA open file read"

Do you know how to work with and parse strings (and arrays) using functions like Mid, Left, Right, Split and Join?

If not, try reading this: http://www.exceltrick.com/formulas_macros/vba-split-function/ I found this by googling for "VBA String functions parse text"

Do you know how to work with Workbook and Worksheet objects and assign values to Range objects in Excel?

If not, try reading this: http://www.anthony-vba.kefra.com/vba/vbabasic2.htm I found this by googling for "Workbook Worksheet Range VBA"

Once you have had a chance to try putting together a solution using these pieces, you can post specific questions on any issues you run into.

Reading text files with specific prefix

1 Answers1