CODEWITHSUNDEEP
vbaexcel
user3519068
user3519068

Reputation: 1

Reading text files with specific prefix

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

Upvotes: 0

Views: 563

Answers (1)

Blackhawk
Blackhawk

Reputation: 6120

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

Upvotes: 1

Related Questions