Why does this macro produce all capitals?

[aiwnjoo]

I want it to copy and paste whatever the cell is;

Andrew Smith

not

ANDREW SMITH

Thanks;

Sub Replacing()

    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Cursor = xlWait
    Application.DisplayStatusBar = True
    Application.StatusBar = "Generating DM Pack, please wait!"
    
    Dim sFile     As String
    Dim wrdApp    As Word.Application
    Dim wrdDoc    As Word.Document
    Dim sInput(2) As String, sOutput(2) As String

    sFile = "Pack"
    
    Set wrdApp = New Word.Application

    With wrdApp
    
        .Visible = True
        
    Set wrdDoc = .Documents.Open("C:\Users\Admin\Desktop\" + sFile + ".doc")
    
    .Selection.Find.ClearFormatting
    .Selection.Find.Replacement.ClearFormatting
    
    sInput(0) = "C2"
    sInput(1) = "C1"
'    sInput(2) = "C8"
'    sInput(3) = "C9"
'    sInput(4) = "C10"
'    sInput(5) = "C11"
'    sInput(6) = "C12"
    
    sOutput(0) = "C2"
    sOutput(1) = "C3"
'    sOutput(2) = "ADD1"
'    sOutput(3) = "ADD2"
'    sOutput(4) = "ADD3"
'    sOutput(5) = "ADD4"
'    sOutput(6) = "ADD5"
    
    For i = 0 To UBound(sInput) - 1
    
      With .Selection.Find
        .Text = sOutput(i)
        .Replacement.Text = Range(sInput(i))
        .Forward = True
        .Wrap = wdFindContinue
        .Format = False
        .MatchCase = False
        .MatchWholeWord = False
        .MatchWildcards = False
        .MatchSoundsLike = False
        .MatchAllWordForms = False
        .Execute Replace:=wdReplaceAll
        
    End With
    
    Next
    
    End With
    
        MsgBox "DM Pack Created!" & vbCrLf & vbCrLf & "Please Check/Save/Print the Letter!"
    
    Set wrdDoc = Nothing
    
    Set wrdApp = Nothing
    
    Application.Cursor = xlDefault
    Application.StatusBar = "DM Pack Created!"
    Application.StatusBar = False
    Application.ScreenUpdating = True
    Application.EnableEvents = True

End Sub

 

[aiwnjoo]

bump

 

[aiwnjoo]

Bump

 

[aiwnjoo]

Bump

 

[Norie]

This appears to be a Word question.

 

[p45cal]

Patience… , patience.

It's Word doing this. From Word's vba help:

Unless otherwise specified, replacement text inherits the formatting of the text it replaces in the document. For example, if you replace the string "abc" with "xyz," occurrences of "abc" with bold formatting are replaced with the string "xyz" with bold formatting.
Also, if MatchCase is False, occurrences of the search text that are uppercase will be replaced with an uppercase version of the replacement text regardless of the case of the search and replacement text. Using the previous example, occurrences of "ABC" are replaced with "XYZ."


So you might get round this problem by change .MatchCase to True, but you'd have to be sure that the occurrences in the document of the find string matched in case also.


bump indeed.

 

[aiwnjoo]

Thanks I will try that.

 

[Smitty]

FYI - Three bumps in less than 10 minutes is a bit excessive. Early Saturday morning like this and you'll need to have some patience.