MobileMobile | Continue

Excel Bar

Excel Bar

Your excel questions will be responsed by our excel experts within 24hrs.Our service is free.

 Forgot Pass?
 Register Now
Find
Hot Search: Vlookup Match VBA
12Next
Return Post new threads
View: 580|Reply: 17

Find and replace as footnote (from Excel) [SOLVED]

[Copy link]

1

Topics

6

Posts

19

Integral

Member

Rank: 2

Integral
19
Post on 9-30-2016 08:44:22 | All posts |Read mode
Hi All,
I have an Excel routine that populates a Word template by searching codes and replacing them with the relevant text. This is working fine.
Embedded within the replacement text, I have additional codes that relate to footnotes - the Excel tool can correctly identify these codes, look up the relevant footnote text and add a new footnote with the correct reference and text. All good.
What I cannot get it to successfully do is place the footnote at the correct location in the body of the Word document. Currently, my best solution is as follows:
Dim Rge as Object, SearchCode$ 'wDoc Object passed-into the procedure as open Word Doc
SearchCode = {1} 'unique dummy code to find in body of text
Set Rge = wDoc.Content 'set the range object to the whole body of the doc
Rge.Start = InStr(Rge.Text, SearchCode) 'set the start of the range to be where the instance of the unique SearchCode appears --- THIS IS THE PROBLEM
With Rge.Find
       .ClearFormatting
       .Text = SearchCode
       .Replacement.Text =  
       .Forward = True
       .Wrap = 1
       .Execute Replace:=2 'remove the searchcode
End With
Rge.End = Rge.Start 'define the range as the one character space where the unique code was originally
Rge.Footnotes.Add Range:=Rge, Reference:=1, Text:=Test 'insert the footnote hereThe problem is in correctly defining the location of the SearchCode within the body of the document - using the InStr() function doesn't work because this picks up all the nonprintable characters in the document, so the character count is never correct.
Any suggestions for how to get around this? Perhaps one of the following:Is there some way of me defining Rge in terms of an output from the .Find function?Alternatively, can I replace the SearchCode directly with the footnote?Or, instead, is there a simple way of identifying the accurate character count / insertion point for the replaced text?
Many thanks in advance,
AL




Reply

Props Report

2

Topics

188

Posts

466

Integral

Conqueror

Rank: 3Rank: 3

Integral
466
Post on 9-30-2016 09:17:47 | All posts



Try:
Dim SearchCode$ 'wDoc Object passed-into the procedure as open Word Doc
SearchCode = {1} 'unique dummy code to find in body of text
With wDoc.Content
  With .Find
    .ClearFormatting
    .Text = SearchCode
    .Forward = True
    .Wrap = 1
    .Execute
  End With
  If .Find.Found Then
    .Delete
    .Footnotes.Add Range:=.Duplicate, Reference:=1, Text:=Test
  End If
End With











Reply Support Opposition

Props Report

1

Topics

6

Posts

19

Integral

Member

Rank: 2

Integral
19
 Author| Post on 9-30-2016 09:40:04 | All posts



No, that doesn't work - just errors out...


Thanks though.




Reply Support Opposition

Props Report

1

Topics

6

Posts

19

Integral

Member

Rank: 2

Integral
19
 Author| Post on 9-30-2016 10:20:17 | All posts



Just an update - I still haven't resolved this issue but I think i understand why it isn't working.
SearchCode = {1}
Set Rge = wDoc.Content 'set the range object to the whole body of the doc
With Rge.Find
       .ClearFormatting
       .Text = SearchCode
       .Replacement.Text =  
       .Forward = True
       .Wrap = 1
       .Execute Replace:=2 '--- [1]
End WithAccording to other sources, if this were being written in Word VBA (rather than via Excel), the Rge variable would actually be set as a Range, rather than as an object. At point [1] in the code, the scope of the Rge variable would then be redefined to start at the location of the found text.
This is not happening when the Object vartype is used, and i can't find any way of getting the Find function to return to the position of the found text.
Any ideas on how to do this from Excel?




Reply Support Opposition

Props Report

1

Topics

6

Posts

19

Integral

Member

Rank: 2

Integral
19
 Author| Post on 9-30-2016 10:53:44 | All posts



Solved.
I think it was something to do with my use of braces {} - when I stop searching for a code bracketed in braces (e.g. {1}) and switch instead to searching for a unique string (REF01), the Rge object correctly readjusts its scope and I can use that.
Weird.
Final code:
  SearchCode2 = Split(Split(ReplaceText, {REF0)(i), })(0)
        Set Rge = wDoc.Content
        With Rge.Find
            .Text = REF0  SearchCode2
            .Execute
        End With
        
        Rge.Start = Rge.Start - 1
        Rge.End = Rge.Start + 7
        wDoc.Content.Footnotes.Add Range:=Rge, Reference:=SearchCode2, Text:=test
        Rge.Delete(Not: I have retained the braces around the unique code, hence needing to adjust the size of Rge at the end. When they are included in the search, the Find function doesn't adjust the scope of Rge.)




Reply Support Opposition

Props Report

2

Topics

188

Posts

466

Integral

Conqueror

Rank: 3Rank: 3

Integral
466
Post on 9-30-2016 11:43:36 | All posts



The code I posted works just fine for the scenario you described in post #1. If it's not working for you, that's because your content is not what you described. As for erroring out, that suggests you're not even using the code as posted...
Reply Support Opposition

Props Report

1

Topics

6

Posts

19

Integral

Member

Rank: 2

Integral
19
 Author| Post on 10-1-2016 02:33:58 | All posts



No, that doesn't work - just errors out...


Thanks though.




Reply Support Opposition

Props Report

1

Topics

6

Posts

19

Integral

Member

Rank: 2

Integral
19
 Author| Post on 10-2-2016 13:02:58 | All posts



No, that doesn't work - just errors out...


Thanks though.




Reply Support Opposition

Props Report

39

Topics

2573

Posts

5415

Integral

Forum Legend

Rank: 8Rank: 8

Integral
5415
Post on 12-15-2016 20:02:22 | All posts



Try
=SUBSTITUTE(M22,:,!,2)
where M22 is your text and ! is the replace string.




Reply Support Opposition

Props Report

0

Topics

40

Posts

82

Integral

Member

Rank: 2

Integral
82
Post on 12-15-2016 20:28:35 | All posts



Do you mean that you have a lot of similar strings where you want to replace the second colon? Or are they all identical? Or is the part around the colon identical? Please provide a selection of sample data: one item is not enough.











Reply Support Opposition

Props Report

12Next
Return Post new threads

Points policy of this forum

Archiver|Mobile|Small dark house|Contact us|Excel Bar

GMT-5, 11-18-2017 04:52 , Processed in 0.166974 second(s), 20 queries .

Powered by Discuz! X3

© 2001-2013 Comsenz Inc.

!fastreply! Top !return_list!