Vba to loop on a text file and extract data into excel ( reading text file from vba)

K

kuldeepsingh25

New Member
Joined
Oct 23, 2018
Messages
1
Hi John_w,

I want to READ the String "TREASURY REFERENCE NUMBER: " when this string found, I want to extract columns, below 14 lines from the string , data in excel.Sample text file , required output in excel sample and the code I am trying is as follows:
TextFile:[CODE---+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9----+----10---+----11---+----12---+----13--
===== NEW PAGE ========
RUN DATE: 02/09/2018 PAGE NO: 1
INVOICE NUMBER - VOUCHER NUMBER CONVERSION REPORT
ADVICE REMITTANCE INVOICE VOUCHER TREASURY BLANKET CURR REMITTANCE
NUMBER DATE NUMBER NUMBER REFERENCE PO NUMBER CODE AMOUNT
--------- ---------- -------------------- ---------------- --------- --------- ---- ----------------
000040181 06/09/2018 92244597 20180605892016 000029122 B663865 GBP
92244598 20180605892017 000029122 B663865 GBP
92244599 20180605892018 000029122 B663865 GBP
92244600 20180605892019 000029122 B663865 GBP
92244601 20180605892021 000029122 B663865 GBP
92244602 20180605892022 000029122 B663865 GBP
92244593 20180605892023 000029122 B663865 GBP
92244594 20180605892024 000029122 B663865 GBP
92244595 20180605892025 000029122 B663865 GBP
92244596 20180605892026 000029122 B663865 GBP
92244590 20180605892027 000029122 B663865 GBP
92244591 20180605892028 000029122 B663865 GBP
92244591 20180605892028 000029122 B663865 GBP
92244592 20180605892029 000029122 B663865 GBP
92244589 20180605892030 000029122 B663865 GBP 49446.43
000040182 05/09/2018 9992221539-1 20180805923649 000029297 B674636A8 USD
9992220423-1 20180805923650 000029297 B674636A8 USD
9992221536-1 20180805923658 000029297 B674636A8 USD
9992221544-1 20180805923664 000029297 B674636A8 USD
9992223597-1 20180805923670 000029297 B674636A8 USD
9992223596-1 20180805923692 000029297 B674636A8 USD
9992223554-1 20180805923698 000029297 B674636A8 USD
9992221577-1 20180805923713 000029297 B674636A8 USD
9992220431-1 20180805924036 000029297 B674636A8 USD
9992221542-1 20180805924045 000029297 B674636A8 USD
9992221530-1 20180805924048 000029297 B674636A8 USD 1264321.00
000040183 05/09/2018 9992223554-1 20180805923698 000029297 B674636A8 GBP 27161.67
000040184 06/09/2018 6479006489 20180605891451 000029110 B8006435 GBP 12000.00
000040185 06/09/2018 1003502 20180605891494 000029154 B8006592B USD 35200.00
1003485 20180605891473 000029154 B8008923B USD
1003485 20180605891473 000029154 B8008923B USD
1003486 20180605891478 000029154 B8008923B USD
1003487 20180605891479 000029154 B8008923B USD
1003488 20180605891480 000029154 B8008923B USD
1003489 20180605891481 000029154 B8008923B USD
1003490 20180605891482 000029154 B8008923B USD
1003491 20180605891483 000029154 B8008923B USD
1003492 20180605891484 000029154 B8008923B USD
1003493 20180605891485 000029154 B8008923B USD
1003494 20180605891486 000029154 B8008923B USD
1003495 20180605891487 000029154 B8008923B USD
1003496 20180605891488 000029154 B8008923B USD
1003497 20180605891489 000029154 B8008923B USD
1003498 20180605891490 000029154 B8008923B USD
1003499 20180605891491 000029154 B8008923B USD
1003500 20180605891492 000029154 B8008923B USD
1003501 20180605891493 000029154 B8008923B USD 468705.00
*** END OF REPORT ***
*** END OF REPORT ***
===== NEW PAGE ========
RUN DATE: 02/09/2018 REMITTANCE ADVICE PAGE 1 OF 1

TREASURY REFERENCE NUMBER: 000029122
REMITTANCE ADVICE NUMBER : 000040181
REMITTANCE DATE: 06/09/2018
CURRENCY: GBP

SUPPLIER CODE: V243
SUPPLIER NAME AND ADDRESS
GUSTAV KLAUKE GMBH
AUF DEM KNAPP 46

REMSCHEID XXX XXX
GERMANY
ORDER POINT:

INV/CR INV/CR BLANKET

INV/CR INV/CR BLANKET
NUMBER DATE PO NUMBER CHARGE TYPE AMOUNT
-------------------- ---------- ---------------- -------------------------- -------------------
92244599 05/06/2018 B663865 NET VALUE 462.80
92244595 05/06/2018 B663865 NET VALUE 16339.18
92244589 05/06/2018 B663865 NET VALUE 1103.60
92244596 05/06/2018 B663865 NET VALUE 10551.32
92244598 05/06/2018 B663865 NET VALUE 213.60
92244591 05/06/2018 B663865 NET VALUE 2456.40
92244593 05/06/2018 B663865 NET VALUE 1778.00
92244590 05/06/2018 B663865 NET VALUE 61.64
92244600 05/06/2018 B663865 NET VALUE 1798.60
92244592 05/06/2018 B663865 NET VALUE 3087.00
92244601 05/06/2018 B663865 NET VALUE 2218.00
92244597 05/06/2018 B663865 NET VALUE 2182.84
92244602 05/06/2018 B663865 NET VALUE 644.93
92244594 05/06/2018 B663865 NET VALUE 6548.52

REMITTANCE AMOUNT (GBP) 49446.43

REMITTANCE AMOUNT (GBP) 49446.43
BT Financial Accounting and Billing
Accounts Payable Centre PO Box 998 Manchester M60 1GT
Freephone 0800 515465
===== NEW PAGE ========
RUN DATE: 02/09/2018 REMITTANCE ADVICE PAGE 1 OF 1

TREASURY REFERENCE NUMBER: 000029297
REMITTANCE ADVICE NUMBER : 000040182
REMITTANCE DATE: 05/09/2018
CURRENCY: USD

SUPPLIER CODE: V473
SUPPLIER NAME AND ADDRESS
CISCO INTERNATIONAL LTD
9-11 NEW SQUARE PARK
BENFONT LAKES
FELTHAM TW14 8HA
FELTHAM TW14 8HA
UNITED KINGDOM
ORDER POINT:

INV/CR INV/CR BLANKET
NUMBER DATE PO NUMBER CHARGE TYPE AMOUNT
-------------------- ---------- ---------------- -------------------------- -------------------
9992221539-1 28/07/2018 B674636A8 NET VALUE 297400.00
9992221577-1 28/07/2018 B674636A8 NET VALUE 45918.00
9992221530-1 28/07/2018 B674636A8 NET VALUE 24600.00
9992221536-1 28/07/2018 B674636A8 NET VALUE 157133.00
9992221544-1 28/07/2018 B674636A8 NET VALUE 104755.00
9992221542-1 28/07/2018 B674636A8 NET VALUE 26298.00
9992223597-1 28/07/2018 B674636A8 NET VALUE 72135.00
9992223596-1 28/07/2018 B674636A8 NET VALUE 57646.00
9992220423-1 28/07/2018 B674636A8 NET VALUE 262440.00
9992223554-1 28/07/2018 B674636A8 NET VALUE 177909.00
9992220431-1 28/07/2018 B674636A8 NET VALUE 38087.00

REMITTANCE AMOUNT (USD) 1264321.00

REMITTANCE AMOUNT (USD) 1264321.00
BT Financial Accounting and Billing
Accounts Payable Centre PO Box 998 Manchester M60 1GT
Freephone 0800 515465
===== NEW PAGE ========
RUN DATE: 02/09/2018 REMITTANCE ADVICE PAGE 1 OF 1

TREASURY REFERENCE NUMBER: 000029297
REMITTANCE ADVICE NUMBER : 000040183
REMITTANCE DATE: 05/09/2018
CURRENCY: GBP

SUPPLIER CODE: V473
SUPPLIER NAME AND ADDRESS
CISCO INTERNATIONAL LTD
9-11 NEW SQUARE PARK
BENFONT LAKES
FELTHAM TW14 8HA
FELTHAM TW14 8HA
UNITED KINGDOM
ORDER POINT:

INV/CR INV/CR BLANKET
NUMBER DATE PO NUMBER CHARGE TYPE AMOUNT
-------------------- ---------- ---------------- -------------------------- -------------------
9992223554-1 28/07/2018 B674636A8 VAT VALUE 27161.67
][/CODE]

