Filling word vba variables from excel

[IkEcht]

Hi all,

I'm trying to do a lot of find and replace operations in word, using word vba. Getting the text to find from an excel file, as well as the replacing text.
It is in the replacing text that I have a problem. I want to be able to use code in there. So for instance replace the text Yearnow by Year(date). All of this works fine when done from within word only, but getting the data from within excel makes it malfunction. For instance in the code below even though the word "gemeente" is taken from excel, this is not replaced by "test" . Any ideas on how to solve this?

Sub vervangmaarvanuitexcel()
Dim zoek As String
Dim vervang As String
Dim vervang2 As Variant
Dim i As Integer
Dim wb As Excel.Workbook

gemeente = "test"

Set xl = CreateObject("Excel.Application")
xl.Workbooks.Open "Q:\635 Netwerkanalyse\1 - Projectdocumenten\rapportage\dummyzoekenvervang.xlsx"

i = 2
Do
zoek = xl.worksheets("zoekenvervang").Cells(i, 1).Text
vervang = xl.worksheets("zoekenvervang").Cells(i, 2)
Call zoekenvervang(zoek, vervang)
i = i + 1
Loop Until activeworkbook.worksheets("zoekenvervang").Cells(i, 1) <> ""

xl.Workbooks.Close
Set xl = Nothing

End Sub

Thanks for your help!
IkEcht

[fumei]

You need to post all your code.

Even though you have:

gemeente = "test"

you never use it afterwards. No wonder the code does nothing.

[IkEcht]

Hi Fumei,

thanks for your answer. I clearly didn't elaborate enough on my question. Else in the code " gemeente" is declared as a public variable of the string type.
Now from cell (i,2) I would retrieve "gemeente" . This exactly is my problem. What I want to happen is that it receives " gemeente" and replace it by the value of the variable gemeente. What is does is receive " gemeente" and treat this as a string.

I hope the problem is clear this way. If not I will only be able to attach more code and sample document and workbook when back at the office.

Jaap

[fumei]

OK, if gemeente is a public variable, then when you instruct:
gemeente = "test"
that variable is now equal "test"

But the fact remains you do not DO anything with it.

As for gemeente getting the value of cell(i,2), I do not see anything like that.

[IkEcht]

Hi Fumei,

Clearly I still wasn't clear enough on what I hope to realize.

I have a large document, in which I want to do a lot of search and replace actions (through the function "zoekenvervang" that is called in the sub I entered at the start of this thread).

Now I want to get the search (zoek) and the replace (vervang) term from an excell-document. This works fine as long as the search and the replace term are just strings. But I want to be able to put other things into the replace field.
So for instance if I would type just in the sub:
vervang = gemeente
the resulting value of vervang would be "test"
but if I fill vervang from the excel-document, with the word gemeente, so:
vervang = xl.ws.cells(i,2)
equals
vervang = gemeente
The value of the gemeente variable isn't evaluated by the code, instead the value of the excel-cell is a string to vba.

So basicly what I want is that if the value of the excel-cell referenced is gemeente, vba immedialty replaces this by "test" (in this example). Same goes for several other variables.

I hope what I try to do is clear now.

THanks, Jaap

[macropod]

For an implementation of using an Excel workbook to hold find/replace strings, see: http://www.msofficeforums.com/word/12803-find-replace.html#post34254

However, it seems from the above discussion that you also want to be able to programmatically substitute values retrieved from the Excel file with other values. In that case, why not simply change the Excel data? Or use a different source file? If neither of those approached is suitable, then you'll need to add code that actually tests what is coming from Excel and, where a match is found, change it. Simply setting 'vervang = gemeente' isn't enough.