How to 1. Jump to a specific page (in a massive document) based on asearch then 2. Copy and paste a

[John]

BACKGROUND/CONTEXT

I am working from a very large word document, approximately 1000 pages
long. Each page has one 'article' on it.

I am currently doing something manually, which I would love to do
using a macro.

I am populating a table (which happens to be in an Excel spreadsheet)


One column is the Article number 'xxxxx'. One of the other columns is
a 'Topic' which is typically three alphanumeric characters (like AA1,
AD4, BB4 DO6 etc.)



PROBLEM/TASK

This is what i have to do

Step 1 - Jump to the page with the article number. Achieved by
pressing control-f and searching for '10022' or 'Article: 10022'.

Step 2 - I press the 'up' arrow key (about) four times to reach a line
called 'Topic: AB1'. I then copy 'AB1' to the clipboard and paste it
into an Excel spreadsheet.


This is obviously a complex task to automate, so any hints/tips would
be greatly appreciated.




Thanks for any hints/tips

 

[Doug Robbins - Word MVP]

Is it always Topic AB1 that you are wanting to copy? Is the Topic always on
the same page as the Article? Do you need to do it for multiple Articles
(which would be the only reason that I could see for bothering to automate
the task)? If so, from where will those Article Numbers be obtained? Are
they already in the Spreadsheet? If so, is it that you have a spreadsheet
with a column of Article Numbers and other columns headed with the Topic
number and you want to populate the cells in the rows for each article with
each of the topics?

In other words, we really need to know exactly what it is that you want to
do?

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com

 

[John]

Is it always Topic AB1 that you are wanting to copy?  Is the Topic always on
the same page as the Article?  

Yes, it is always on the same page. It is not always AB1, it generally
to alpha characters (i.e. from A-Z) followed by one or two numbers.

Do you need to do it for multiple Articles
(which would be the only reason that I could see for bothering to automate
the task)?  

Yes, for several hundred

If so, from where will those Article Numbers be obtained? Are
they already in the Spreadsheet?

Yes, they are in a spreadsheet. (The numbers are chosen by a 3rd
party)

If so, is it that you have a spreadsheet
with a column of Article Numbers and other columns headed with the Topic
number and you want to populate the cells in the rows for each article with
each of the topics?

Yes, exactly.

In other words, we really need to know exactly what it is that you want to
do?

I often find it hard to explain things.


Grateful for any advice/tips. (I have done it manually now but it
would be good to automate it in future)

Kind regards

 

[Doug Robbins - Word MVP]

That clarifies things somewhat.

Just what is it that you want to have copied and inserted into the
spreadsheet?

Using AB1 as an example, is there some text associated with AB1 that is to
be copied and how is it arranged with respect to the AB1? Is it in the same
paragraph as AB1? Is the whole of the paragraph to be copied?

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com

 

[John]

That clarifies things somewhat.

Just what is it that you want to have copied and inserted into the
spreadsheet?

Using AB1 as an example, is there some text associated with AB1 that is to
be copied and how is it arranged with respect to the AB1?  Is it in thesame
paragraph as AB1?  Is the whole of the paragraph to be copied?

This is the kind of layout I'm talking about:

==============================

blah blah blah blah blah
Date of article: 23 June 1999
Topic: AB1
Lorem ipsum dolor sit amet, consectetur adipiscing elit. Quisque erat
neque, tristique non gravida vitae, egestas in ligula. Duis et metus
in leo vestibulum suscipit. Aenean diam turpis, posuere ut vulputate
eu, bibendum ac arcu. Nulla in tempus odio. Nam eros orci, hendrerit
ut rutrum sit amet, tincidunt quis metus. Vestibulum ante ipsum primis
in faucibus orci luctus et ultrices posuere cubilia Curae; Maecenas
vitae tortor nec nibh fermentum ultricies. Vivamus at eros viverra dui
fermentum rutrum a vel est. Mauris sit amet lectus eget quam rhoncus
pharetra et interdum eros. Etiam justo mi, condimentum ac congue vel,
lobortis at diam. Proin commodo justo vitae lacus egestas
pellentesque. Aliquam et dolor eget enim gravida pulvinar volutpat sed
eros. Donec commodo nisl at lectus egestas gravida. Nulla dictum
blandit purus a faucibus. Nunc ultricies leo ut nibh consectetur
blandit. Morbi at nisi nulla. Phasellus sed nisi et nunc sagittis
aliquet ac sit amet tortor.

==============================


So, it is just the text 'AB1' that I need. There is no other text on
that line. No other text needs to be copied.

 

[Doug Robbins - Word MVP]

OK, so if I understand correctly, select the block of cells in the Excel
Spreadsheet (assumed to be the first one in the Work Book) that are formed
by the column of Section Numbers and the row of Topic tags and assign the
name of Matrix to it. Then save and close the workbook.

Then in Word, with the document containing the Section Numbers and the
Topics as the active document, run a macro containing the following code:

Dim i As Long
Dim j As Long
Dim xlapp As Object 'Excel.Application
Dim xlbook As Object 'Excel.Workbook
Dim xlsheet As Object 'Excel.Worksheet
Dim MatrixArray As Variant
Dim Source As Document
Dim srange As Range
Set Source = ActiveDocument
On Error Resume Next
Set xlapp = GetObject(, "Excel.Application")
If Err Then
Set xlapp = CreateObject("Excel.Application")
End If
On Error GoTo ErrMsg
With xlapp
'Modify the path and file name in this row so that it points to the
Excel Workbook
Set xlbook = .Workbooks.Open("C:\Users\Doug\Documents\matrixbook.xlsx")
Set xlsheet = xlbook.Worksheets(1)
MatrixArray = xlbook.Names("Matrix").RefersToRange.Value
For j = 2 To UBound(MatrixArray, 1)
With Source
Selection.HomeKey wdStory
Selection.Find.ClearFormatting
With Selection.Find
If .Execute(FindText:=MatrixArray(j, 1), Forward:=True, _
MatchWildcards:=False, Wrap:=wdFindContinue,
MatchCase:=False) = True Then
Set srange = Selection.Bookmarks("\page").Range
For i = 2 To UBound(MatrixArray, 2)
If InStr(srange, MatrixArray(1, i)) > 0 Then
xlsheet.Cells(j, i).Value = MatrixArray(1, i)
End If
Next i
End If
End With
End With
Next j
xlbook.Activate 'Close SaveChanges:=False
Set xlbook = Nothing
End With
Set xlapp = Nothing

ErrMsg:

If Err.Number > 0 Then
MsgBox Err.Number & vbCr & Err.Description
Exit Sub
End If

The above macro produced the following when run on a document containing a
section 10022 with a topic AD4 on that page

AA1 AD4 BB4 DO6
1002010021
10022 AD410023
1002410025
1002610027
1002810029


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com