OuputExcel File:Code:
[TABLE="class: cms_table"]
<tbody>[TR]
[TD="width: 202, bgcolor: transparent"]TREASURY REFERENCE NUMBER[/TD]

[TD="width: 86, bgcolor: transparent"]INVOICE_NO[/TD]

[TD="width: 99, bgcolor: transparent"]INVOICE_DATE[/TD]

[TD="width: 86, bgcolor: transparent"]PO_NUMBER[/TD]

[TD="width: 94, bgcolor: transparent"]CHARGE_TYPE[/TD]

[TD="width: 78, bgcolor: transparent"]AMOUNT[/TD]

[/TR]

[TR]
[TD="bgcolor: transparent"]29297[/TD]

[TD="bgcolor: transparent"]9992221539-1[/TD]

[TD="bgcolor: transparent"]28/07/2018[/TD]

[TD="bgcolor: transparent"]B674636A8[/TD]

[TD="bgcolor: transparent"]NET VALUE[/TD]

[TD="bgcolor: transparent"]297400[/TD]

[/TR]

[TR]
[TD="bgcolor: transparent"]29297[/TD]

[TD="bgcolor: transparent"]9992221577-1[/TD]

[TD="bgcolor: transparent"]28/07/2018[/TD]

[TD="bgcolor: transparent"]B674636A8[/TD]

