Change letters in microsoft word using vba and excel cell reference

[olli.excel]

Hello! I have a document where I would like to change all letters to another spesific based on excel cell value:

Sub SelectToBracketsDelete()
Dim j As Integer
For j = 2 To 10
stringReplaced = stringReplaced + "string to be searched"
For Each myStoryRange In ActiveDocument.StoryRanges
With myStoryRange.Find
.Text = workbooks("Bok1.xlsm").Worksheets("Ark1").Cells(j, 2).Value
.Replacement.Text = workbooks("Bok1").Worksheets("Ark1").Cells(j, 3).Value
.Wrap = wdFindContinue
.ClearFormatting
.Replacement.ClearFormatting
.Replacement.Highlight = False
.Execute Replace:=wdReplaceAll
End With

Next myStoryRange
Next j


End Sub

I got an error: sub og function not defined

I have values in B column that is searched in word. C column is the replacement. Is it also possible to do this only with first sentence(to first .)?

I appreciate any help!

[cytop]

You don't mention where the error occurs but I would assume

Please Login or Register  to view this content.

'Workbooks' means nothing to Word unless the code has a reference to Excel

You need to add a reference to Excel (Tools/References menu)
You then need to either start Excel and load the source workbook or get a reference to an existing instance which has the workbook loaded (probably easier to start Excel yourself).

Once you have that working then you can start to debug any issues with the Find/Replace code.

[olli.excel]

Thank you for the answer! Now it says "script out of range" with debug at ".Text = workbooks("Bok1.xlsm").Worksheets("Ark1").Cells(j, 2).Value"

I added the reference in tools. Bok1.xlsm and Ark 1 is correct. Do you know why it is out of range?

[cytop]

You then need to either start Excel and load the source workbook or get a reference to an existing instance which has the workbook loaded

Did you?

Bok1.xlsm and Ark 1 is correct

You probably do have a workbook named 'bok1' which has a worksheet named 'Ark 1' but unless your code has a reference to them it means nothing.

Have a read of this

[olli.excel]

I tried again with some changes from that website. Now there`s nothing to debug and the numbers will not change. Can you take a look at the files i attach?

Thank you for the help!

[cytop]

Do you want to loop through the Excel range replacing all in Col B with the corresponding value form Col C?

[olli.excel]

Yes. It works now, but some values is wrong. I`m trying to do the same back again, but it doesnt work properly. some letters are converted and some are not. maybe go letter by letter in word and search in excel: if found then change?

[cytop]

...or try this

Please Login or Register  to view this content.

[olli.excel]

Thank you so much! Didn`t think of offset. it works perfectly! Modified somehow. can to the code several times, then do it backwards several times and its normal!

[cytop]

can to the code several times, then do it backwards several times and its normal

Sorry - I really have no idea what you mean but will assume ending with an exclamation (!) means something good

[olli.excel]

Yes, it works perfectly. Created a code in excel so i can "encrypt" a word file to the codes specified in excel