Solved: import record from text file & match with record in excel

[Rob342]

Hi Guys
Really need some help with this one, to match a record in a text file with a record on excel
I have already opened the text file & selected the correct record as per attachments, but really need to know how to match the claim no & post the values to the sheet "DBase".
This is where is starts & ends at the mo
If somebody can show me how to match the claim & get the 1st bit of data back in the sheet then i might able to carry on!

Sub ImportMatchData()
Dim FSO As Object '<-- FileSystemObject
Dim fsoTStream As Object '<-- TextStream
Dim fsoFile As Object '<-- File
Dim strLine As String

Set FSO = CreateObject("Scripting.FileSystemObject")
For Each fsoFile In FSO.GetFolder(ThisWorkbook.Path & "\").Files
If fsoFile.Type = "Text Document" Then
Set fsoTStream = FSO.OpenTextFile(fsoFile.Path, 1, False, &HFFFFFFFE)
Do While Not fsoTStream.AtEndOfStream
strLine = fsoTStream.ReadLine
If Left(strLine, 6) = "DW028M" Then
'read the data in the file
'match the claim num with the claim no on "DBase" col D
' if found then put the data req to the col & row on "DBase" starting at col "O"
'find the next claim and match same

End If
Loop
fsoTStream.Close
End If
Next
End Sub

[Rob342]

solved the problem!

[Tinbendr]

Well, I went to the trouble to solve it, I'm gonna post it.

[Rob342]

Hi David

Sorry I didn't think that i was going to get a reply to this problem, i opologise, i had a spare hour today and did solve the 1st part of writing it back to the sheet.Its not as slick as yours though.
The next problem was to convert the data back to the correct format for the sheet, but you have kindly done that for me, thankyou.

You obviously have come across this type of record format before and read it beautifully.

Thankyou for taking the time & effort, much appreciated, i'm glad you did post it!

Regards
Rob

[Tinbendr]

Glad I could help.

I couldn't make some of the fields match your expected outcome, but I figured you could tweak it.

I did include the sign of the monies when posting to the workbook. This way, if you have a credit somewhere in the future, the code will already be in place.