[A1sysman]

Hi all, I asked yesterday, VBA scripting was suggested. I was hoping for
advise on how to run a macro to resolve the UINs from either a csv or an
Excel document. Any futher advise please ...

Can Word be configured to use a csv lookup table or an Excel spread sheet?

Below are examples of the specifics:

Current structure and data in the Word doc €¦

Sponsor1233/Sponsor
Sponsor44/Sponsor

Intended output after using (macro?) look up €¦

SponsorJOHN BROWN/Sponsor
SponsorPAUL WHITE/Sponsor

Where there the lookup uses a csv file generated from an Excel spreadsheet,
that reads€¦

Brown,John,1233
White,Paul,44
Green,Nick,9062
.... and so on.

If the answer is "yes, Word can do this", how please?

Thanks,

A1sysman

[Helmut Weber[_2_]]

Hi A1sysman,

sorry, there are so many ways,
and it isn't that easy and
better ask in ...word.vba.general,
and I didn't care about speed or effective coding,
but anyway, just for showing that it is possible:

Sub Test003()
Dim rngDocNow As Range ' the active document's range
Dim rngDocCsv As Range ' the csv-document's range
Dim strNumber As String ' the number to be searched for
Dim strName() As String ' the line where the number is found
Dim strReplace As String ' the string to replace the number with
Dim docCsv As Document ' the csv-document
Dim DocNow As Document ' the active document
' -------------------------------------------------------
Set DocNow = ActiveDocument
Set docCsv = Documents.Open("c:\test\test.csv")
DocNow.Activate
Set rngDocNow = DocNow.Range
With rngDocNow.Find
.Text = "\Sponsor\*\/Sponsor\"
.MatchWildcards = True
While .Execute
strNumber = rngDocNow.Text
strNumber = Left(strNumber, Len(strNumber) - 10)
strNumber = Right(strNumber, Len(strNumber) - 9)
' MsgBox strNumber ' for testing
Set rngDocCsv = docCsv.Range
With rngDocCsv.Find
.Text = "," & strNumber & vbCrLf
If .Execute Then
strName = Split(rngDocCsv.Paragraphs(1).Range.Text, ",")
strReplace = UCase(strName(1))
strReplace = strReplace & " "
strReplace = strReplace & UCase(strName(0))
rngDocNow.Text = _
"Sponsor" & strReplace & "/Sponsor"
' MsgBox strReplace ' for testing
End If
rngDocNow.Collapse Direction:=wdCollapseEnd
End With
Wend
End With
docCsv.Close
Set docCsv = Nothing
End Sub

--

Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Vista Small Business, Office XP

[A1sysman]

Hi Helmut,

Thank you for your reply. You have been a great help to me.

Rgds,

A1sysman

"Helmut Weber" wrote:

Hi A1sysman,

sorry, there are so many ways,
and it isn't that easy and
better ask in ...word.vba.general,
and I didn't care about speed or effective coding,
but anyway, just for showing that it is possible:

Sub Test003()
Dim rngDocNow As Range ' the active document's range
Dim rngDocCsv As Range ' the csv-document's range
Dim strNumber As String ' the number to be searched for
Dim strName() As String ' the line where the number is found
Dim strReplace As String ' the string to replace the number with
Dim docCsv As Document ' the csv-document
Dim DocNow As Document ' the active document
' -------------------------------------------------------
Set DocNow = ActiveDocument
Set docCsv = Documents.Open("c:\test\test.csv")
DocNow.Activate
Set rngDocNow = DocNow.Range
With rngDocNow.Find
.Text = "\Sponsor\*\/Sponsor\"
.MatchWildcards = True
While .Execute
strNumber = rngDocNow.Text
strNumber = Left(strNumber, Len(strNumber) - 10)
strNumber = Right(strNumber, Len(strNumber) - 9)
' MsgBox strNumber ' for testing
Set rngDocCsv = docCsv.Range
With rngDocCsv.Find
.Text = "," & strNumber & vbCrLf
If .Execute Then
strName = Split(rngDocCsv.Paragraphs(1).Range.Text, ",")
strReplace = UCase(strName(1))
strReplace = strReplace & " "
strReplace = strReplace & UCase(strName(0))
rngDocNow.Text = _
"Sponsor" & strReplace & "/Sponsor"
' MsgBox strReplace ' for testing
End If
rngDocNow.Collapse Direction:=wdCollapseEnd
End With
Wend
End With
docCsv.Close
Set docCsv = Nothing
End Sub

--

Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Vista Small Business, Office XP