in an excel macro can you import data from word into a cell?

T

Trefor

I have several word documents being emailed to me containing tables. I would
like to be able to import some of the data in the word table into a cell in
my spreadsheet. Is this possible using a macro in the spreadsheet?
 
H

HiArt

hi Trefor,

Yes, that's one of the advantages of the MS Office Family!

Look in Excel VBA help "Controlling One Microsoft Office Applicatio
from Another".

A quick search in Google found this
http://www.mindspring.com/~tflynn/excelvba2.html#Open_Word.

You'd replace the "With mywdRange" with code appropraite to you
problem. It's fairly straight-forward if you know Word VBA. Once wor
is open use:

Selection.Tables(1).Select ' goes to a specified table occurence
Selection.SelectRow
Selection.Copy

The switch back to Excel and Paste.

HTH

Ar
 
T

Trefor

Thankyou for the reply, but I must be missing something here. I am still
learning about macros/VBA in Excel and no nothing about macros/VBA in Word.

I tried to run the code and got an error:

Dim wdApp As Word.Application >> User-defined type not defined.
 
D

Dave Peterson

Click on Tools|References
Then scroll down looking for:
Microsoft Word xx.0 Object library

(xx.0 will change with the version of word/office you have.)
 
T

Trefor

Dave, thankyou that fixed that problem, but the bigger issue I am still
having problems with. I have searched around here and found many people
looking for the same thing (using keywords: grab, copy, import, extract)

To explain what I am trying to do in more detail: (using Office 2003)

I would like to run a macro in Excel that:

1. Checks to see if there is an open Word document. (I don’t want to specify
a name or have to open the file from within Excel)

2. Select and copy the data from word to excel. (The word documents (for
some reason beyond my control) have text boxes set in autoshapes to look like
tables, each cell within the “table†has a text box and the text box has
several lines of text in it (names and addresses for example))

3. I am guessing for simplicity it would be easier to just grab everything,
then paste it into a "scratch" area in excel, and then I can pick and choose
what I want from the scratch area.

I am fairly happy with macro's/VBA in excel, what I am not familiar with is
VBA in Word or switching between the two.

Any help very much appreciated.
 
D

Dave Peterson

This will check to see if MSWord is running and check to see if there's an
active document in word:

Option Explicit
Sub testme01()

Dim WDApp As Object
Dim WDDoc As Object

Set WDApp = Nothing
On Error Resume Next
Set WDApp = GetObject(, "Word.Application")
If Err.Number <> 0 Then
MsgBox "Word isn't running!"
Exit Sub
End If

WDApp.Visible = True 'at least for testing, just to make sure

Set WDDoc = Nothing
On Error Resume Next
Set WDDoc = WDApp.activedocument
On Error GoTo 0

If WDDoc Is Nothing Then
MsgBox "No activedocument in Word"
Exit Sub
End If

End Sub

When I need to do stuff in word, I usually record a macro when I do it
manually. Then I struggle to make it work the way I want.

If you do that and still have trouble, you can post back--or post to an MSWord
newsgroup.


Dave, thankyou that fixed that problem, but the bigger issue I am still
having problems with. I have searched around here and found many people
looking for the same thing (using keywords: grab, copy, import, extract)

To explain what I am trying to do in more detail: (using Office 2003)

I would like to run a macro in Excel that:

1. Checks to see if there is an open Word document. (I don’t want to specify
a name or have to open the file from within Excel)

2. Select and copy the data from word to excel. (The word documents (for
some reason beyond my control) have text boxes set in autoshapes to look like
tables, each cell within the “table†has a text box and the text box has
several lines of text in it (names and addresses for example))

3. I am guessing for simplicity it would be easier to just grab everything,
then paste it into a "scratch" area in excel, and then I can pick and choose
what I want from the scratch area.

I am fairly happy with macro's/VBA in excel, what I am not familiar with is
VBA in Word or switching between the two.

Any help very much appreciated.
Click to expand...
 
T

Trefor

Dave,

