Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Word / Programming / June 2005

Tip: Looking for answers? Try searching our database.

Macro: search and transfer text strings from Word to Excel

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
mindful_learner - 01 Jun 2005 13:01 GMT
Hi everyone,

Apologies in advance if this is the wrong forum for this question.

I need a simple macro to search for certain text strings in a document
and then copy these instances into an Excel document.  I'm presuming
this is a rather simple task to do.  I don't have time to learn VBA to
do this one-off task and I wondered if anyone had some code I could
steal/modify.  Grateful thanks in advance if anyone can be so kind to
help.

Here's what I need in more detail:

I have a word document which contains text organised around headings.
For example:

Page 1
Text: a text string
Graphic: a graphic name.

I want a macro that will search for all instances of the word 'Graphic'

and then copy the text string that comes after this, which then needs
to be put in an Excel sheet.  The word 'Graphic' and the following text

string is always in a table row.  The text string after the word
'Graphic' always ends in a full stop.  The word Graphic is always
followed by a colon.

Added complication! Sometimes the word graphic is 'Graphic_1:' or
'Graphic_2:' etc, so the initial search would have to be something like

SEARCH FOR 'Graphic*:' (i.e. use * as a wildcard character).

It would be nice if the final excel sheet could group found entries by
the page they were found on Word.  E.g. if the Word document had this:

Page 1
Graphic_1: cat
Graphic_2: dog

Page 2
Graphic_1: apple
Graphic_2: pear

The final Excel sheet would have a Graphic column with

cat
dog
---- space----
apple
pear

I know this is a lot to ask, but you'd really be saving me a ton of
time.  I just don't have time to start learning macro programming for
this single-task.  If anyone can give me anything to get me started it
would be much appreciated.  I have programmed in C and C++ before, so I

can understand any general programming instructions you need to give
me.

Kind regards
Helmut Weber - 01 Jun 2005 15:59 GMT
Hi,

>The word 'Graphic' and the following text string
>is always in a table row.

In one (1) cell or in two different cells?

Greetings from Bavaria, Germany

Helmut Weber, MVP
"red.sys" & chr(64) & "t-online.de"
Word XP, Win 98
http://word.mvps.org/
mindful_learner - 01 Jun 2005 17:27 GMT
Hi Helmut,

The word 'Graphic' and the following text string are always in one (1)
cell.  The delimiter between the two words is the colon (:) after the
word 'Graphic'.  To summarise, I'd need some kinda algorithm like the
following (excuse the appaling pseudo code - i just want to get the
point across).

For (the whole document/each page)
  Find each instance of text string 'Graphic*:'
  Copy text after colon (:) until full stop (or end of table row)
  Paste text string into new Excel cell in Column 'A'
  Enter blank row into Excel each time new page is encountered
End For

There! Terrible algorithm, but hopefully gets the point across ;0)

Many thanks,
mindful
Helmut Weber - 02 Jun 2005 16:39 GMT
Hi,
I think a lot at once is rarely a good idea. ;-)
1st, to get the strings to be transferred to Excel:

Sub test783()
Dim rTmp As Range
Dim s As String ' String for excel
Set rTmp = ActiveDocument.Range
ResetSearch
With rTmp.Find
  .Text = "Graphic*:"
  .MatchCase = True
  .MatchWildcards = True
  While .Execute
     rTmp.Collapse direction:=wdCollapseEnd
     .Text = "*."
     .Execute
     s = Trim(rTmp.Text)
     ' to get rid of leading and trailing spaces
     s = Left(s, Len(s) - 1)
     ' to get rid of the trailing full stop
     MsgBox "transfer to Excel: " & s
     rTmp.Collapse direction:=wdCollapseEnd
     rTmp.End = ActiveDocument.Range.End
     .Text = "Graphic*:"
  Wend
End With
ResetSearch
End Sub
Public Sub ResetSearch()
With Selection.Find
  .ClearFormatting
  .Replacement.ClearFormatting
  .Text = ""
  .Replacement.Text = ""
  .Forward = True
  .Wrap = wdFindContinue
  .Format = False
  .MatchCase = False
  .MatchWholeWord = False
  .MatchWildcards = False
  .MatchSoundsLike = False
  .MatchAllWordForms = False
  ' plus some more if needed
  .Execute
End With
End Sub

Then you would need something which tells you,
whether you are on a new page.
OK, start with page 1, the initial value.
Check whether rTmp.Information(wdActiveEndPageNumber)
is equal to initial value. If so, you are on the same page,
if not you are on a new page. Then set the inital value to
the new page.

Sub test784()
Dim rTmp As Range
Dim lPg1 As Long
Dim lPgx As Long
Set rTmp = ActiveDocument.Range
lPg1 = 1
ResetSearch
With rTmp.Find
  .Text = "the"
  While .Execute
     lPgx = rTmp.Information(wdActiveEndPageNumber)
     If lPgx <> lPg1 Then
        MsgBox "Old page = " & lPg1 & " New Page = " & lPgx
        lPg1 = lPgx
     End If
  Wend
End With
ResetSearch
End Sub

Of course, in no way all you want.
But I'd say, try to get comfortable with that at first,
and then ask again.
Without learning some VBA it'll be hard.

Greetings from Bavaria, Germany

Helmut Weber, MVP
"red.sys" & chr(64) & "t-online.de"
Word XP, Win 98
http://word.mvps.org/
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.