Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Now Live: Qlik Sense SaaS Simplified Authoring – Analytics Creation for Everyone: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Replace a String in Word with a Macro

Does anybody know the way to replace a string (Let`s say: %Amount%) in a Word File using the vbscript?

Thanks a lot

1 Solution

Accepted Solutions
flipside
Specialist II
Specialist II

Hi Pedro,

Try this, courtesy of Microsoft ...

sub openDoc

Const wdReplaceAll  = 2

Set objWord = CreateObject("Word.Application")
objWord.Visible = True
Set objFile1 = objWord.Documents.Open("...  your file path here ...")

Set objSelection = objWord.Selection

objSelection.Find.Text = "%Amount%"
objSelection.Find.Forward = TRUE
objSelection.Find.MatchWholeWord = TRUE

objSelection.Find.Replacement.Text = "Qlikview"
objSelection.Find.Execute ,,,,,,,,,,wdReplaceAll

end sub

All the commas in the last line of code are correct.

flipside

View solution in original post

6 Replies
flipside
Specialist II
Specialist II

Hi Pedro,

Try this, courtesy of Microsoft ...

sub openDoc

Const wdReplaceAll  = 2

Set objWord = CreateObject("Word.Application")
objWord.Visible = True
Set objFile1 = objWord.Documents.Open("...  your file path here ...")

Set objSelection = objWord.Selection

objSelection.Find.Text = "%Amount%"
objSelection.Find.Forward = TRUE
objSelection.Find.MatchWholeWord = TRUE

objSelection.Find.Replacement.Text = "Qlikview"
objSelection.Find.Execute ,,,,,,,,,,wdReplaceAll

end sub

All the commas in the last line of code are correct.

flipside

Not applicable
Author

Thanks a lot, and  sorry about the delay...

Not applicable
Author

Hi Pedro,

Can we do something similar for microsoft excel  after creating "Excel.Application" object in an activesheet ? 

Thanks,

Puneet.

Not applicable
Author

I think this should work:

Set xlApp = CreateObject("Excel.Application")

xlApp.Visible = True

Set xlSheet = xlApp.ActiveSheet

'*********************************************************

xlSheet.Cells.Replace "TextToFind", "TextToReplace"

Regards

Not applicable
Author

Hi Pedro,

Thanks for the response. This worked perfectly !

In the syntax of xlSheet.Cells.Replace "TextToFind", "TextToReplace"

is there a parameter which can help me replace full cell contents if even TextToFind is a part of the content of a cell ?

For example - My Cell contains "TextToFind ABC" and I want to replace the full cell contents with "TextToReplace".

Not applicable
Author

Try this, I've tested it and it worked for me. It's a modification of the Excel Online Help  example

Set objCells=xlSheet.Cells

With objCells

   Set c = .Find ("TextoToFind")

        If Not c Is Nothing Then

       firstAddress = c.Address

        Do

           c.Value = "TextToReplace"

            Set c = .FindNext(c)

        Loop While Not c Is Nothing And c.Address <> firstAddress

    End If

End With

Hope It works for you