Solved: Word Macro/VBA Find and Replace

[anmlhse]

Any help is greatly appreciated!

I have a 2 seperate plain text files that I bring into word separately:
File One reads:
9, "2", 575006074, 1, 575011762
9, "2", 575006076, 1, 575011772
9, "2", 575006078, 2, 575011783
9, "2", 575006078, 1, 575011782
9, "2", 575006100, 1, 575011823
9, "2", 575006100, 2, 575011824
9, "2", 575006100, 3, 575011825
File Two reads:
8, 575006074, 1, "James Smith"
8, 575006078, 1, "Marcia Johnson"
8, 575006078, 2, "Don OHara"
8, 575006076, 1, "Eric Dixon"
8, 575006100, 1, "Chuck Brady"
8, 575006100, 2, "Chris Miller"
8, 575006100, 3, "Mona Tyler"

What I need to do is pull from the first file the ending 9 digit number and replace the first digit before each persons name with this 9 digit code in the second file. The first set of 9 digits match between both files.

The NEW second file needs to read:

8, 575006074, 575011762, "James Smith"
8, 575006078, 575011782, "Marcia Johnson"
8, 575006078, 575011783, "Don OHara"
8, 575006076, 575011772, "Eric Dixon"
8, 575006100, 575011823, "Chuck Brady"
8, 575006100, 575011824, "Chris Miller"
8, 575006100, 575011825, "Mona Tyler"

I have only done a couple off macros in word, however I just cannot wrap my head on what exactly needs to be done. Not even sure Find and Replace is correct. I also need to save this in Word. Does this make sense?

Thank you.
Jane

[Frosty]

This seems a bit tricky to me as well, as I can't tell from looking at your data if you've given me "real" data or "dummy" data.

Conceptually, it sounds like you're describing a database-like functionality, where you essentially want to (again, conceptually) JOIN two tables using a QUERY.

However, in order for those joins to work, you need unique data (i.e., Find/Replace could work, after a fashion, but not reliably if 575006078 means both "Marcia Johnson" and "Don OHara."

So, is that dummy data, or no?

Because the only other way I see of creating your output is basically saying (in code)
1. here's the stuff on line 1, file 1.
2. here's the stuff on line 1, file 2.
3. spit some of the stuff from #1 and some of the stuff from #2 onto line 1 of new file 3.

If you need to search through file 1 and file 2 in order to marry your data... I think you need to give more information (unless you're sure the actual line # of the data is good).

It may help to post up some dummy (but good) data (as well as any additional info you have), and see if a couple of the different brains here can contribute.

[Frosty]

And, if you really are just relying on line numbering... it would probably be faster to simply copy and past the stuff into a word document, and then try using the "Convert Table To Text" feature, separating on a comma.

You'd end up with (at least, if your data looks the same as above) a table with 5 columns, 7 rows, and table with 4 columns and 7 rows... then you could copy all of the columns of one table (except for the blank last column) and paste it on to the end of the other table.

You'd end up with one big table with 12 columns and 7 rows... and then you could just delete which ever columns that don't have data you want.

[macropod]

Hi Jane,

Here's an Excel macro that will process the data:
Sub DataMerge()
'Set Up Variables
Dim StrIn1() ' Array
Dim StrIn2() ' Array
Dim StrFlNm1 As String
Dim StrFlNm2 As String
Dim iFileIn1 As Long
Dim iFileIn2 As Long
Dim StrOut As String
Dim iFileOut As Long
Dim StrData As String
Dim StrFlOut As String
Dim i As Long, j As Long
'Get Input FileName
StrFlNm1 = Application.GetOpenFilename(filefilter:="Text Files(*.txt;*.csv),*.txt;*.csv,All Files (*.*),*.*", _
Title:="Open File with Client Names")
StrFlOut = StrFlNm1
'Get Input FileName
StrFlNm2 = Application.GetOpenFilename(filefilter:="Text Files(*.txt;*.csv),*.txt;*.csv,All Files (*.*),*.*", _
Title:="Open Secondary File")
'Turn Screen Updating Off
Application.ScreenUpdating = False
'Get Next Available File Handle Number
iFileIn1 = FreeFile()
'Open the file and store the data in an array
Open StrFlNm1 For Input As #iFileIn1
i = 0
Do While Seek(iFileIn1) <= LOF(iFileIn1)
ReDim Preserve StrIn1(4, i)
On Error Resume Next
Input #iFileIn1, StrIn1(1, i), StrIn1(2, i), StrIn1(3, i), StrIn1(4, i)
i = i + 1
Loop
'Close the File
Close #iFileIn1
'Get Next Available File Handle Number
iFileIn2 = FreeFile()
'Open the file and store the data in an array
Open StrFlNm2 For Input As #iFileIn2
i = 0
Do While Seek(iFileIn2) <= LOF(iFileIn2)
ReDim Preserve StrIn2(5, i)
On Error Resume Next
Input #iFileIn2, StrIn2(1, i), StrIn2(2, i), StrIn2(3, i), StrIn2(4, i), StrIn2(5, i)
i = i + 1
Loop
'Close the File
Close #iFileIn2
'Match the Data from both arrays
For i = 0 To UBound(StrIn1, 2)
'Pair the 2nd & 3rd elements from the names file
StrData = StrIn1(2, i) & StrIn1(3, i)
'Clear the 3rd element from the names file
StrIn1(3, i) = vbNullString
For j = 0 To UBound(StrIn2, 2)
'Compare the paired data from the names file against the corresponding data
' from the 3rd & 4th elements from the secondary file. Insert data from
' the 5th element of the secondary file into the 3rd element of the names file
If StrData = StrIn2(3, j) & StrIn2(4, j) Then
StrIn1(3, i) = StrIn2(5, j)
Exit For
End If
Next
Next
'Assemble the output data
For i = 0 To UBound(StrIn1, 2)
StrOut = StrOut & StrIn1(1, i) & ", " & StrIn1(2, i) & ", " & StrIn1(3, i) & ", " & _
Chr(34) & StrIn1(4, i) & Chr(34) & vbCrLf
Next
StrOut = Left(StrOut, Len(StrOut) - 1)
'Get Next Available File Handle Number
iFileOut = FreeFile()
'Open names file for content replacement
Open StrFlOut For Output As #iFileOut
'Output the data
Print #iFileOut, StrOut
'Close the file
Close #iFileOut
'Remove Message From Status Bar
Application.StatusBar = False
'Turn Screen Updating On
Application.ScreenUpdating = True
MsgBox "Finished!", vbOKOnly
End SubTo use it, you select the data file containing the client names, then the file containing the data to be merged with it. As part of the processing, if no match is found, the relevant field is left empty. The output is written back to the client name file.

[anmlhse]

GREAT! Thank you Macropod! And Thank you Frosty for taking a look as well. The data is real, except for I removed a bunch of similar data.

Very much appreciate all of your expertise!!!

Thank you again!
Jane

[macropod]

The data is realNo, the data are real:whip