[TD="bgcolor: transparent"]NET VALUE[/TD]

[TD="bgcolor: transparent"]45918[/TD]

[/TR]

[TR]
[TD="bgcolor: transparent"]29297[/TD]

[TD="bgcolor: transparent"]9992221530-1[/TD]

[TD="bgcolor: transparent"]28/07/2018[/TD]

[TD="bgcolor: transparent"]B674636A8[/TD]

[TD="bgcolor: transparent"]NET VALUE[/TD]

[TD="bgcolor: transparent"]24600[/TD]

[/TR]

[TR]
[TD="bgcolor: transparent"]29297[/TD]

[TD="bgcolor: transparent"]9992221536-1[/TD]

[TD="bgcolor: transparent"]28/07/2018[/TD]

[TD="bgcolor: transparent"]B674636A8[/TD]

[TD="bgcolor: transparent"]NET VALUE[/TD]

[TD="bgcolor: transparent"]157133[/TD]

[/TR]

[TR]
[TD="bgcolor: transparent"]29297[/TD]

[TD="bgcolor: transparent"]9992221544-1[/TD]

[TD="bgcolor: transparent"]28/07/2018[/TD]

[TD="bgcolor: transparent"]B674636A8[/TD]

[TD="bgcolor: transparent"]NET VALUE[/TD]

[TD="bgcolor: transparent"]104755[/TD]

[/TR]

[TR]
[TD="bgcolor: transparent"]29297[/TD]

[TD="bgcolor: transparent"]9992221542-1[/TD]

[TD="bgcolor: transparent"]28/07/2018[/TD]

[TD="bgcolor: transparent"]B674636A8[/TD]

[TD="bgcolor: transparent"]NET VALUE[/TD]

