how to replace a text in word by cell value (bulleted text) using macro

Gowri · #1 03-18-2017, 10:56 AM

I have to replace a text in word by cell value in excel using macro. Am getting output but if the cell contain bulleted text it is coming in the same line in word.I want the same cell value to be replaced in word.

macropod · #2 03-18-2017, 09:52 PM

You really haven't given us anything meaningful to work with. There is no particular reason replacing text should change paragraph formats, regardless of where the data are coming from.

There are plenty of code examples on this forum for using Excel workbooks to hold Find/Replace strings.

Gowri · #3 03-18-2017, 11:40 PM

Here is my code. Please check it.

Code:

Dim oWordApp As Object, oWordDoc As Object, rngStory As Object
    Dim sFolder As String, strFilePattern As String
    Dim strFileName As String, sFileName As String
 Dim i As Long, j As Long, FindWord As String, Ans As String, FoundWord As String, ReplaceAns As String
Call CopyingAFile
        Set oWordApp = CreateObject("Word.Application")
    oWordApp.Visible = True
        Set oWordDoc = oWordApp.Documents.Open("E:\Output\Original.docx")
For i = 2 To 40
            FindWord = Sheets("LookUp").Range("B" & i).Value
            If FindWord <> vbNullString Then
            FoundWord = FindWord
       Ans = Sheets("Answers").Range("C" & i).Value
       If Ans <> vbNullString Then
       ReplaceAns = Ans
        For Each rngStory In oWordDoc.StoryRanges
            With rngStory.Find
                .Text = FoundWord
                .Replacement.Text = ReplaceAns
                .Wrap = wdFindContinue
                .Execute Replace:=wdReplaceAll
            End With
        Next
        End If
End If
Next i
        oWordDoc.Close SaveChanges:=True
        MsgBox "File Saved"
    oWordApp.Quit
    MsgBox " Word File Modified"
    Set oWordApp = Nothing: Set oWordDoc = Nothing
End Sub

If i run the code the cell value in excel is ·Firstline
·Secondline
replaced in word as ·Firstline ·Seconline . But my requirement is to replace in word as ·Firstline
·Seconline

macropod · #4 03-19-2017, 12:05 AM

There is no reference to ·Firstline or ·Secondline in your code, so it's a bit hard to understand what you mean. That said, it appears you find text is in column B of the 'LookUp' sheet and the replace text is in column C of the 'Answers' sheet. The key to your problem would appear to be whatever the relevant cells contain - especially for the 'Answers' sheet.

We'd be able to understand the issues better if you provided both sample document and a workbook with some sample data. You do this via the paperclip symbol on the 'Go Advanced' tab at the bottom of this screen.

PS: When posting code, please use the code tags, indicated by the # button on the posting menu. Without them, your code loses much of whatever structure it had.

gmayor · #5 03-19-2017, 12:13 AM

Without having the materials to test, something like the following may work, Chr(183) is the bullet character. Note that if you are using late binding to Word you need to use the numeric equivalents of the 'wd' commands (see below).

Code:

If Ans <> vbNullString Then
       ReplaceAns = Replace(Ans, Chr(32) & Chr(183), vbCr & Chr(183))
       For Each rngStory In oWordDoc.StoryRanges
              With rngStory.Find
                    .Text = FoundWord
                    .Replacement.Text = ReplaceAns
                    .Wrap = 1
                    .Execute Replace:=2
              End With
       Next
End If

Gowri · #6 03-19-2017, 08:57 AM

I tried your code. It is also not working. In output am getting bullets but it is replacing in the same line. I want it to be replaced in next line if bullet is in cell value of excel.

gmayor · #7 03-19-2017, 09:48 PM

Post the document and worksheet.

Gowri · #8 03-19-2017, 11:17 PM

i have attached my files. I want it to be replaced as ·C
·C++
·Java

macropod · #9 03-26-2017, 04:14 AM

Your workbook structure doesn't exactly make it easy to extract the data. For example you don't have either a row or column titled 'college name', to match your entry in the Word file; all you have is 'College name' (note the difference in case) embedded in another string. It's also not apparent why you've set a loop to process rows 2 To 40 when your data only span 4 rows.

As for outputting .C
·C++
·Java
as bulleted text, you can't really achieve that by inserting bullets into Excel; you should instead insert the un-bulleted text (in Excel) into bulleted paragraphs in Word. To do that, of course, you'd need to have <<subjects>> in a bulleted paragraph in Word.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Select Cell Text to paste into Find/Replace	CBarry	Word VBA	2	02-16-2017 04:37 AM
Macro to find text and replace with form field containing that text	iiiiifffff	Word VBA	16	06-04-2016 01:47 AM
Macro to search warning text style and replace the text color	rohanrohith	Word VBA	3	11-27-2014 01:08 PM
Macro to find coloured text and replace with form-field/formtext containing that text	tarktran	Word VBA	1	11-26-2014 08:12 AM
Word VBA Macro to Find and Replace based on the Alt Text of an Image	bennymc	Word VBA	1	01-27-2014 04:23 PM