topic Re: Replace a String in Word with a Macro in QlikView

Replace a String in Word with a Macro

Wed, 30 May 2012 14:07:55 GMT

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

Thanks a lot

Replace a String in Word with a Macro

flipside — Wed, 30 May 2012 21:35:05 GMT

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

Re: Replace a String in Word with a Macro

Tue, 12 Jun 2012 08:12:50 GMT

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

Re: Replace a String in Word with a Macro

Thu, 24 Jan 2013 00:21:45 GMT

Hi Pedro,

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

Thanks,

Puneet.

Re: Replace a String in Word with a Macro

Thu, 24 Jan 2013 07:35:18 GMT

I think this should work:

Set xlApp = CreateObject("Excel.Application")

xlApp.Visible = True

Set xlSheet = xlApp.ActiveSheet

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

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

Regards

Re: Replace a String in Word with a Macro

Thu, 24 Jan 2013 07:40:17 GMT

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".

Re: Replace a String in Word with a Macro

Thu, 24 Jan 2013 09:10:22 GMT

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