[TD="bgcolor: transparent"]26298[/TD]

[/TR]

[TR]
[TD="bgcolor: transparent"]29297[/TD]

[TD="bgcolor: transparent"]9992223597-1[/TD]

[TD="bgcolor: transparent"]28/07/2018[/TD]

[TD="bgcolor: transparent"]B674636A8[/TD]

[TD="bgcolor: transparent"]NET VALUE[/TD]

[TD="bgcolor: transparent"]72135[/TD]

[/TR]

[TR]
[TD="bgcolor: transparent"]29297[/TD]

[TD="bgcolor: transparent"]9992223596-1[/TD]

[TD="bgcolor: transparent"]28/07/2018[/TD]

[TD="bgcolor: transparent"]B674636A8[/TD]

[TD="bgcolor: transparent"]NET VALUE[/TD]

[TD="bgcolor: transparent"]57646[/TD]

[/TR]

[TR]
[TD="bgcolor: transparent"]29297[/TD]

[TD="bgcolor: transparent"]9992220423-1[/TD]

[TD="bgcolor: transparent"]28/07/2018[/TD]

[TD="bgcolor: transparent"]B674636A8[/TD]

[TD="bgcolor: transparent"]NET VALUE[/TD]

[TD="bgcolor: transparent"]262440[/TD]

[/TR]

[TR]
[TD="bgcolor: transparent"]29297[/TD]

[TD="bgcolor: transparent"]9992223554-1[/TD]

[TD="bgcolor: transparent"]28/07/2018[/TD]

[TD="bgcolor: transparent"]B674636A8[/TD]

[TD="bgcolor: transparent"]NET VALUE[/TD]

[TD="bgcolor: transparent"]177909[/TD]

[/TR]

[TR]
[TD="bgcolor: transparent"]29297[/TD]

[TD="bgcolor: transparent"]9992220431-1[/TD]

[TD="bgcolor: transparent"]28/07/2018[/TD]

[TD="bgcolor: transparent"]B674636A8[/TD]

[TD="bgcolor: transparent"]NET VALUE[/TD]

[TD="bgcolor: transparent"]38087[/TD]

[/TR]

[TR]
[TD="bgcolor: transparent"]29154[/TD]

[TD="bgcolor: transparent, align: right"]1003502[/TD]

[TD="bgcolor: transparent"]23/05/2018[/TD]

[TD="bgcolor: transparent"]B8006592B[/TD]

[TD="bgcolor: transparent"]NET VALUE[/TD]

[TD="bgcolor: transparent"]35200[/TD]

[/TR]

[TR]
[TD="bgcolor: transparent"]29154[/TD]

[TD="bgcolor: transparent, align: right"]1003485[/TD]

[TD="bgcolor: transparent"]23/05/2018[/TD]

[TD="bgcolor: transparent"]B8008923B[/TD]

[TD="bgcolor: transparent"]NET VALUE[/TD]

[TD="bgcolor: transparent"]22420[/TD]

[/TR]

[TR]
[TD="bgcolor: transparent"]29154[/TD]

[TD="bgcolor: transparent, align: right"]1003486[/TD]

[TD="bgcolor: transparent"]23/05/2018[/TD]

[TD="bgcolor: transparent"]B8008923B[/TD]

[TD="bgcolor: transparent"]NET VALUE[/TD]

[TD="bgcolor: transparent"]24440[/TD]

[/TR]

[TR]
[TD="bgcolor: transparent"]29154[/TD]

[TD="bgcolor: transparent, align: right"]1003487[/TD]

[TD="bgcolor: transparent"]23/05/2018[/TD]

[TD="bgcolor: transparent"]B8008923B[/TD]

[TD="bgcolor: transparent"]NET VALUE[/TD]

[TD="bgcolor: transparent"]25810[/TD]

[/TR]

[TR]
[TD="bgcolor: transparent"]29154[/TD]

[TD="bgcolor: transparent, align: right"]1003488[/TD]

