Posts by Elron

This is the code I have so far, but it needs to begin extracting text after the "Output Financial Values --" in the image above.

Sub ReadDataFromTextFile()

Dim myFile As String
Dim text As String
Dim textline As String

Dim InvCount As Integer
Dim GrAmt As Integer
Dim AgyDisc As Integer
Dim Tax1 As Integer
Dim TotalInvAmt As Integer

myFile = "U:\01-API_BCS reconciliation project\Example Files\GRAINV_20211201102200prn_MasterProductionSummary-20211202-006.txt"


Open myFile For Input As #1

    Do Until EOF(1)
        Line Input #1, textline
        text = text & textline
    Loop

Close #1

InvCount = InStr(text, "Documents")
GrAmt = InStr(text, "Gross Amount")
AgyDisc = InStr(text, "Agency Discount")
Tax1 = InStr(text, "Tax 1 Amount")
TotalInvAmt = InStr(text, "Total Invoice Amount")


'Count from first letter of "InvCount"(+27) to last character before first letter of data to pull, then how many characters to extract.

Range("N4").Value = Mid(text, InvCount + 27, 14)
Range("O4").Value = Mid(text, GrAmt + 27, 14)
Range("P4").Value = Mid(text, AgyDisc + 27, 14)
Range("Q4").Value = Mid(text, Tax1 + 27, 14)
Range("R4").Value = Mid(text, TotalInvAmt + 27, 14)




End Sub

Hello.

I am working toward a system to loop through a folder of text files while comparing file names to a list in an Excel worksheet. If a file name is in the list, I need to extract five fields of data from the text file and paste into fields on the same row in the list. So far I can extract the fields from a text file (by specifying the name of the text file in the code--I cannot loop yet) and put the data in the correct fields of the Excel file.

The immediate issue is that there are two sets of data using the same words, one set in the top half of the page and the other in the bottom half. The code I have pulls from the first set, but the data I need for reconciliation is in the second set, below the words "Output Financial Values - -", which is Documents, Gross Amount, Agency Discount, Tax 1 Amount and Total Invoice Amount. Can someone help me start the extraction after "Output Financial Values - -"? I've attached a sample workbook and two text files.