Selectively copy from Word to Excel Using VBA in Excel

[Sarvesh]

I would like to be able to copy all text associated with a particular
style into excel. I am able to open the document and do a find but
that does not solve my problem.

For example:

Document Heading A
[Normal] Some text in the document

Document Heading B
[Normal] Some text in the document

I would like to be able to copy text associated with say the style

. So in effect on execution I would get:
[Cell(1,1)] "Document Heading A"
[Cell(2,1)] "Document Heading B"

Any help would be much appreciated.​

 

[Cindy Meister -WordMVP-]

Hi Sarvesh,

I would like to be able to copy all text associated with a particular
style into excel. I am able to open the document and do a find but
that does not solve my problem.

Which VERSION of Word are we dealing with?

Why does FIND not solve your problem? Where, more exactly, are you
stuck? Have you seen the information on the mvps.org/word site dealing
with automating Excel from Word (or vice versa) and use Find/Replace?

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jan 24 2003)
http://www.mvps.org/word

This reply is posted in the Newsgroup; please post any follow question
or reply in the newsgroup and not by e-mail

 

[Sarvesh]

Hi Cindy,
I am using Excel2002 and Word 2002 (Office XP). I face two problems.
The primary one is how to copy the entire paragraph associated with a
style into Excel and the secondary is that Excel crashes on execution
of the Find in Word. Here is a snippet of my test code (I will have to
enhance to copy instead of replace for my needs).

Sub CopyFromWord()
Dim wrdApp As Word.Application
-- Cut out code for error checking prior to opening word document

wrdApp.Documents.Open ("D:\temp\SBS_Enhancements_PDSN_List.doc")
'****The statement below causes the crash****
'***Run Time Error '5': Invalid procedure call or argument ***
wrdApp.Selection.Find.ClearFormatting
wrdApp.Selection.Find.Replacement.ClearFormatting
With wrdApp.Selection.Find
.Text = "R1"
.Replacement.Text = "XXXX"
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
wrdApp.Selection.Find.Execute Replace:=wdReplaceAll

 

[Cindy Meister -WordMVP-]

Hi Sarvesh,

Excel crashes on execution
of the Find in Word

The answer to this one (if it's what I think) is straightforward and
short, so I'll take it first:

BUG: Automation Client Receives Error Calling Word Find Object
[Q292744]
http://support.microsoft.com?kbid=292744

I am using Excel2002 and Word 2002 (Office XP).
how to copy the entire paragraph associated with a
style into Excel

With wrdApp.Selection.Find
.Text = "R1"
.Replacement.Text = "XXXX"

OK, go into Word and look at the FIND dialog box; expand it ("More") so
that you can use all the functionality. Make sure the cursor is in the
"Find" box and delete anything in it. Also click the "Clear formatting"
button if there's anything in the label just below the "Find" box.

Now click the FORMAT button, choose "Styles" and pick the style name
you want to search for. Now, when you use "Find next", you should hit
each instance of text formatted with this style.

Once you have this working to your satisfaction, record the steps in a
macro to get the basic syntax you need. Then, you'll want to place it
in a Do...Loop so that you can repeatedly "find", take the found text
into Excel, and repeat until the end of the document. If you're not
sure how to build the loop, take a look at the information at
mvps.org/word.

The basic syntax for transferring the TEXT to an cell in Excel would
be, very roughly:

ActiveWorksheet.range("A1:A1").Value = wdApp.Selection.Range.Text

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Sep 30 2003)
http://www.mvps.org/word

This reply is posted in the Newsgroup; please post any follow question
or reply in the newsgroup and not by e-mail

 

[Sarvesh]

Hi Cindy,
Thanks a bunch. The microsoft bulletin suggested late binding which
solved my crash problem and now I am able to find text associated with
a particular style and paste it into Excel. Here is the essence of my
code for your perusal (I have omitted the bells and whistles relating
to user forms and formatting).

Sub CopyFromWord()
Dim wrdApp As Object
' Note: Using late binding (as opposed to Dim wrdApp AS
Word.Application) due to a bug
' Details at http://support.microsoft.com?kbid=292744
Dim wrdDoc As Object
Dim IsWordRunning As Boolean
Dim row As Integer

IsWordRunning = ApplicationIsRunning("Word.Application")
If IsWordRunning = True Then
Set wrdApp = GetObject(, "Word.Application")
Else
Set wrdApp = CreateObject("Word.Application")
End If
wrdApp.Visible = True
Set wrdDoc = wrdApp.Documents.Open("D:\temp\SBS_Enhancements_PDSN_List.doc")

wrdApp.Selection.Find.ClearFormatting
wrdApp.Selection.Find.Style = "Requirement"
'wrdApp.Selection.Find.ParagraphFormat.Borders.Shadow = False
With wrdApp.Selection.Find
.text = ""
.Replacement.text = ""
.Forward = True
.Wrap = wdFindContinue
.Format = True
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
row = 0
Do While wrdApp.Selection.Find.Execute = True
row = row + 1
Worksheets("Sheet1").Cells(row, 1).Value =
wrdApp.Selection.Range.text
Loop

wrdApp.Quit ' close the Word application

Hi Sarvesh,

Excel crashes on execution
of the Find in Word

The answer to this one (if it's what I think) is straightforward and
short, so I'll take it first:

BUG: Automation Client Receives Error Calling Word Find Object
[Q292744]
http://support.microsoft.com?kbid=292744

I am using Excel2002 and Word 2002 (Office XP).
how to copy the entire paragraph associated with a
style into Excel

With wrdApp.Selection.Find
.Text = "R1"
.Replacement.Text = "XXXX"

OK, go into Word and look at the FIND dialog box; expand it ("More") so
that you can use all the functionality. Make sure the cursor is in the
"Find" box and delete anything in it. Also click the "Clear formatting"
button if there's anything in the label just below the "Find" box.

Now click the FORMAT button, choose "Styles" and pick the style name
you want to search for. Now, when you use "Find next", you should hit
each instance of text formatted with this style.

Once you have this working to your satisfaction, record the steps in a
macro to get the basic syntax you need. Then, you'll want to place it
in a Do...Loop so that you can repeatedly "find", take the found text
into Excel, and repeat until the end of the document. If you're not
sure how to build the loop, take a look at the information at
mvps.org/word.

The basic syntax for transferring the TEXT to an cell in Excel would
be, very roughly:

ActiveWorksheet.range("A1:A1").Value = wdApp.Selection.Range.Text

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Sep 30 2003)
http://www.mvps.org/word

This reply is posted in the Newsgroup; please post any follow question
or reply in the newsgroup and not by e-mail