[TD="bgcolor: transparent"]23/05/2018[/TD]

[TD="bgcolor: transparent"]B8008923B[/TD]

[TD="bgcolor: transparent"]NET VALUE[/TD]

[TD="bgcolor: transparent"]11800[/TD]

[/TR]

[TR]
[TD="bgcolor: transparent"]29154[/TD]

[TD="bgcolor: transparent, align: right"]1003489[/TD]

[TD="bgcolor: transparent"]23/05/2018[/TD]

[TD="bgcolor: transparent"]B8008923B[/TD]

[TD="bgcolor: transparent"]NET VALUE[/TD]

[TD="bgcolor: transparent"]18800[/TD]

[/TR]

[TR]
[TD="bgcolor: transparent"]29154[/TD]

[TD="bgcolor: transparent, align: right"]1003490[/TD]

[TD="bgcolor: transparent"]23/05/2018[/TD]

[TD="bgcolor: transparent"]B8008923B[/TD]

[TD="bgcolor: transparent"]NET VALUE[/TD]

[TD="bgcolor: transparent"]58003[/TD]

[/TR]

[TR]
[TD="bgcolor: transparent"]29154[/TD]

[TD="bgcolor: transparent, align: right"]1003491[/TD]

[TD="bgcolor: transparent"]23/05/2018[/TD]

[TD="bgcolor: transparent"]B8008923B[/TD]

[TD="bgcolor: transparent"]NET VALUE[/TD]

[TD="bgcolor: transparent"]12750[/TD]

[/TR]

[TR]
[TD="bgcolor: transparent"]29154[/TD]

[TD="bgcolor: transparent, align: right"]1003492[/TD]

[TD="bgcolor: transparent"]23/05/2018[/TD]

[TD="bgcolor: transparent"]B8008923B[/TD]

[TD="bgcolor: transparent"]NET VALUE[/TD]

[TD="bgcolor: transparent"]10740[/TD]

[/TR]

[TR]
[TD="bgcolor: transparent"]29154[/TD]

[TD="bgcolor: transparent, align: right"]1003493[/TD]

[TD="bgcolor: transparent"]23/05/2018[/TD]

[TD="bgcolor: transparent"]B8008923B[/TD]

[TD="bgcolor: transparent"]NET VALUE[/TD]

[TD="bgcolor: transparent"]23826[/TD]

[/TR]

[TR]
[TD="bgcolor: transparent"]29154[/TD]

[TD="bgcolor: transparent, align: right"]1003494[/TD]

[TD="bgcolor: transparent"]23/05/2018[/TD]

[TD="bgcolor: transparent"]B8008923B[/TD]

[TD="bgcolor: transparent"]NET VALUE[/TD]

[TD="bgcolor: transparent"]11400[/TD]

[/TR]

[TR]
[TD="bgcolor: transparent"]29154[/TD]

[TD="bgcolor: transparent, align: right"]1003495[/TD]

[TD="bgcolor: transparent"]23/05/2018[/TD]

[TD="bgcolor: transparent"]B8008923B[/TD]

[TD="bgcolor: transparent"]NET VALUE[/TD]

[TD="bgcolor: transparent"]40554[/TD]

[/TR]

[TR]
[TD="bgcolor: transparent"]29154[/TD]

[TD="bgcolor: transparent, align: right"]1003496[/TD]

[TD="bgcolor: transparent"]23/05/2018[/TD]

[TD="bgcolor: transparent"]B8008923B[/TD]

[TD="bgcolor: transparent"]NET VALUE[/TD]

[TD="bgcolor: transparent"]52320[/TD]

[/TR]

[TR]
[TD="bgcolor: transparent"]29154[/TD]

[TD="bgcolor: transparent, align: right"]1003497[/TD]

[TD="bgcolor: transparent"]23/05/2018[/TD]

[TD="bgcolor: transparent"]B8008923B[/TD]

