MS Windows 10 Enterprise. Excel for Microsoft 365 MSO (V.2302 Build 16.0.16130.20806) 32-bit
Posts
11
Need Macro or VBA to extract data from report to use in Excel
Hello Forum,
Hopefully, this is an easy problem to solve. I have a multi-page report (text file) showing delayed payments which is formatted for the users in this way. See also attachments.
I would like to use an excel macro or VBA to extract the fields in yellow (SSN and AMOUNT) from the report, and place these two pieces of data in a CSV or Text file as below. Note that the rows in the source report between each person's payment information is variable (as there are between 1 and x nr of REASONs for each delayed payment). Does anyone know how to glean such data from a text report and place in a text or CSV file?
Regards and thanks!
The goal:
489-72-5078 3500
499-42-1234 10000
etc..
See attachments. Source Delayed Payments Report and excel (i.e. the result i want to achieve) REPORT DELAYED PAYMENTS.TXT Excel-result.xlsx
Last edited by JMB99; 11-03-2023 at 10:08 AM.
Reason: Added attachments to my question
Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
Posts
29,969
Re: Need Macro or VBA to extract data from report to use in Excel
See the yellow banner at top page on how to attach a workbook. Please include both the text input file and the CSV/text output file, including sample expected results
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
According to your attachment a VBA demonstration for starters :
PHP Code:
Sub Demo1()
V = ThisWorkbook.Path & "\REPORT DELAYED PAYMENTS.TXT"
If Dir(V) = "" Then V = Application.GetOpenFilename("Text files,*.txt"): If V = False Then Exit Sub
R& = FreeFile
Open V For Binary As #R
V = Split(Input(LOF(R), #R), "SSN: ")
Close #R
If Not V(0) Like "*DELAYED PAYMENTS *" Then Beep: Exit Sub
ReDim W(1 To UBound(V), 1)
For R = 1 To UBound(V)
W(R, 0) = Split(V(R), vbCrLf)(0)
W(R, 1) = Val(Split(V(R), "AMOUNT:")(1))
Next
With Blad1
.UsedRange.Clear
.[A1].Resize(UBound(W), 2) = W
.UsedRange.Borders.Weight = 2
End With
End Sub
► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄
To create a text file from a text file Excel is very not necessary ‼
Anyway, according to your missing csv attachment , another VBA demonstration for starters :
PHP Code:
Sub Demo2()
V = ThisWorkbook.Path & "\REPORT DELAYED PAYMENTS.TXT"
If Dir(V) = "" Then V = Application.GetOpenFilename("Text files,*.txt"): If V = False Then Exit Sub
F% = FreeFile
Open V For Binary As #F
W = Split(Input(LOF(F), #F), "SSN: ")
Close #F
If Not W(0) Like "*DELAYED PAYMENTS *" Then Beep: Exit Sub
Open Replace(V, ".txt", ".csv", , , 1) For Output As #F
For R& = 1 To UBound(W)
Print #F, Split(W(R), vbCrLf)(0) & "," & Val(Split(W(R), "AMOUNT:")(1))
Next
Close #F
End Sub
► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄
LinkBack URL
About LinkBacks
Register To Reply
Hello, try this !
Or this ...
, another VBA demonstration for starters :
Bookmarks