none
Help With Find/Replace RRS feed

  • Question

  • Hi Guys,

    I’ve been giving the task to clean up some invoices and I need some help.

    The task in general consist in replacing the company header which has the company name and the wire banking information. Now, the company header coming out from the sys is placed in different locations throughout the entire document. 

    Now, I was reading the “Writing Word Macros” by Steven Roman and noted the following search code and made few changes to work for my scenario.

     
    Sub GetInv() 
    
    On Error GoTo Err_Handler 
    
    '//Set search ranges 
    
    Set docA = ActiveDocument 
    
    Set rngToSrch = docA.Range 
    
    Set rngResult = rngToSrch.Duplicate 
    
    intCount = 0 
    
    '//Set extra wide page margins 
    
    With docA.PageSetup 
    
        .TopMargin = InchesToPoints(0.5) 
    
        .BottomMargin = InchesToPoints(0.5) 
    
        .LeftMargin = InchesToPoints(0.5) 
    
        .RightMargin = InchesToPoints(0.5) 
    
    End With 
    
    '//Loop to find all "Wire..." 
    
    Do 
    
        StatusBar = "Hearders Found : " & intCount 
    
        DoEvents 
    
        With rngResult.Find 
    
            .ClearFormatting 
    
            .Text = "Wire Remittance Instructions:" 
    
            .Format = True 
    
            .Wrap = wdFindStop 
    
            .MatchWildcards = True 
    
            .Execute 
    
        End With 
    
        '//Exit loop if not found 
    
        If (Not rngResult.Find.Found) Then 
    
            Exit Do 
    
        End If 
    
        '//Select text 
    
        With rngResult 
    
            .Select 
    
            With Selection 
    
                .Collapse wdCollapseStart 
    
                .Bookmarks.Add "C" & intCount 
    
            End With 
    
            '//Prepare for next search by 
    
            '//moving the start position over one word 
    
            .MoveStart wdWord 
    
            '//and extending the end of rngresult 
    
            .End = rngToSrch.End 
    
        End With 
    
        intCount = intCount + 1 
    
    Loop Until Not rngResult.Find.Found 
    
    
    Err_Exit: 
    
        Exit Sub 
    
    Err_Handler: 
    
        Resume Err_Exit 
    
    End Sub 
    

    Now, the code simple searches for the following:

    “Wire Remittance Instructions:”

    Now, the problem begins when the header is inserted inside the invoice itemization. Example:

    01/21/2013 Blah Blah Blah ….. Testing

    Blank

    Blank

                            Wire Remittance Instructions:

                            ----------------------------------------

                            Banksomething

                            Banksomething

                            Banksomething address

                            Banksomething

                            Banksomething

                            Banksomething

    Blank

    Blank

    01/22/2013 Blah Blah Blah ….. Testing

    Now, the only way I found was here in this part of the code:

        With rngResult

            .Select

            With Selection

                .Collapse wdCollapseStart

                     1. Go back to the previous line that is not black, check if the line begins with a date.

                     2. If it returns true, then 

                            a. Delete everything between the two dates. (including the header)

                     3. If it returns false, then (this means the beginning of a new invoice)

                           a. Insert a page break

                           b. Replace the header with the correct header

                     4. Delete any blank pages.

            End With

            '//Prepare for next search by

            '//moving the start position over one word

            .MoveStart wdWord

            '//and extending the end of rngresult

            .End = rngToSrch.End

    Hope this is something doable. Any help will be appreciated.

    SD


    The computing scientist's main challenge is not to get confused by the complexities of his own making.

    Saturday, May 11, 2013 12:27 AM

