Tuesday, September 18, 2012 10:06 PM
Hello, I am very new to VBA (Excel). I was wondering if someone could start me off on the right track on extracting data from a text file. Well, actually, it is a PRF file that I open in Monarch Explorer and export it as a text file.
An example is below:
DPR# BC800-00 ASSN 04 CORP 099999 INST BANKCARD DAILY ACTIVITY JOURNAL BY PLAN AS OF 09/17/12 PAGE 404
BIN 123456789 EXAMPLE FEDERAL CREDIT UNION RUN DATE 09/17/12
TOTAL OUTSTANDING BALANCES - BEGINNING $74,786.02
RECOVERY OUTSTANDING BALANCES - BEGINNING $0.00
NON-SYSTEM GENERATED TRANSACTIONS - POSTED
TRAN CODE REASON CODE DESCRIPTION COUNT $ AMOUNT
05 00 SALES DRAFT 13 $1,040.49
65 00 PAYMENT - THANK YOU 6 $713.55-
65 35 ON-LINE PAYMENT-THANK YO 3 $743.00-
TOTAL NON-SYSTEM GENERATED TRANSACTIONS - POSTED 22 $416.06-
TOTAL NON-SYSTEM GENERATED TRANSACTIONS 22 $416.06-
PLAN TOTAL TRANSACTIONS - POSTED 22 $416.06-
PLAN TOTAL TRANSACTIONS - UNPOSTED 0 $0.00
PLAN TOTAL TRANSACTIONS - RECOVERY 0 $0.00
PLAN TOTAL TRANSACTIONS 22 $416.06-
TOTAL OUTSTANDING BALANCES - ENDING $74,369.96
RECOVERY OUTSTANDING BALANCES - ENDING $0.00
I would like to be able to extract BIN 123456789. I would also like to extract the $416.06- from the row that contains PLAN TOTAL TRANSACTIONS (the 4th one). The info isn't always in the same area. I would be doing this for multiple BINs, so if someone could start me in the right direction, that would be great.
Tuesday, September 18, 2012 11:17 PMAnswerer
Use "open file for" method and grab line with "BIN" using function instr()
and last line with "PLAN TOTAL TRANSACTIONS". To cut words you can use Split() function.
How to do that you can read in Excels Help.
Sub pobierz() Const sFile$ = "c:\temp\fcu.txt" '<- your file (change this line) Dim strLine$, f%: f = FreeFile Dim result$ Open sFile For Input As #f Do While Not EOF(f) Line Input #f, strLine If InStr(1, strLine, "BIN") > 0 Then result = Trim(Split(strLine, "EXAMPLE")(0)) & vbCr If InStr(1, strLine, "PLAN TOTAL TRANSACTIONS ") > 0 Then result = result & Trim(Split(strLine, "$")(1)) Loop Close #f MsgBox result, vbInformation , "VBATools.pl" End Sub
Oskar Shon, Office System MVP
Press if Helpful; Answer when a problem solved