Extract specific values in text files and paste to Excel

[swaggerbox]

I have several text files in the path: C:\MyReports. I need to extract the total billable number of records per invoice type (the same structure for all text files) and paste them to excel. For example, in the text file below for Invoice type XXXXXABC, I need to input 12 in column B row 2 (row 1 contains the invoice type heading, e.g. XXXXXABC, XXXXXABD, XXXXXABE, etc.), 6 in column C (XXXXXABD), 1 in column D (XXXXXABE), 0 in column E (XXXXXABF) and 0 in column F (XXXXXABG). Any ideas?


INVOICE # : XXXXXABC
TOTAL NUMBER OF
BILLABLE RECORDS : 12

INVOICE # : XXXXXABD
TOTAL NUMBER OF
BILLABLE RECORDS : 6

INVOICE # : XXXXXABE
TOTAL NUMBER OF
BILLABLE RECORDS : 1

INVOICE # : XXXXXABF
TOTAL NUMBER OF
BILLABLE RECORDS : 0

INVOICE # : XXXXXABG
TOTAL NUMBER OF
BILLABLE RECORDS : 0

[JKwan]

give this a spin

Sub blah()
sn = Filter(Split(CreateObject("scripting.filesystemobject").opentextfile( _
"C:\MyReports\Invoice.txt").readall, vbCrLf), "BILLABLE RECORDS")
j = 2
For i = 0 To UBound(sn)
Cells(2, j) = Split(sn(i), ":")(1)
j = j + 1
Next i
End Sub

[swaggerbox]

thanks JK

[snb]

Sub M_snb()
sn = filter(split(join(Filter(Split(CreateObject("scripting.filesystemobject").opentextfile("C:\MyReports\Invoice.txt").readall, vbCrLf), "BILLABLE RECORDS")," : ")," : "),"B",0)
cells(2,2).resize(,ubound(sn))=sn
End Sub