Answers

  • It really doesn't matter what the document is created by; what matters is what it contains. It also doesn't matter what the data look like in Word; what matters is what the data are.

    And let's deal with one problem at a time.

    You say the Find/Replace didn't do anything. Did you check the 'use wildcards' option? If you did it would certainly have deleted the 'Wire Remittance' details for the example in your second post.

    For deleting the 'Wire Remittance' details for an example like the second one in your third post (assuming it too is followed by an 'Invoice Number' line), try changing the wildcard Find/Replace expression to:
    Find = ([!0-9//]{10}[!^13]{1,}^13)[!0-9//]{1,}[!^13]{1,}Wire Remittance Instructions*([ ]{1,}Invoice Number[!^13]{1,}^13)
    Replace = \1\2 (to keep the 'Invoice Number' line)
    or
    Replace = \1 (to delete the 'Invoice Number' line)


    Cheers
    Paul Edstein
    [MS MVP - Word]


    Tuesday, May 14, 2013 12:40 AM

All replies

  • If you could post some meaningful examples of the data you're working with (obfuscate anything sensitive), it'd be a lot easier to come up with a solution. Provided there are some consistent relationships amongst the data to be replaced it shouldn't be too difficult.

    Cheers
    Paul Edstein
    [MS MVP - Word]

    Saturday, May 11, 2013 12:54 AM
  • Hi Paul,

    First thanks a lot for the help. I really would like to show you the original doc, but I can get in so much trouble.

    But, I'd like to create something similar.

    1. I noted the system is consistent. It places the headers in two different locations.

    1. At the beginning of the invoice
    2. In the middle of the invoice explanation.

    Now, The code goes thru every page finding every header with no problem. I tried adding the following to the code but It does not do anything.

        With rngResult
            .Select
            With Selection
                .MoveUp Unit:=wdLine, Count:=1
                Do
                    .MoveUp Unit:=wdLine, Count:=1, Extend:=wdExtend
                    intUp = intUp + 1
                    If (.Bookmarks("\Line").Empty = False) Then
                        If (Len(.Bookmarks("\Line")) <> 0) Then
                            Exit Do
                        End If
                    End If
                Loop
                MsgBox intUp
            End With
            '//Prepare for next search by
            '//moving the start position over one word
            .MoveStart wdWord
            '//and extending the end of rngresult
            .End = rngToSrch.End
        End With

    Not sure why is not moving up. The scenario will look as follow:

    I don't know If because I'm moving up a line my selection is losing the range.

    SD


    The computing scientist's main challenge is not to get confused by the complexities of his own making.

    Sunday, May 12, 2013 6:37 PM
  • In our first post you said:
    the company header which has the company name and the wire banking information
    You now say:
    the system is consistent. It places the headers in two different locations
    but there's nothing I can see in the example you posted that looks anything like a company name for the header; only the 'Wire Remittance Instructions' reference.

    Based on the limited sample data you have posted, if you want to delete everything between the 'Wire Remittance Instructions' and the line that has 'Invoice Number', that is easy enough with a wildcard Find/Replace using:
    Find = [ ]{1,}Wire Remittance Instructions*([ ]{1,}Invoice Number[!^13]{1,}^13)
    Replace = \1
    If you want to delete the 'Invoice Number' line as well, omit the Replace expression.

    Similarly, if you want to also delete everything between the
    '04/03/2013 AAAAAAA     .20 Prepared correspondence to client'
    line and the 'Wire Remittance Instructions' line you could use:
    Find = ([!0-9//]{10}[!^13]{1,}^13)[!0-9//]{1,}[ ]{1,}Wire Remittance Instructions*([ ]{1,}Invoice Number[!^13]{1,}^13)
    with
    Replace = \1\2 (to keep the 'Invoice Number' line)
    or
    Replace = \1 (to delete the 'Invoice Number' line)

    No macro required - or you could record either of the above as a macro.


    Cheers
    Paul Edstein
    [MS MVP - Word]





    • Edited by macropodMVP Monday, May 13, 2013 12:00 AM Additional wildcard Find/Replace advice
    Sunday, May 12, 2013 11:40 PM
  • Hi Paul,

    Apologies, the sys does inserts a header but not the needed one. The below is the one inserted by the sys.

    Now the one needed is as follow:

    But the problem is that I need to find a way to test what position the header is currently located. I the only way is to test if the prior (not null/empty) wdLine start with a date value (maybe I can use IsDate())

    Then if there is a date, then delete between:

    '04/03/2013 AAAAAAA     .20 Prepared correspondence to client'

    And

    '04/03/2013 AAAAAAA     .20 Correspondence to attorney'

    Now, if there is no date at the prior (not null) line then insert a page break.

    Thanks a lot

    SD


    The computing scientist's main challenge is not to get confused by the complexities of his own making.

    Monday, May 13, 2013 12:45 AM
  • Did you try the second set of wildcard Find/Replace instructions from my previous post?

    FWIW your posts are confusing. Without seeing more data (preferably not as an image, but as actual text that can be worked with), it's hard to understand the different scenarios you are describing.


    Cheers
    Paul Edstein
    [MS MVP - Word]

    Monday, May 13, 2013 12:57 AM
  • Hi Paul,

    I did try your recommendation but nothing happens.

    Let me try explain. The document is generated by and old sys. It generates many invoices in a single word document. This particular doc has 137 pages and the invoices are structured as follow:

    Front Page

    Explanation Page or Pages

    This means that each invoice can have 1 cover and many explanation pages. Then at the end of every explanation page or pages begins another invoice. So, they are all mixed.

    Now, the goal of this automation is generally to organize them all.

    Now, what I think it's happening is that the system is having problems with page margins, it is inserting the header at the begin of every page, but because the margins are not matching it looks like it's inserted right in the middle of the page. (Tried playing with margins, but no luck. Nothing is matching)

    That's why there are two scenarios.

    1. The first scenario is where the system header is inserted at the beginning of the invoice cover. Now, because of the margins issue sometimes the invoice can start right at the middle of a page or ending of the prior invoice last explanation page. That's the scenario where I wanted to insert a page break.

    2. The second scenario is when the header is showing right in the middle of the explanation. This means the system finished the page, entered a page break then started a new page posting the header and continued with the explanation. That's the scenario where I wanted to remove the header and merge the explanation.

    Please let me know If I'm not making sense. My English is not the best.

    SD


    The computing scientist's main challenge is not to get confused by the complexities of his own making.

    Monday, May 13, 2013 1:58 PM
  • It really doesn't matter what the document is created by; what matters is what it contains. It also doesn't matter what the data look like in Word; what matters is what the data are.

    And let's deal with one problem at a time.

    You say the Find/Replace didn't do anything. Did you check the 'use wildcards' option? If you did it would certainly have deleted the 'Wire Remittance' details for the example in your second post.

    For deleting the 'Wire Remittance' details for an example like the second one in your third post (assuming it too is followed by an 'Invoice Number' line), try changing the wildcard Find/Replace expression to:
    Find = ([!0-9//]{10}[!^13]{1,}^13)[!0-9//]{1,}[!^13]{1,}Wire Remittance Instructions*([ ]{1,}Invoice Number[!^13]{1,}^13)
    Replace = \1\2 (to keep the 'Invoice Number' line)
    or
    Replace = \1 (to delete the 'Invoice Number' line)


    Cheers
    Paul Edstein
    [MS MVP - Word]


    Tuesday, May 14, 2013 12:40 AM
  • Hello,

    Since you haven't responded for a few days, I temporarily mark Paul's reply as answer because I think he has provided useful information for resolving your issue. If his suggestion does not help you work out the problem, please unmark it and post your doubt. Thanks for your understanding and support.

    Best Regards,


    Damon Zheng
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Sunday, May 19, 2013 6:23 PM
    Moderator