Mw Word Splicing

[conte]

I have a word document that includes the report of about 200 customers. Each customer has a report sizevarying from 2 to 6 pages. I have the names of these customers stored in MS Excel. I would like to write a macro to delete the report of some customers automatically. The number of customers report to be deleted varies from 10 to 100. If someone can help me write a macro, I will apreciate it.

Thanks

 

[TonyJollans]

Hi conte,

It's not too difficult if you can identify the start and end positions of individual customer's reports. If you post some info on this I will do something for you. Meanwhile some (very) simple pseudocode for it is:

For each name in Excel list
Find start of customer report
Save start pos (Selection.Start)
Find end of customer report
Save end pos (Selection.End)
Delete Range(savedstart, savedend)
Next name

If all you want is some help with a particular part of it, just name the part.

Enjoy,
Tony

 

[conte]

Tony,

This is another issue. We run these reports almost 2-3 times a week and the length of each cutomer report is different every time we run. Lets say ABC Company has 2 pages on today's report, but tomorrow the same company may have report of 3 or 4 pages. And it is not easy to anticipate the number of page unless you count the number of appreance of the customer names in the whole report, because, the customer name appears at the same position on each page.

 

[TonyJollans]

Hi conte,

It doesn't matter how often you run it, nor how long any individual customer report is. What matters is whether you can identify exactly the start and end of any report to be deleted. If each report starts on a new page and the customer name is in the same place on every page that would probably be enough. Let us know.

Enjoy,
Tony

 

[conte]

Hi Tony,

The report works exactly the same as you say. Each report starts on a new page and the customer name is in the same place on every page. What should the code look like then?

Thanks

 

[TonyJollans]

Hi conte,

You’re not giving me a lot to go on, but say:

(a) You have a list of Customer Names in Column A of your WorkBook
(b) Each page of your Report contains Customer Name somewhere

The code below should do what you want, but a couple of points first

(a) It would be easier if, for example, the Customer name was known to be always the first thing on the page.
(b) The code may fail if one Customer name contains another, for example Widgets and Widgets (UK)
(c) This searches for each Customer from the top of the document. If you know it’s ordered in the same way as the worksheet, for example, it could be made more efficient

  Dim Excel As Excel.Application
    
  Dim ExcelCell As Excel.Range
  Dim ExcelCustomer As String
  Dim CustomerPageStart As Long
  Dim PageStart As Long
    
  Application.ScreenUpdating = False
    
  With Selection.Find
    
    .Replacement.Text = ""
    .Forward = True
    .Wrap = wdFindStop
    .Format = False
    .MatchCase = False
    .MatchWholeWord = False
    .MatchWildcards = False
    .MatchSoundsLike = False
    .MatchAllWordForms = False
        
  End With
        
  Set Excel = CreateObject("Excel.Application")
    
  Excel.Workbooks.Open FileName:=”

D:\My Documents\CustList.xls

"

  For Each ExcelCell In Excel.Range(Excel.[

A

1], Excel.[

A

65536].End(xlUp))
        
    ExcelCustomer = ExcelCell.Text
        
    Selection.HomeKey Unit:=wdStory
    
    Do: With Selection

' Find first occurrence of Customer, if any

      .Find.Execute FindText:=ExcelCustomer
      If Not .Find.Found Then Exit Do

' Back to start of page, note position

      .GoTo wdGoToPage, wdGoToAbsolute, .Information(wdActiveEndPageNumber)
      CustomerPageStart = .Start
            
      Do While .Find.Found

' Next page, BUT just save position and quit looking if end of Doc

        If .Information(wdNumberOfPagesInDocument) = .Information(wdActiveEndPageNumber) Then
            NextPageStart = ActiveDocument.Range.End
            Exit Do
        Else
            .GoTo What:=wdGoToPage, Which:=wdGoToNext
        End If
        NextPageStart = .Start

' Look for Customer in range to first of (next page and end of document)

        If .Information(wdNumberOfPagesInDocument) = _
                .Information(wdActiveEndPageNumber) Then
            Selection.EndKey Unit:=wdStory
        Else
            .GoTo What:=wdGoToPage, Which:=wdGoToNext
        End If
        ActiveDocument.Range(NextPageStart, .End).Select
        .Find.Execute
            
      Loop
            
      ActiveDocument.Range(CustomerPageStart, NextPageStart).Delete
            
    End With: Loop
        
  Next
    
  Excel.Quit
  Set Excel = Nothing

  Application.ScreenUpdating = True

Enjoy,
Tony

 

[fumei]

That may take care of the Excel part, but depending on how you have the Word file pulling in the individual reports, it may be easier to use bookmarks.

If your Word file has bookmarks (e.g. BlahCustomer_Start and BlahCustomer_End) to indicate where that customer's report will fit - it is both easier to insert the report...and to delete it.

Dim CustomerDoc As Document
Dim DeleteRange As Range
Set CustomerDoc = ActiveDocument
Set DeleteRange = CustomerDoc.Range(CustomerDoc.Bookmarks("BlahCustomer_Start&quot.Range.Start, _
CustomerDoc.Bookmarks("BlahCustomer_End&quot.Range.End)
DeleteRange.Delete


Then if you want to collect an array of whatever number of customer reports to delete (or in fact to insert) just make an array, or collection, of the bookmark names and loop through it.