copy range from excel into certain section of word doc

[crazydragon84]

Hi I have bunch of similar word doc that has variable amount of sentences/paragraphs that needs to be inserted. I have a table in excel that lists out each paragraph that needs to go into each document. I have 40 documents and each documents will have different number of paragraph. In each document, i have a text that you can search for in the section that paragraphs needs to be inserted (e.g. "Paste_text_HERE"). I am executing the macro from excel which basically loops through a list of document names, filter the table for paragraphs that needs to be pasted into the document (which is range of cells, not specific cell), copies that range, find the "Paste_Text_here" on the document and paste it in there. I am having a difficult time executing the last step, which is pasting into the doc. After i paste it, i also want to format the paragraphs that were inserted to use certain font style. Here is the code I am using the identify the place that paragraphs needs to be inserted, and my attempt to paste the data there.

Set docWD = appWd.Documents.Open(File_Path & rng.Value & ".docx")
Set wdFind = appWd.Selection.Find


 With wdFind
                .MatchCase = True
                .Text = "Paste_Text_here"
                .Execute
 End With

With selection.Range
                .Paste  '<---- this is where the error occurs
                .Font.Name = "XYZ"
                .Font.Color = Black
                .Font.Bold = False
                .Font.Italic = False
                .Font.Allcaps = False
                .Font.Size = 8
End With

Any help is appreciated. Thank you.

 

[shknbk2]

Is the error you are getting saying that the Clipboard is empty or that you have the wrong number of arguments or something else?

For wrong number of arguments, try adding appwd in front of the selection.Range in the With block like you did with the Set wdFind.

For an empty Clipboard, try copying the Excel range right before the .Paste (e.g., after executing the Find).

Can

 

[crazydragon84]

Hi you were correct about selection not applying to word, so i changed it to appwd.selection. What I want the code to do is find the text, and paste what was copied from excel after the text found (which is what it's currently doing) and then change the font style of pasted fonts, but the font style changes are not applying to what was pasted. If i try changing the order of .paste to the end, it will paste over the founded text. Any suggestions?

With wdFind                
                .Forward = True
                .MatchCase = True
                .MatchWholeWord = True
                .Execute Findtext:=ED_Text


            End With
            
                        
            With Sheets("abc")
                .Range("ED_List").AutoFilter Field:=6, Criteria1:=rng.Value
                .Range("G3:G" & ED_LRow).SpecialCells(xlCellTypeVisible).Copy
            End With
            
            With appWd.Selection
                .Paste
                With .Font
                    .Name = "Cambria"
                    .Color = Black
                    .Bold = False
                    .Italic = False
                    .Allcaps = False
                    .Size = 8
                    .Underline = False
                End With
                


            End With

 

[shknbk2]

Try this:

With wdFind
    .Forward = True
    .MatchCase = True
    .MatchWholeWord = True
    .Execute Findtext:=ED_Text
End With

If wdFind.Found Then
    Dim r As Word.Range
    Set r = appWd.Selection.Range.Duplicate
    With Sheets("abc")
        .Range("ED_List").AutoFilter Field:=6, Criteria1:=rng.Value
        .Range("G3:G" & ED_LRow).SpecialCells(xlCellTypeVisible).Copy
    End With
    
    With appWd.Selection
        .Paste
        r.End = .End
        With r.Font
            .Name = "Cambria"
            .Color = Black
            .Bold = False
            .Italic = False
            .AllCaps = False
            .Size = 8
            .Underline = False
        End With
    End With
End If

 

[crazydragon84]

Thank you so much for this. I think we are getting close! Few things:

1. Your code seems to apply the font style to everything inclusive of all texts before what was pasted (e.g. if it was pasted on page 3, then everything in page 1 and 2 becomes same font style).
2. Your Code seems to paste OVER what was found, i need to paste AFTER what was found.
3. so i have two codes 1 for ED_Text and one for ND_Text. your code works fine for the ND_Text (I changed what's applicable), but for ED_Text it skips over the If .found then. I am wondering if because the text i have has a number at the end, where as the text on the word doc has a superscript number (although when i do the same process manually in word doc it can still find the text even with the superscript). Thoughts?

 

[shknbk2]

For 1) and 2), I modified the code so that I think it will address the issues.