[TD="bgcolor: transparent"]NET VALUE[/TD]

[TD="bgcolor: transparent"]34010[/TD]

[/TR]

[TR]
[TD="bgcolor: transparent"]29154[/TD]

[TD="bgcolor: transparent, align: right"]1003498[/TD]

[TD="bgcolor: transparent"]23/05/2018[/TD]

[TD="bgcolor: transparent"]B8008923B[/TD]

[TD="bgcolor: transparent"]NET VALUE[/TD]

[TD="bgcolor: transparent"]46280[/TD]

[/TR]

[TR]
[TD="bgcolor: transparent"]29154[/TD]

[TD="bgcolor: transparent, align: right"]1003498[/TD]

[TD="bgcolor: transparent"]23/05/2018[/TD]

[TD="bgcolor: transparent"]B8008923B[/TD]

[TD="bgcolor: transparent"]NET VALUE[/TD]

[TD="bgcolor: transparent"]46280[/TD]

[/TR]

[TR]
[TD="bgcolor: transparent"]29154[/TD]

[TD="bgcolor: transparent, align: right"]1003499[/TD]

[TD="bgcolor: transparent"]23/05/2018[/TD]

[TD="bgcolor: transparent"]B8008923B[/TD]

[TD="bgcolor: transparent"]NET VALUE[/TD]

[TD="bgcolor: transparent"]23664[/TD]

[/TR]

[TR]
[TD="bgcolor: transparent"]29154[/TD]

[TD="bgcolor: transparent, align: right"]1003500[/TD]

[TD="bgcolor: transparent"]23/05/2018[/TD]

[TD="bgcolor: transparent"]B8008923B[/TD]

[TD="bgcolor: transparent"]NET VALUE[/TD]

[TD="bgcolor: transparent"]27876[/TD]

[/TR]

[TR]
[TD="bgcolor: transparent"]29154[/TD]

[TD="bgcolor: transparent, align: right"]1003501[/TD]

[TD="bgcolor: transparent"]23/05/2018[/TD]

[TD="bgcolor: transparent"]B8008923B[/TD]

[TD="bgcolor: transparent"]NET VALUE[/TD]

[TD="bgcolor: transparent"]24012[/TD]

[/TR]

[TR]
[TD="bgcolor: transparent"]29320[/TD]

[TD="bgcolor: transparent, align: right"]1000259702[/TD]

[TD="bgcolor: transparent"]28/08/2018[/TD]

[TD="bgcolor: transparent"]B662055[/TD]

[TD="bgcolor: transparent"]CREDIT NET VALUE[/TD]

[TD="bgcolor: transparent"]-10227.48[/TD]

[/TR]

[TR]
[TD="bgcolor: transparent"]29320[/TD]

[TD="bgcolor: transparent, align: right"]1000259704[/TD]

[TD="bgcolor: transparent"]28/08/2018[/TD]

[TD="bgcolor: transparent"]B662055[/TD]

[TD="bgcolor: transparent"]CREDIT NET VALUE[/TD]

[TD="bgcolor: transparent"]-609.5[/TD]

[/TR]

[TR]
[TD="bgcolor: transparent"]29320[/TD]

[TD="bgcolor: transparent, align: right"]1000259704[/TD]

[TD="bgcolor: transparent"]28/08/2018[/TD]

[TD="bgcolor: transparent"]B662055[/TD]

[TD="bgcolor: transparent"]CREDIT NET VALUE[/TD]

[TD="bgcolor: transparent"]-609.5[/TD]

[/TR]

[TR]
[TD="bgcolor: transparent"]29320[/TD]

[TD="bgcolor: transparent, align: right"]1000259703[/TD]

[TD="bgcolor: transparent"]28/08/2018[/TD]

[TD="bgcolor: transparent"]B662055[/TD]

[TD="bgcolor: transparent"]CREDIT NET VALUE[/TD]

[TD="bgcolor: transparent"]-3816[/TD]

[/TR]