Many thanks, I am heading in the right direction I think. I do seem to be
having problems with the Word macro's. If I record a macro in Word, should I
simply be able to copy the recorded macro from Word to Excel and run it from
Excel? If so, it's not working. I get "error 438 - Object doesn't support
this property or method" when trying to "Selection.WholeStory" for example.

In playing with this I have also noted that the Word (.RTF) file was created
with "Oracle Reports" does this change anything (or should I post on the Word
discussion group).
 
D

Dave Peterson

Excel and Word both use Selection.

If you don't qualify it in a macro in excel, then excel will think you mean you
want to use the excel "version".

wddoc.selection.wholestory

may work ok (with wdDoc like in the sample code).

Word also will record constants that excel doesn't know about. You could either
translate those constants into numbers or set a reference (tools|references|and
check "Microsoft Word x.xx Object library".

And I would think that if the .rtf file was already open in MSWord, then you
shouldn't have a problem--but that's more of a guess. Either try it and see or
post to the Word newsgroup.
Dave,

Many thanks, I am heading in the right direction I think. I do seem to be
having problems with the Word macro's. If I record a macro in Word, should I
simply be able to copy the recorded macro from Word to Excel and run it from
Excel? If so, it's not working. I get "error 438 - Object doesn't support
this property or method" when trying to "Selection.WholeStory" for example.

In playing with this I have also noted that the Word (.RTF) file was created
with "Oracle Reports" does this change anything (or should I post on the Word
discussion group).
Click to expand...
 
T

Trefor

Dave,

Actually I added the following to your sample code:

Dim mywdRange As Word.Range
With mywdRange
Selection.WholeStory <------ This Line Errors with 438

Selection.Tables(1).Select <------ This Line Errors with 438
Selection.SelectRow
Selection.Copy
End With

WDDoc.Selection.WholeStory <------ This Line Errors with 438

"Microsoft Word x.xx Object library". <--- I had already set this

I just tried creating a new word doc, with just one word on a blank page,
same problem. I will post to the Word newsgroup.

Many thanks for your assistance.
 
D

Dave Peterson

I think posting in the MSWord newsgroup is an excellent idea.

But after struggling with MSWord, this did work:

Option Explicit
Sub testme01()

Dim WDApp As Word.Application
Dim WDDoc As Word.Document
Dim myWDRange As Word.Range

Set WDApp = Nothing
On Error Resume Next
Set WDApp = GetObject(, "Word.Application")
If Err.Number <> 0 Then
MsgBox "Word isn't running!"
Exit Sub
End If

WDApp.Visible = True 'at least for testing, just to make sure

Set WDDoc = Nothing
On Error Resume Next
Set WDDoc = WDApp.ActiveDocument
On Error GoTo 0

If WDDoc Is Nothing Then
MsgBox "No activedocument in Word"
Exit Sub
End If

With WDDoc
Set myWDRange = .Range.Tables(1).Rows(1).Cells(1).Range
End With

ActiveCell.Value = myWDRange.Text

End Sub
Dave,

Actually I added the following to your sample code:

Dim mywdRange As Word.Range
With mywdRange
Selection.WholeStory <------ This Line Errors with 438

Selection.Tables(1).Select <------ This Line Errors with 438
Selection.SelectRow
Selection.Copy
End With

WDDoc.Selection.WholeStory <------ This Line Errors with 438

"Microsoft Word x.xx Object library". <--- I had already set this

I just tried creating a new word doc, with just one word on a blank page,
same problem. I will post to the Word newsgroup.

Many thanks for your assistance.
Click to expand...
 
T

Trefor

Dave, many thanks. Your latest code certainly works for a new sheet with a
table I created in it. It still does not work for me on my problem because I
have text boxes made to look like a table, but they are not actually a
tables. I will hope for a reply to my Word post and see if someone can help
me there. Thanks again
 
D

Dave Peterson

Good luck in the Word newsgroups.

You may want to search google to see if anyone had a suggestion to a similar
problem.
Dave, many thanks. Your latest code certainly works for a new sheet with a
table I created in it. It still does not work for me on my problem because I
have text boxes made to look like a table, but they are not actually a
tables. I will hope for a reply to my Word post and see if someone can help
me there. Thanks again
Click to expand...
 
Top