With wdFind
    .Forward = True
    .MatchCase = True
    .MatchWholeWord = True
    .Execute Findtext:=ED_Text
End With

With Sheets("abc")
    .Range("ED_List").AutoFilter Field:=6, Criteria1:=Rng.Value
    .Range("G3:G" & ED_LRow).SpecialCells(xlCellTypeVisible).Copy
End With

If wdFind.Found Then
    With appWd.Selection
         .Collapse wdCollapseEnd
        Dim r As Word.Range
        Set r = .Range.Duplicate
        .Paste
        r.End = .End
        With r.Font
            .Name = "Cambria"
            .Color = Black
            .Bold = False
            .Italic = False
            .Allcaps = False
            .Size = 8
            .Underline = False
        End With
    End With
End If

For 3), the number at the end will probably be the issue. Since you set MatchWholeWord = True in the code, the superscript causes the find to fail because it's looking for the whole word, and with the number, the whole word is not found. When you do the finding manually, you might not have the checkbox selected for matching the whole word. If possible, you can change the code to set it to false as long as whatever you are finding is not found in a false positive location.

However, now that would change what code here needs to do. What I mean is that if you were to find the part of the word (presumably the beginning of the word up until the superscript), simply collapsing the selection as I have it would cause the pasting to happen just prior to the superscript, moving it to after the pasted text. So you'd need to move the selection to the end after the superscript. That can be done a couple of different ways but it depends on the found text. Is it on a line by itself? Does it have a puctuation or a space directly after? How many possible numbers could be in the superscript? Etc.

 

[crazydragon84]

Thanks for this, i'm going to try your code out in a moment, but to answer your questions about no.3.

I took out all the .matchcase and .wholeword because i didn't need it, because with the number at the end there would be no other match in the document anyways. ok to give a little more info, the sections are actually titled "New Deals" and "Exited Deals". Each time and for each documents i have different number of New Deals and Exited Deals. Some times there are neither (in which case based on your codes i was able to figure out how to treat this case), sometimes only one of the two, or sometimes there are both. The Exited Deals is the one that will say "Exited Deals#" (which is what's stored in ED_Text). New Deals do not have a superscript. However, it is entirely possible that the words "Exited Deals" without the number appears somewhere else on the document, but i get past that on the "New Deals" by matching case because if it were anywhere else on the document it would be "New deals" so with exited deals i can technically do the same (but there will always be 1 number after the text). Actually, the pasting should show up on the next line as "New Deals" and "Exited Deals" is a header and below that is where i list what i copy from excel. Basically the excel stores list of all new and exited deals that needs to be pasted into these two sections. Let me know if that makes sense. I will try out your code now for no.1 and 2.

 

[crazydragon84]

ok i just tried your new code it works for the formating and pasting after! Thanks so much for this. I need to rethink how the data is brought in from excel as it pastes all the ranges as one clump so looks like one huge paragraph, but i can fix that on my own.

In word, when i look for "Exited Investments5" with match case, i do find it, but the "Find Whole Words Only" is grayed out and i can't select it. so maybe having the number is the issue. so the best solution would be to look for "Exited Investments" with match case and then pasting the data into the next line (or shift over 2 spaces to the right, whichever is easier). Thank you for your help again.

 

[crazydragon84]

One interesting thing to note though, is the even with the number i can find and replace the same exited deals# text...

This code works:

With wdFind                
                .Text = ED_Text
                .MatchCase = True
                .Replacement.Text = ""
                .Execute Replace:=wdReplaceOne, Forward:=True, Wrap:=wdFindContinue
                With appWd.Selection
                    .TypeBackspace
                    .TypeBackspace
                End With
            End With

 

[shknbk2]

As soon as you manually search for text with a space, the Match Whole Word becomes disabled. It's automatically not a whole word anymore.

However, that got me thinking whether searching for multiple words with MatchWholeWord set to True would work in code or not. It doesn't. Using random text, I set the .Text to be a string with two separate words and set MatchWholeWord to True. The second word was not the whole word but just the first letter. The code found it just fine. So I'm guessing that setting MatchWholeWord to True in code is ignored if there is a space in the .Text string as well. But, that then doesn't answer why you were having trouble before.