[TR]
[TD="bgcolor: transparent"]29320[/TD]

[TD="bgcolor: transparent, align: right"]1000261809[/TD]

[TD="bgcolor: transparent"]31/08/2018[/TD]

[TD="bgcolor: transparent"]B662055[/TD]

[TD="bgcolor: transparent"]CREDIT NET VALUE[/TD]

[TD="bgcolor: transparent"]-892.5[/TD]

[/TR]

[TR]
[TD="bgcolor: transparent"]29298[/TD]

[TD="bgcolor: transparent"]9992226374-1[/TD]

[TD="bgcolor: transparent"]01/08/2018[/TD]

[TD="bgcolor: transparent"]B674636A8[/TD]

[TD="bgcolor: transparent"]NET VALUE[/TD]

[TD="bgcolor: transparent"]48637[/TD]

[/TR]

</tbody>[/TABLE]


TheCode which I am trying:Code:
Public Sub Import_Text_File()
Dim dataFile As String
Dim fileLine As String, item As String, parts As Variant
Dim i As Long, mRow As Long, n As Long
Dim pprData() As Variant
Dim treasuryrefno As String
Dim pprReportDest As Range

dataFile = "C:\Users\desktop\ALL.txt" 'CHANGE THIS FOLDER PATH AND FILE NAME

With Worksheets("PPR_Consolidated")
.Cells.Clear
.Range("A1:G1").Value = Array("TREASURY_BATCH_NO", "INVOICE_NO", "INVOICE_DATE", "PO_NUMBER", "CHARGE_TYPE", "NET_VALUE", "REMITTANCE_AMT")
Set pprReportDest = .Range("A2")
mRow = 0
End With

Open dataFile For Input As #1
n = 0

While Not EOF(1)

Line Input #1 , fileLine
Debug.Print fileLine

item = GetItem(fileLine, "TREASURY REFERENCE NUMBER: ")
If item <> "" Then treasuryrefno = item

parts = Split(Application.WorksheetFunction.Trim(fileLine), " ")

If UBound(parts) = 4 Then
If IsNumeric(parts(0)) Then
n = n + 1
ReDim Preserve pprData(1 To 7, 1 To n)
pprData(1, n) = treasuryrefno
pprData(2, n) = parts(0)
pprData(3, n) = parts(1)
pprData(4, n) = parts(2)
pprData(5, n) = parts(3)
pprData(6, n) = parts(4)
End If
End If

item = GetItem(fileLine, "REMITTANCE AMOUNT ")
If item <> "" Then

For i = 1 To n
pprData(7, i) = item
Next
pprReportDest.Offset(mRow, 0).Resize(n, UBound(pprData)).Value = Application.Transpose(pprData)
mRow = mRow + n
n = 0
End If

Wend

Close #1

MsgBox "Finished"
End Sub
Private Function GetItem(text As String, item As String) As String
Dim p1 As Long, p2 As Long

GetItem = ""
p1 = InStr(text, item)
If p1 > 0 Then
p1 = p1 + Len(item)
p2 = InStr(p1, text, " ")
If p2 = 0 Then p2 = Len(text) + 1
GetItem = Mid(text, p1, p2 - p1)
'Debug.Print GetItem
End If
End Function

Need your help ugently!

Thanks n advance!
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Sort by date Sort by votes
You must log in or register to reply here.

Similar threads

S
  • Solved
IF function excluding 0 and 9 in text string
Replies
4
Views
358
dreid1011
dreid1011
S
  • Question Question
Create New Sheet with Specific Name Based on Variable Cell
Replies
0
Views
242
SBExcel123
S
T
  • Solved
VBA to copy down data for blank cells - repeat for entire data set.
2
Replies
14
Views
1K
Marc L
Marc L
R
  • Solved
import data file to excel only for specific
Replies
3
Views
816
roykana
R
R
  • Locked
  • Question Question
VBA Code to fill in tables?
Replies
1
Views
414
Fluff
Fluff

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top