Importing Word text into Excel
 

I need to import a number of text lines from Word file into Excel. Using what
I found from postings here on importing tables and the Word macro recorder I
have the following partial code:
FName = Application _
.GetOpenFilename("Word Files (*.doc), *.doc")
Set oWord = GetObject(FName)
With oWord.Find
.Text = "Seq."
.Replacement.Text = ""
....
End With 'oWord
Selection.Find.Execute
Selection.MoveRight Unit:=wdCharacter, Count:=66, Extend:=wdExtend
Selection.MoveDown Unit:=wdLine, Count:=100, Extend:=wdExtend
Selection.Copy
Cells("A1").Activate
ActiveSheet.Paste

However, Excel VBA doesn't know wdCharacter or wdLine. It doesn't know
wdForward if I try a Move function. The number of lines of text to import is
constant.

Any workarounds would be greatly appreciated!

Importing Word text into Excel
 

Excel doesn't know you're working with Word objects. You need to tell it.

You'll need to set a reference to the Word objects in the VBE using Tools -
References.

Select "Microsoft Word 11.0 Object Library" (for 2003)

This code should get you started

Sub Test()
Dim WordApp As Word.Application
Dim oDoc As Word.Document

Set WordApp = GetObject(, "Word.Application")
Debug.Print WordApp

If WordApp Is Nothing Then Exit Sub

For Each oDoc In WordApp.Documents
Debug.Print oDoc.Name
Next oDoc

Set WordApp = Nothing

End Sub

--
HTH,
Barb Reinhardt



"Bonsai Bill" wrote:

I need to import a number of text lines from Word file into Excel. Using what
I found from postings here on importing tables and the Word macro recorder I
have the following partial code:
FName = Application _
.GetOpenFilename("Word Files (*.doc), *.doc")
Set oWord = GetObject(FName)
With oWord.Find
.Text = "Seq."
.Replacement.Text = ""
...
End With 'oWord
Selection.Find.Execute
Selection.MoveRight Unit:=wdCharacter, Count:=66, Extend:=wdExtend
Selection.MoveDown Unit:=wdLine, Count:=100, Extend:=wdExtend
Selection.Copy
Cells("A1").Activate
ActiveSheet.Paste

However, Excel VBA doesn't know wdCharacter or wdLine. It doesn't know
wdForward if I try a Move function. The number of lines of text to import is
constant.

Any workarounds would be greatly appreciated!

Importing Word text into Excel
 

Thanks Barb, that did the trick! I knew the problem but didn't know the
solution.

I know I may be asking too much but is there a workaround for the coding?
The reason is that my users know zip about macros. Maybe the easiest thing to
do is to include it in the instruction writeup.

"Barb Reinhardt" wrote:

Excel doesn't know you're working with Word objects. You need to tell it.

You'll need to set a reference to the Word objects in the VBE using Tools -
References.

Select "Microsoft Word 11.0 Object Library" (for 2003)

This code should get you started

Sub Test()
Dim WordApp As Word.Application
Dim oDoc As Word.Document

Set WordApp = GetObject(, "Word.Application")
Debug.Print WordApp

If WordApp Is Nothing Then Exit Sub

For Each oDoc In WordApp.Documents
Debug.Print oDoc.Name
Next oDoc

Set WordApp = Nothing

End Sub

--
HTH,
Barb Reinhardt



"Bonsai Bill" wrote:

I need to import a number of text lines from Word file into Excel. Using what
I found from postings here on importing tables and the Word macro recorder I
have the following partial code:
FName = Application _
.GetOpenFilename("Word Files (*.doc), *.doc")
Set oWord = GetObject(FName)
With oWord.Find
.Text = "Seq."
.Replacement.Text = ""
...
End With 'oWord
Selection.Find.Execute
Selection.MoveRight Unit:=wdCharacter, Count:=66, Extend:=wdExtend
Selection.MoveDown Unit:=wdLine, Count:=100, Extend:=wdExtend
Selection.Copy
Cells("A1").Activate
ActiveSheet.Paste

However, Excel VBA doesn't know wdCharacter or wdLine. It doesn't know
wdForward if I try a Move function. The number of lines of text to import is
constant.

Any workarounds would be greatly appreciated!

Importing Word text into Excel
 

Hi Bill,

There are various way of approaching this, depending on how your Word document is set up.

For example, if the range you want to import is bookmarked in Word, and you want to link to it so that the Excel workbook will
update to reflect the bookmark's contents, you could use a formula in Excel like:
=Word.Document|'C:\My Documents\Wordfile.doc'!'!BkMrk'
where 'C:\My Documents\Wordfile.doc' is the document's path and name, and 'BkMrk' is the name of the bookmark.
No macro required.

Conversely, if the range you want to copy is for a one-off update, and there's always a specific start & end string (you've already
indicated '' as the start string), then a different kind of search in Word might be better. This is especially so since line counts
in Word often depend on the current printer driver.

--
Cheers
macropod
[MVP - Microsoft Word]


"Bonsai Bill" wrote in message ...
Thanks Barb, that did the trick! I knew the problem but didn't know the
solution.

I know I may be asking too much but is there a workaround for the coding?
The reason is that my users know zip about macros. Maybe the easiest thing to
do is to include it in the instruction writeup.

"Barb Reinhardt" wrote:

Excel doesn't know you're working with Word objects. You need to tell it.

You'll need to set a reference to the Word objects in the VBE using Tools -
References.

Select "Microsoft Word 11.0 Object Library" (for 2003)

This code should get you started

Sub Test()
Dim WordApp As Word.Application
Dim oDoc As Word.Document

Set WordApp = GetObject(, "Word.Application")
Debug.Print WordApp

If WordApp Is Nothing Then Exit Sub

For Each oDoc In WordApp.Documents
Debug.Print oDoc.Name
Next oDoc

Set WordApp = Nothing

End Sub

--
HTH,
Barb Reinhardt



"Bonsai Bill" wrote:

I need to import a number of text lines from Word file into Excel. Using what
I found from postings here on importing tables and the Word macro recorder I
have the following partial code:
FName = Application _
.GetOpenFilename("Word Files (*.doc), *.doc")
Set oWord = GetObject(FName)
With oWord.Find
.Text = "Seq."
.Replacement.Text = ""
...
End With 'oWord
Selection.Find.Execute
Selection.MoveRight Unit:=wdCharacter, Count:=66, Extend:=wdExtend
Selection.MoveDown Unit:=wdLine, Count:=100, Extend:=wdExtend
Selection.Copy
Cells("A1").Activate
ActiveSheet.Paste

However, Excel VBA doesn't know wdCharacter or wdLine. It doesn't know
wdForward if I try a Move function. The number of lines of text to import is
constant.

Any workarounds would be greatly appreciated!

Importing Word text into Excel
 

Hi macropod,

My application doesn't involve links and the desired text is not bookmarked.
However the last line always has the string of "tab80tab540" and could be
used to anchor the last line.

You mentioned that a different search method would be safer than using Find.
I checked Help but didn't find any promising methods, What would you suggest?
A simple few lines of code would be very helpful. (I failed to mention that
I am developing code using OfficeXP but have users that have Office 2007.)

Thanks for your help.

"macropod" wrote:

Hi Bill,

There are various way of approaching this, depending on how your Word document is set up.

For example, if the range you want to import is bookmarked in Word, and you want to link to it so that the Excel workbook will
update to reflect the bookmark's contents, you could use a formula in Excel like:
=Word.Document|'C:\My Documents\Wordfile.doc'!'!BkMrk'
where 'C:\My Documents\Wordfile.doc' is the document's path and name, and 'BkMrk' is the name of the bookmark.
No macro required.

Conversely, if the range you want to copy is for a one-off update, and there's always a specific start & end string (you've already
indicated '' as the start string), then a different kind of search in Word might be better. This is especially so since line counts
in Word often depend on the current printer driver.

--
Cheers
macropod
[MVP - Microsoft Word]


"Bonsai Bill" wrote in message ...
Thanks Barb, that did the trick! I knew the problem but didn't know the
solution.

I know I may be asking too much but is there a workaround for the coding?
The reason is that my users know zip about macros. Maybe the easiest thing to
do is to include it in the instruction writeup.

"Barb Reinhardt" wrote:

Excel doesn't know you're working with Word objects. You need to tell it.

You'll need to set a reference to the Word objects in the VBE using Tools -
References.

Select "Microsoft Word 11.0 Object Library" (for 2003)

This code should get you started

Sub Test()
Dim WordApp As Word.Application
Dim oDoc As Word.Document

Set WordApp = GetObject(, "Word.Application")
Debug.Print WordApp

If WordApp Is Nothing Then Exit Sub

For Each oDoc In WordApp.Documents
Debug.Print oDoc.Name
Next oDoc

Set WordApp = Nothing

End Sub

--
HTH,
Barb Reinhardt



"Bonsai Bill" wrote:

I need to import a number of text lines from Word file into Excel. Using what
I found from postings here on importing tables and the Word macro recorder I
have the following partial code:
FName = Application _
.GetOpenFilename("Word Files (*.doc), *.doc")
Set oWord = GetObject(FName)
With oWord.Find
.Text = "Seq."
.Replacement.Text = ""
...
End With 'oWord
Selection.Find.Execute
Selection.MoveRight Unit:=wdCharacter, Count:=66, Extend:=wdExtend
Selection.MoveDown Unit:=wdLine, Count:=100, Extend:=wdExtend
Selection.Copy
Cells("A1").Activate
ActiveSheet.Paste

However, Excel VBA doesn't know wdCharacter or wdLine. It doesn't know
wdForward if I try a Move function. The number of lines of text to import is
constant.

Any workarounds would be greatly appreciated!

Importing Word text into Excel
 

Hi Bill,

You could replace:

With oWord.Find
.Text = "Seq."
.Replacement.Text = ""
....
End With 'oWord
Selection.Find.Execute
Selection.MoveRight Unit:=wdCharacter, Count:=66, Extend:=wdExtend
Selection.MoveDown Unit:=wdLine, Count:=100, Extend:=wdExtend
Selection.Copy
Cells("A1").Activate
ActiveSheet.Paste

with:

With oWord.Content
With .Find
.ClearFormatting
.Text = "(Seq.)*(tab80tab540)"
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindStop
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchAllWordForms = False
.MatchSoundsLike = False
.MatchWildcards = True
.Execute
End With
.Copy
End With
Cells("A1").Paste

This makes the code impervious to line count changes and should run faster too.

--
Cheers
macropod
[MVP - Microsoft Word]


"Bonsai Bill" wrote in message ...
Hi macropod,

My application doesn't involve links and the desired text is not bookmarked.
However the last line always has the string of "tab80tab540" and could be
used to anchor the last line.

You mentioned that a different search method would be safer than using Find.
I checked Help but didn't find any promising methods, What would you suggest?
A simple few lines of code would be very helpful. (I failed to mention that
I am developing code using OfficeXP but have users that have Office 2007.)

Thanks for your help.

"macropod" wrote:

Hi Bill,

There are various way of approaching this, depending on how your Word document is set up.

For example, if the range you want to import is bookmarked in Word, and you want to link to it so that the Excel workbook will
update to reflect the bookmark's contents, you could use a formula in Excel like:
=Word.Document|'C:\My Documents\Wordfile.doc'!'!BkMrk'
where 'C:\My Documents\Wordfile.doc' is the document's path and name, and 'BkMrk' is the name of the bookmark.
No macro required.

Conversely, if the range you want to copy is for a one-off update, and there's always a specific start & end string (you've
already
indicated '' as the start string), then a different kind of search in Word might be better. This is especially so since line
counts
in Word often depend on the current printer driver.

--
Cheers
macropod
[MVP - Microsoft Word]


"Bonsai Bill" wrote in message ...
Thanks Barb, that did the trick! I knew the problem but didn't know the
solution.

I know I may be asking too much but is there a workaround for the coding?
The reason is that my users know zip about macros. Maybe the easiest thing to
do is to include it in the instruction writeup.

"Barb Reinhardt" wrote:

Excel doesn't know you're working with Word objects. You need to tell it.

You'll need to set a reference to the Word objects in the VBE using Tools -
References.

Select "Microsoft Word 11.0 Object Library" (for 2003)

This code should get you started

Sub Test()
Dim WordApp As Word.Application
Dim oDoc As Word.Document

Set WordApp = GetObject(, "Word.Application")
Debug.Print WordApp

If WordApp Is Nothing Then Exit Sub

For Each oDoc In WordApp.Documents
Debug.Print oDoc.Name
Next oDoc

Set WordApp = Nothing

End Sub

--
HTH,
Barb Reinhardt



"Bonsai Bill" wrote:

I need to import a number of text lines from Word file into Excel. Using what
I found from postings here on importing tables and the Word macro recorder I
have the following partial code:
FName = Application _
.GetOpenFilename("Word Files (*.doc), *.doc")
Set oWord = GetObject(FName)
With oWord.Find
.Text = "Seq."
.Replacement.Text = ""
...
End With 'oWord
Selection.Find.Execute
Selection.MoveRight Unit:=wdCharacter, Count:=66, Extend:=wdExtend
Selection.MoveDown Unit:=wdLine, Count:=100, Extend:=wdExtend
Selection.Copy
Cells("A1").Activate
ActiveSheet.Paste

However, Excel VBA doesn't know wdCharacter or wdLine. It doesn't know
wdForward if I try a Move function. The number of lines of text to import is
constant.

Any workarounds would be greatly appreciated!

Importing Word text into Excel
 

Hello macrpod,

Your proposed code worked! However, for some reason I had to replace
Cells("A1").Paste
with
Range("A1").Activate
ActiveSheet.Paste

to eliminate mismatch error.

One great thing about your code was that I can avoid having others load Word
References because I removed ".Wrap = wdFindStop" I close the Word file right
after doing the copy and pasting so I assume there should be no problem with
using default Wrap.

Thanks for your help! I really appreciate folks like you and Barb helping
the less knowledgeable users like me. I always learn valuable new tricks and
tips whenever I come to this discussion group!

"macropod" wrote:

Hi Bill,

You could replace:

With oWord.Find
.Text = "Seq."
.Replacement.Text = ""
....
End With 'oWord
Selection.Find.Execute
Selection.MoveRight Unit:=wdCharacter, Count:=66, Extend:=wdExtend
Selection.MoveDown Unit:=wdLine, Count:=100, Extend:=wdExtend
Selection.Copy
Cells("A1").Activate
ActiveSheet.Paste

with:

With oWord.Content
With .Find
.ClearFormatting
.Text = "(Seq.)*(tab80tab540)"
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindStop
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchAllWordForms = False
.MatchSoundsLike = False
.MatchWildcards = True
.Execute
End With
.Copy
End With
Cells("A1").Paste

This makes the code impervious to line count changes and should run faster too.

--
Cheers
macropod
[MVP - Microsoft Word]


"Bonsai Bill" wrote in message ...
Hi macropod,

My application doesn't involve links and the desired text is not bookmarked.
However the last line always has the string of "tab80tab540" and could be
used to anchor the last line.

You mentioned that a different search method would be safer than using Find.
I checked Help but didn't find any promising methods, What would you suggest?
A simple few lines of code would be very helpful. (I failed to mention that
I am developing code using OfficeXP but have users that have Office 2007.)

Thanks for your help.

"macropod" wrote:

Hi Bill,

There are various way of approaching this, depending on how your Word document is set up.

For example, if the range you want to import is bookmarked in Word, and you want to link to it so that the Excel workbook will
update to reflect the bookmark's contents, you could use a formula in Excel like:
=Word.Document|'C:\My Documents\Wordfile.doc'!'!BkMrk'
where 'C:\My Documents\Wordfile.doc' is the document's path and name, and 'BkMrk' is the name of the bookmark.
No macro required.

Conversely, if the range you want to copy is for a one-off update, and there's always a specific start & end string (you've
already
indicated '' as the start string), then a different kind of search in Word might be better. This is especially so since line
counts
in Word often depend on the current printer driver.

--
Cheers
macropod
[MVP - Microsoft Word]


"Bonsai Bill" wrote in message ...
Thanks Barb, that did the trick! I knew the problem but didn't know the
solution.

I know I may be asking too much but is there a workaround for the coding?
The reason is that my users know zip about macros. Maybe the easiest thing to
do is to include it in the instruction writeup.

"Barb Reinhardt" wrote:

Excel doesn't know you're working with Word objects. You need to tell it.

You'll need to set a reference to the Word objects in the VBE using Tools -
References.

Select "Microsoft Word 11.0 Object Library" (for 2003)

This code should get you started

Sub Test()
Dim WordApp As Word.Application
Dim oDoc As Word.Document

Set WordApp = GetObject(, "Word.Application")
Debug.Print WordApp

If WordApp Is Nothing Then Exit Sub

For Each oDoc In WordApp.Documents
Debug.Print oDoc.Name
Next oDoc

Set WordApp = Nothing

End Sub

--
HTH,
Barb Reinhardt



"Bonsai Bill" wrote:

I need to import a number of text lines from Word file into Excel. Using what
I found from postings here on importing tables and the Word macro recorder I
have the following partial code:
FName = Application _
.GetOpenFilename("Word Files (*.doc), *.doc")
Set oWord = GetObject(FName)
With oWord.Find
.Text = "Seq."
.Replacement.Text = ""
...
End With 'oWord
Selection.Find.Execute
Selection.MoveRight Unit:=wdCharacter, Count:=66, Extend:=wdExtend
Selection.MoveDown Unit:=wdLine, Count:=100, Extend:=wdExtend
Selection.Copy
Cells("A1").Activate
ActiveSheet.Paste

However, Excel VBA doesn't know wdCharacter or wdLine. It doesn't know
wdForward if I try a Move function. The number of lines of text to import is
constant.

Any workarounds would be greatly appreciated!

Importing Word text into Excel
 

Hi Bill,

Instead of:

Range("A1").Activate
ActiveSheet.Paste

you should be able to use:

ActiveSheet.Range("A1").Paste

--
Cheers
macropod
[MVP - Microsoft Word]


"Bonsai Bill" wrote in message ...
Hello macrpod,

Your proposed code worked! However, for some reason I had to replace
Cells("A1").Paste
with
Range("A1").Activate
ActiveSheet.Paste

to eliminate mismatch error.

One great thing about your code was that I can avoid having others load Word
References because I removed ".Wrap = wdFindStop" I close the Word file right
after doing the copy and pasting so I assume there should be no problem with
using default Wrap.

Thanks for your help! I really appreciate folks like you and Barb helping
the less knowledgeable users like me. I always learn valuable new tricks and
tips whenever I come to this discussion group!

"macropod" wrote:

Hi Bill,

You could replace:

With oWord.Find
.Text = "Seq."
.Replacement.Text = ""
....
End With 'oWord
Selection.Find.Execute
Selection.MoveRight Unit:=wdCharacter, Count:=66, Extend:=wdExtend
Selection.MoveDown Unit:=wdLine, Count:=100, Extend:=wdExtend
Selection.Copy
Cells("A1").Activate
ActiveSheet.Paste

with:

With oWord.Content
With .Find
.ClearFormatting
.Text = "(Seq.)*(tab80tab540)"
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindStop
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchAllWordForms = False
.MatchSoundsLike = False
.MatchWildcards = True
.Execute
End With
.Copy
End With
Cells("A1").Paste

This makes the code impervious to line count changes and should run faster too.

--
Cheers
macropod
[MVP - Microsoft Word]


"Bonsai Bill" wrote in message ...
Hi macropod,

My application doesn't involve links and the desired text is not bookmarked.
However the last line always has the string of "tab80tab540" and could be
used to anchor the last line.

You mentioned that a different search method would be safer than using Find.
I checked Help but didn't find any promising methods, What would you suggest?
A simple few lines of code would be very helpful. (I failed to mention that
I am developing code using OfficeXP but have users that have Office 2007.)

Thanks for your help.

"macropod" wrote:

Hi Bill,

There are various way of approaching this, depending on how your Word document is set up.

For example, if the range you want to import is bookmarked in Word, and you want to link to it so that the Excel workbook will
update to reflect the bookmark's contents, you could use a formula in Excel like:
=Word.Document|'C:\My Documents\Wordfile.doc'!'!BkMrk'
where 'C:\My Documents\Wordfile.doc' is the document's path and name, and 'BkMrk' is the name of the bookmark.
No macro required.

Conversely, if the range you want to copy is for a one-off update, and there's always a specific start & end string (you've
already
indicated '' as the start string), then a different kind of search in Word might be better. This is especially so since line
counts
in Word often depend on the current printer driver.

--
Cheers
macropod
[MVP - Microsoft Word]


"Bonsai Bill" wrote in message
...
Thanks Barb, that did the trick! I knew the problem but didn't know the
solution.

I know I may be asking too much but is there a workaround for the coding?
The reason is that my users know zip about macros. Maybe the easiest thing to
do is to include it in the instruction writeup.

"Barb Reinhardt" wrote:

Excel doesn't know you're working with Word objects. You need to tell it.

You'll need to set a reference to the Word objects in the VBE using Tools -
References.

Select "Microsoft Word 11.0 Object Library" (for 2003)

This code should get you started

Sub Test()
Dim WordApp As Word.Application
Dim oDoc As Word.Document

Set WordApp = GetObject(, "Word.Application")
Debug.Print WordApp

If WordApp Is Nothing Then Exit Sub

For Each oDoc In WordApp.Documents
Debug.Print oDoc.Name
Next oDoc

Set WordApp = Nothing

End Sub

--
HTH,
Barb Reinhardt



"Bonsai Bill" wrote:

I need to import a number of text lines from Word file into Excel. Using what
I found from postings here on importing tables and the Word macro recorder I
have the following partial code:
FName = Application _
.GetOpenFilename("Word Files (*.doc), *.doc")
Set oWord = GetObject(FName)
With oWord.Find
.Text = "Seq."
.Replacement.Text = ""
...
End With 'oWord
Selection.Find.Execute
Selection.MoveRight Unit:=wdCharacter, Count:=66, Extend:=wdExtend
Selection.MoveDown Unit:=wdLine, Count:=100, Extend:=wdExtend
Selection.Copy
Cells("A1").Activate
ActiveSheet.Paste

However, Excel VBA doesn't know wdCharacter or wdLine. It doesn't know
wdForward if I try a Move function. The number of lines of text to import is
constant.

Any workarounds would be greatly appreciated!

Importing Word text into Excel
 

Hi macropod,

I am sorry but I am back once more. The code almost works:
With oWord.Content
With .Find
.ClearFormatting
.Text = "(Seq.)*(^t80^t540)"
.Replacement.Text = ""
.Forward = True
.Wrap = 0
...
.Execute
End With 'Find
.Copy
End With ' Content

The problem is that it selects all text to the (^t80^t540) in the last line.
How can I extend the copy to the end of that line? I tried several functions
but they all failed. The line may contain numbers or capital letters.

Thanks again for your help!

"macropod" wrote:

Hi Bill,

Instead of:

Range("A1").Activate
ActiveSheet.Paste

you should be able to use:

ActiveSheet.Range("A1").Paste

--
Cheers
macropod
[MVP - Microsoft Word]


"Bonsai Bill" wrote in message ...
Hello macrpod,

Your proposed code worked! However, for some reason I had to replace
Cells("A1").Paste
with
Range("A1").Activate
ActiveSheet.Paste

to eliminate mismatch error.

One great thing about your code was that I can avoid having others load Word
References because I removed ".Wrap = wdFindStop" I close the Word file right
after doing the copy and pasting so I assume there should be no problem with
using default Wrap.

Thanks for your help! I really appreciate folks like you and Barb helping
the less knowledgeable users like me. I always learn valuable new tricks and
tips whenever I come to this discussion group!

"macropod" wrote:

Hi Bill,

You could replace:

With oWord.Find
.Text = "Seq."
.Replacement.Text = ""
....
End With 'oWord
Selection.Find.Execute
Selection.MoveRight Unit:=wdCharacter, Count:=66, Extend:=wdExtend
Selection.MoveDown Unit:=wdLine, Count:=100, Extend:=wdExtend
Selection.Copy
Cells("A1").Activate
ActiveSheet.Paste

with:

With oWord.Content
With .Find
.ClearFormatting
.Text = "(Seq.)*(tab80tab540)"
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindStop
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchAllWordForms = False
.MatchSoundsLike = False
.MatchWildcards = True
.Execute
End With
.Copy
End With
Cells("A1").Paste

This makes the code impervious to line count changes and should run faster too.

--
Cheers
macropod
[MVP - Microsoft Word]


"Bonsai Bill" wrote in message ...
Hi macropod,

My application doesn't involve links and the desired text is not bookmarked.
However the last line always has the string of "tab80tab540" and could be
used to anchor the last line.

You mentioned that a different search method would be safer than using Find.
I checked Help but didn't find any promising methods, What would you suggest?
A simple few lines of code would be very helpful. (I failed to mention that
I am developing code using OfficeXP but have users that have Office 2007.)

Thanks for your help.

"macropod" wrote:

Hi Bill,

There are various way of approaching this, depending on how your Word document is set up.

For example, if the range you want to import is bookmarked in Word, and you want to link to it so that the Excel workbook will
update to reflect the bookmark's contents, you could use a formula in Excel like:
=Word.Document|'C:\My Documents\Wordfile.doc'!'!BkMrk'
where 'C:\My Documents\Wordfile.doc' is the document's path and name, and 'BkMrk' is the name of the bookmark.
No macro required.

Conversely, if the range you want to copy is for a one-off update, and there's always a specific start & end string (you've
already
indicated '' as the start string), then a different kind of search in Word might be better. This is especially so since line
counts
in Word often depend on the current printer driver.

--
Cheers
macropod
[MVP - Microsoft Word]


"Bonsai Bill" wrote in message
...
Thanks Barb, that did the trick! I knew the problem but didn't know the
solution.

I know I may be asking too much but is there a workaround for the coding?
The reason is that my users know zip about macros. Maybe the easiest thing to
do is to include it in the instruction writeup.

"Barb Reinhardt" wrote:

Excel doesn't know you're working with Word objects. You need to tell it.

You'll need to set a reference to the Word objects in the VBE using Tools -
References.

Select "Microsoft Word 11.0 Object Library" (for 2003)

This code should get you started

Sub Test()
Dim WordApp As Word.Application
Dim oDoc As Word.Document

Set WordApp = GetObject(, "Word.Application")
Debug.Print WordApp

If WordApp Is Nothing Then Exit Sub

For Each oDoc In WordApp.Documents
Debug.Print oDoc.Name
Next oDoc

Set WordApp = Nothing

End Sub

--
HTH,
Barb Reinhardt



"Bonsai Bill" wrote:

I need to import a number of text lines from Word file into Excel. Using what
I found from postings here on importing tables and the Word macro recorder I
have the following partial code:
FName = Application _
.GetOpenFilename("Word Files (*.doc), *.doc")
Set oWord = GetObject(FName)
With oWord.Find
.Text = "Seq."
.Replacement.Text = ""
...
End With 'oWord
Selection.Find.Execute
Selection.MoveRight Unit:=wdCharacter, Count:=66, Extend:=wdExtend
Selection.MoveDown Unit:=wdLine, Count:=100, Extend:=wdExtend
Selection.Copy
Cells("A1").Activate
ActiveSheet.Paste

However, Excel VBA doesn't know wdCharacter or wdLine. It doesn't know
wdForward if I try a Move function. The number of lines of text to import is
constant.

Any workarounds would be greatly appreciated!

Importing Word text into Excel
 

Hi Bill,

Are you saying there's extra alpha-numeric characters after the ^t80^t540, plus a paragraph mark/line-feed? Or just a paragraph
mark/line-feed?

--
Cheers
macropod
[MVP - Microsoft Word]


"Bonsai Bill" wrote in message ...
Hi macropod,

I am sorry but I am back once more. The code almost works:
With oWord.Content
With .Find
.ClearFormatting
.Text = "(Seq.)*(^t80^t540)"
.Replacement.Text = ""
.Forward = True
.Wrap = 0
...
.Execute
End With 'Find
.Copy
End With ' Content

The problem is that it selects all text to the (^t80^t540) in the last line.
How can I extend the copy to the end of that line? I tried several functions
but they all failed. The line may contain numbers or capital letters.

Thanks again for your help!

"macropod" wrote:

Hi Bill,

Instead of:

Range("A1").Activate
ActiveSheet.Paste

you should be able to use:

ActiveSheet.Range("A1").Paste

--
Cheers
macropod
[MVP - Microsoft Word]


"Bonsai Bill" wrote in message ...
Hello macrpod,

Your proposed code worked! However, for some reason I had to replace
Cells("A1").Paste
with
Range("A1").Activate
ActiveSheet.Paste

to eliminate mismatch error.

One great thing about your code was that I can avoid having others load Word
References because I removed ".Wrap = wdFindStop" I close the Word file right
after doing the copy and pasting so I assume there should be no problem with
using default Wrap.

Thanks for your help! I really appreciate folks like you and Barb helping
the less knowledgeable users like me. I always learn valuable new tricks and
tips whenever I come to this discussion group!

"macropod" wrote:

Hi Bill,

You could replace:

With oWord.Find
.Text = "Seq."
.Replacement.Text = ""
....
End With 'oWord
Selection.Find.Execute
Selection.MoveRight Unit:=wdCharacter, Count:=66, Extend:=wdExtend
Selection.MoveDown Unit:=wdLine, Count:=100, Extend:=wdExtend
Selection.Copy
Cells("A1").Activate
ActiveSheet.Paste

with:

With oWord.Content
With .Find
.ClearFormatting
.Text = "(Seq.)*(tab80tab540)"
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindStop
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchAllWordForms = False
.MatchSoundsLike = False
.MatchWildcards = True
.Execute
End With
.Copy
End With
Cells("A1").Paste

This makes the code impervious to line count changes and should run faster too.

--
Cheers
macropod
[MVP - Microsoft Word]


"Bonsai Bill" wrote in message
...
Hi macropod,

My application doesn't involve links and the desired text is not bookmarked.
However the last line always has the string of "tab80tab540" and could be
used to anchor the last line.

You mentioned that a different search method would be safer than using Find.
I checked Help but didn't find any promising methods, What would you suggest?
A simple few lines of code would be very helpful. (I failed to mention that
I am developing code using OfficeXP but have users that have Office 2007.)

Thanks for your help.

"macropod" wrote:

Hi Bill,

There are various way of approaching this, depending on how your Word document is set up.

For example, if the range you want to import is bookmarked in Word, and you want to link to it so that the Excel workbook
will
update to reflect the bookmark's contents, you could use a formula in Excel like:
=Word.Document|'C:\My Documents\Wordfile.doc'!'!BkMrk'
where 'C:\My Documents\Wordfile.doc' is the document's path and name, and 'BkMrk' is the name of the bookmark.
No macro required.

Conversely, if the range you want to copy is for a one-off update, and there's always a specific start & end string (you've
already
indicated '' as the start string), then a different kind of search in Word might be better. This is especially so since
line
counts
in Word often depend on the current printer driver.

--
Cheers
macropod
[MVP - Microsoft Word]


"Bonsai Bill" wrote in message
...
Thanks Barb, that did the trick! I knew the problem but didn't know the
solution.

I know I may be asking too much but is there a workaround for the coding?
The reason is that my users know zip about macros. Maybe the easiest thing to
do is to include it in the instruction writeup.

"Barb Reinhardt" wrote:

Excel doesn't know you're working with Word objects. You need to tell it.

You'll need to set a reference to the Word objects in the VBE using Tools -
References.

Select "Microsoft Word 11.0 Object Library" (for 2003)

This code should get you started

Sub Test()
Dim WordApp As Word.Application
Dim oDoc As Word.Document

Set WordApp = GetObject(, "Word.Application")
Debug.Print WordApp

If WordApp Is Nothing Then Exit Sub

For Each oDoc In WordApp.Documents
Debug.Print oDoc.Name
Next oDoc

Set WordApp = Nothing

End Sub

--
HTH,
Barb Reinhardt



"Bonsai Bill" wrote:

I need to import a number of text lines from Word file into Excel. Using what
I found from postings here on importing tables and the Word macro recorder I
have the following partial code:
FName = Application _
.GetOpenFilename("Word Files (*.doc), *.doc")
Set oWord = GetObject(FName)
With oWord.Find
.Text = "Seq."
.Replacement.Text = ""
...
End With 'oWord
Selection.Find.Execute
Selection.MoveRight Unit:=wdCharacter, Count:=66, Extend:=wdExtend
Selection.MoveDown Unit:=wdLine, Count:=100, Extend:=wdExtend
Selection.Copy
Cells("A1").Activate
ActiveSheet.Paste

However, Excel VBA doesn't know wdCharacter or wdLine. It doesn't know
wdForward if I try a Move function. The number of lines of text to import is
constant.

Any workarounds would be greatly appreciated!

Importing Word text into Excel
 

Hi macropod,

There are additional alpha-numerics in the line which concludes with a
paragraph mark. The paragraphs (actually lines because there is a paragraph
mark at end of each line) has Widow/Orphan control, Keep Lines together and
Keep with next.

This last line formatting looks like previous line in all respects.

BTW ActiveSheet.Range("A1").Paste won't work. I am not sure why because it
seems like it should.

Thanks again for all of your help! I wouldn't be "almost there" without your
help.
Bonsai Bill

"macropod" wrote:

Hi Bill,

Are you saying there's extra alpha-numeric characters after the ^t80^t540, plus a paragraph mark/line-feed? Or just a paragraph
mark/line-feed?

--
Cheers
macropod
[MVP - Microsoft Word]


"Bonsai Bill" wrote in message ...
Hi macropod,

I am sorry but I am back once more. The code almost works:
With oWord.Content
With .Find
.ClearFormatting
.Text = "(Seq.)*(^t80^t540)"
.Replacement.Text = ""
.Forward = True
.Wrap = 0
...
.Execute
End With 'Find
.Copy
End With ' Content

The problem is that it selects all text to the (^t80^t540) in the last line.
How can I extend the copy to the end of that line? I tried several functions
but they all failed. The line may contain numbers or capital letters.

Thanks again for your help!

"macropod" wrote:

Hi Bill,

Instead of:

Range("A1").Activate
ActiveSheet.Paste

you should be able to use:

ActiveSheet.Range("A1").Paste

--
Cheers
macropod
[MVP - Microsoft Word]


"Bonsai Bill" wrote in message ...
Hello macrpod,

Your proposed code worked! However, for some reason I had to replace
Cells("A1").Paste
with
Range("A1").Activate
ActiveSheet.Paste

to eliminate mismatch error.

One great thing about your code was that I can avoid having others load Word
References because I removed ".Wrap = wdFindStop" I close the Word file right
after doing the copy and pasting so I assume there should be no problem with
using default Wrap.

Thanks for your help! I really appreciate folks like you and Barb helping
the less knowledgeable users like me. I always learn valuable new tricks and
tips whenever I come to this discussion group!

"macropod" wrote:

Hi Bill,

You could replace:

With oWord.Find
.Text = "Seq."
.Replacement.Text = ""
....
End With 'oWord
Selection.Find.Execute
Selection.MoveRight Unit:=wdCharacter, Count:=66, Extend:=wdExtend
Selection.MoveDown Unit:=wdLine, Count:=100, Extend:=wdExtend
Selection.Copy
Cells("A1").Activate
ActiveSheet.Paste

with:

With oWord.Content
With .Find
.ClearFormatting
.Text = "(Seq.)*(tab80tab540)"
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindStop
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchAllWordForms = False
.MatchSoundsLike = False
.MatchWildcards = True
.Execute
End With
.Copy
End With
Cells("A1").Paste

This makes the code impervious to line count changes and should run faster too.

--
Cheers
macropod
[MVP - Microsoft Word]


"Bonsai Bill" wrote in message
...
Hi macropod,

My application doesn't involve links and the desired text is not bookmarked.
However the last line always has the string of "tab80tab540" and could be
used to anchor the last line.

You mentioned that a different search method would be safer than using Find.
I checked Help but didn't find any promising methods, What would you suggest?
A simple few lines of code would be very helpful. (I failed to mention that
I am developing code using OfficeXP but have users that have Office 2007.)

Thanks for your help.

"macropod" wrote:

Hi Bill,

There are various way of approaching this, depending on how your Word document is set up.

For example, if the range you want to import is bookmarked in Word, and you want to link to it so that the Excel workbook
will
update to reflect the bookmark's contents, you could use a formula in Excel like:
=Word.Document|'C:\My Documents\Wordfile.doc'!'!BkMrk'
where 'C:\My Documents\Wordfile.doc' is the document's path and name, and 'BkMrk' is the name of the bookmark.
No macro required.

Conversely, if the range you want to copy is for a one-off update, and there's always a specific start & end string (you've
already
indicated '' as the start string), then a different kind of search in Word might be better. This is especially so since
line
counts
in Word often depend on the current printer driver.

--
Cheers
macropod
[MVP - Microsoft Word]


"Bonsai Bill" wrote in message
...
Thanks Barb, that did the trick! I knew the problem but didn't know the
solution.

I know I may be asking too much but is there a workaround for the coding?
The reason is that my users know zip about macros. Maybe the easiest thing to
do is to include it in the instruction writeup.

"Barb Reinhardt" wrote:

Excel doesn't know you're working with Word objects. You need to tell it.

You'll need to set a reference to the Word objects in the VBE using Tools -
References.

Select "Microsoft Word 11.0 Object Library" (for 2003)

This code should get you started

Sub Test()
Dim WordApp As Word.Application
Dim oDoc As Word.Document

Set WordApp = GetObject(, "Word.Application")
Debug.Print WordApp

If WordApp Is Nothing Then Exit Sub

For Each oDoc In WordApp.Documents
Debug.Print oDoc.Name
Next oDoc

Set WordApp = Nothing

End Sub

--
HTH,
Barb Reinhardt



"Bonsai Bill" wrote:

I need to import a number of text lines from Word file into Excel. Using what
I found from postings here on importing tables and the Word macro recorder I
have the following partial code:
FName = Application _
.GetOpenFilename("Word Files (*.doc), *.doc")
Set oWord = GetObject(FName)
With oWord.Find
.Text = "Seq."
.Replacement.Text = ""
...
End With 'oWord
Selection.Find.Execute
Selection.MoveRight Unit:=wdCharacter, Count:=66, Extend:=wdExtend
Selection.MoveDown Unit:=wdLine, Count:=100, Extend:=wdExtend
Selection.Copy
Cells("A1").Activate
ActiveSheet.Paste

However, Excel VBA doesn't know wdCharacter or wdLine. It doesn't know
wdForward if I try a Move function. The number of lines of text to import is
constant.

Any workarounds would be greatly appreciated!

Importing Word text into Excel
 

Perhaps something like:
With oWord.Content.Find
.ClearFormatting
.Text = "Seq.*^t80^t540"
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindStop
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchAllWordForms = False
.MatchSoundsLike = False
.MatchWildcards = True
.Execute
If .Found = True Then
.Parent.Expand Unit:=wdParagraph
.Parent.Copy
End If
End With

--
Cheers
macropod
[MVP - Microsoft Word]


"Bonsai Bill" wrote in message ...
Hi macropod,

There are additional alpha-numerics in the line which concludes with a
paragraph mark. The paragraphs (actually lines because there is a paragraph
mark at end of each line) has Widow/Orphan control, Keep Lines together and
Keep with next.

This last line formatting looks like previous line in all respects.

BTW ActiveSheet.Range("A1").Paste won't work. I am not sure why because it
seems like it should.

Thanks again for all of your help! I wouldn't be "almost there" without your
help.
Bonsai Bill

"macropod" wrote:

Hi Bill,

Are you saying there's extra alpha-numeric characters after the ^t80^t540, plus a paragraph mark/line-feed? Or just a paragraph
mark/line-feed?

--
Cheers
macropod
[MVP - Microsoft Word]


"Bonsai Bill" wrote in message ...
Hi macropod,

I am sorry but I am back once more. The code almost works:
With oWord.Content
With .Find
.ClearFormatting
.Text = "(Seq.)*(^t80^t540)"
.Replacement.Text = ""
.Forward = True
.Wrap = 0
...
.Execute
End With 'Find
.Copy
End With ' Content

The problem is that it selects all text to the (^t80^t540) in the last line.
How can I extend the copy to the end of that line? I tried several functions
but they all failed. The line may contain numbers or capital letters.

Thanks again for your help!

"macropod" wrote:

Hi Bill,

Instead of:

Range("A1").Activate
ActiveSheet.Paste

you should be able to use:

ActiveSheet.Range("A1").Paste

--
Cheers
macropod
[MVP - Microsoft Word]


"Bonsai Bill" wrote in message
...
Hello macrpod,

Your proposed code worked! However, for some reason I had to replace
Cells("A1").Paste
with
Range("A1").Activate
ActiveSheet.Paste

to eliminate mismatch error.

One great thing about your code was that I can avoid having others load Word
References because I removed ".Wrap = wdFindStop" I close the Word file right
after doing the copy and pasting so I assume there should be no problem with
using default Wrap.

Thanks for your help! I really appreciate folks like you and Barb helping
the less knowledgeable users like me. I always learn valuable new tricks and
tips whenever I come to this discussion group!

"macropod" wrote:

Hi Bill,

You could replace:

With oWord.Find
.Text = "Seq."
.Replacement.Text = ""
....
End With 'oWord
Selection.Find.Execute
Selection.MoveRight Unit:=wdCharacter, Count:=66, Extend:=wdExtend
Selection.MoveDown Unit:=wdLine, Count:=100, Extend:=wdExtend
Selection.Copy
Cells("A1").Activate
ActiveSheet.Paste

with:

With oWord.Content
With .Find
.ClearFormatting
.Text = "(Seq.)*(tab80tab540)"
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindStop
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchAllWordForms = False
.MatchSoundsLike = False
.MatchWildcards = True
.Execute
End With
.Copy
End With
Cells("A1").Paste

This makes the code impervious to line count changes and should run faster too.

--
Cheers
macropod
[MVP - Microsoft Word]


"Bonsai Bill" wrote in message
...
Hi macropod,

My application doesn't involve links and the desired text is not bookmarked.
However the last line always has the string of "tab80tab540" and could be
used to anchor the last line.

You mentioned that a different search method would be safer than using Find.
I checked Help but didn't find any promising methods, What would you suggest?
A simple few lines of code would be very helpful. (I failed to mention that
I am developing code using OfficeXP but have users that have Office 2007.)

Thanks for your help.

"macropod" wrote:

Hi Bill,

There are various way of approaching this, depending on how your Word document is set up.

For example, if the range you want to import is bookmarked in Word, and you want to link to it so that the Excel
workbook
will
update to reflect the bookmark's contents, you could use a formula in Excel like:
=Word.Document|'C:\My Documents\Wordfile.doc'!'!BkMrk'
where 'C:\My Documents\Wordfile.doc' is the document's path and name, and 'BkMrk' is the name of the bookmark.
No macro required.

Conversely, if the range you want to copy is for a one-off update, and there's always a specific start & end string
(you've
already
indicated '' as the start string), then a different kind of search in Word might be better. This is especially so since
line
counts
in Word often depend on the current printer driver.

--
Cheers
macropod
[MVP - Microsoft Word]


"Bonsai Bill" wrote in message
...
Thanks Barb, that did the trick! I knew the problem but didn't know the
solution.

I know I may be asking too much but is there a workaround for the coding?
The reason is that my users know zip about macros. Maybe the easiest thing to
do is to include it in the instruction writeup.

"Barb Reinhardt" wrote:

Excel doesn't know you're working with Word objects. You need to tell it.

You'll need to set a reference to the Word objects in the VBE using Tools -
References.

Select "Microsoft Word 11.0 Object Library" (for 2003)

This code should get you started

Sub Test()
Dim WordApp As Word.Application
Dim oDoc As Word.Document

Set WordApp = GetObject(, "Word.Application")
Debug.Print WordApp

If WordApp Is Nothing Then Exit Sub

For Each oDoc In WordApp.Documents
Debug.Print oDoc.Name
Next oDoc

Set WordApp = Nothing

End Sub

--
HTH,
Barb Reinhardt



"Bonsai Bill" wrote:

I need to import a number of text lines from Word file into Excel. Using what
I found from postings here on importing tables and the Word macro recorder I
have the following partial code:
FName = Application _
.GetOpenFilename("Word Files (*.doc), *.doc")
Set oWord = GetObject(FName)
With oWord.Find
.Text = "Seq."
.Replacement.Text = ""
...
End With 'oWord
Selection.Find.Execute
Selection.MoveRight Unit:=wdCharacter, Count:=66, Extend:=wdExtend
Selection.MoveDown Unit:=wdLine, Count:=100, Extend:=wdExtend
Selection.Copy
Cells("A1").Activate
ActiveSheet.Paste

However, Excel VBA doesn't know wdCharacter or wdLine. It doesn't know
wdForward if I try a Move function. The number of lines of text to import is
constant.

Any workarounds would be greatly appreciated!

Importing Word text into Excel
 

Hi macropod,

That revised code worked like a charm!! I thank you very much for your help.

Bonsai Bill

"macropod" wrote:

Perhaps something like:
With oWord.Content.Find
.ClearFormatting
.Text = "Seq.*^t80^t540"
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindStop
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchAllWordForms = False
.MatchSoundsLike = False
.MatchWildcards = True
.Execute
If .Found = True Then
.Parent.Expand Unit:=wdParagraph
.Parent.Copy
End If
End With

--
Cheers
macropod
[MVP - Microsoft Word]


"Bonsai Bill" wrote in message ...
Hi macropod,

There are additional alpha-numerics in the line which concludes with a
paragraph mark. The paragraphs (actually lines because there is a paragraph
mark at end of each line) has Widow/Orphan control, Keep Lines together and
Keep with next.

This last line formatting looks like previous line in all respects.

BTW ActiveSheet.Range("A1").Paste won't work. I am not sure why because it
seems like it should.

Thanks again for all of your help! I wouldn't be "almost there" without your
help.
Bonsai Bill

"macropod" wrote:

Hi Bill,

Are you saying there's extra alpha-numeric characters after the ^t80^t540, plus a paragraph mark/line-feed? Or just a paragraph
mark/line-feed?

--
Cheers
macropod
[MVP - Microsoft Word]


"Bonsai Bill" wrote in message ...
Hi macropod,

I am sorry but I am back once more. The code almost works:
With oWord.Content
With .Find
.ClearFormatting
.Text = "(Seq.)*(^t80^t540)"
.Replacement.Text = ""
.Forward = True
.Wrap = 0
...
.Execute
End With 'Find
.Copy
End With ' Content

The problem is that it selects all text to the (^t80^t540) in the last line.
How can I extend the copy to the end of that line? I tried several functions
but they all failed. The line may contain numbers or capital letters.

Thanks again for your help!

"macropod" wrote:

Hi Bill,

Instead of:

Range("A1").Activate
ActiveSheet.Paste

you should be able to use:

ActiveSheet.Range("A1").Paste

--
Cheers
macropod
[MVP - Microsoft Word]


"Bonsai Bill" wrote in message
...
Hello macrpod,

Your proposed code worked! However, for some reason I had to replace
Cells("A1").Paste
with
Range("A1").Activate
ActiveSheet.Paste

to eliminate mismatch error.

One great thing about your code was that I can avoid having others load Word
References because I removed ".Wrap = wdFindStop" I close the Word file right
after doing the copy and pasting so I assume there should be no problem with
using default Wrap.

Thanks for your help! I really appreciate folks like you and Barb helping
the less knowledgeable users like me. I always learn valuable new tricks and
tips whenever I come to this discussion group!

"macropod" wrote:

Hi Bill,

You could replace:

With oWord.Find
.Text = "Seq."
.Replacement.Text = ""
....
End With 'oWord
Selection.Find.Execute
Selection.MoveRight Unit:=wdCharacter, Count:=66, Extend:=wdExtend
Selection.MoveDown Unit:=wdLine, Count:=100, Extend:=wdExtend
Selection.Copy
Cells("A1").Activate
ActiveSheet.Paste

with:

With oWord.Content
With .Find
.ClearFormatting
.Text = "(Seq.)*(tab80tab540)"
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindStop
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchAllWordForms = False
.MatchSoundsLike = False
.MatchWildcards = True
.Execute
End With
.Copy
End With
Cells("A1").Paste

This makes the code impervious to line count changes and should run faster too.

--
Cheers
macropod
[MVP - Microsoft Word]


"Bonsai Bill" wrote in message
...
Hi macropod,

My application doesn't involve links and the desired text is not bookmarked.
However the last line always has the string of "tab80tab540" and could be
used to anchor the last line.

You mentioned that a different search method would be safer than using Find.
I checked Help but didn't find any promising methods, What would you suggest?
A simple few lines of code would be very helpful. (I failed to mention that
I am developing code using OfficeXP but have users that have Office 2007.)

Thanks for your help.

"macropod" wrote:

Hi Bill,

There are various way of approaching this, depending on how your Word document is set up.

For example, if the range you want to import is bookmarked in Word, and you want to link to it so that the Excel
workbook
will
update to reflect the bookmark's contents, you could use a formula in Excel like:
=Word.Document|'C:\My Documents\Wordfile.doc'!'!BkMrk'
where 'C:\My Documents\Wordfile.doc' is the document's path and name, and 'BkMrk' is the name of the bookmark.
No macro required.

Conversely, if the range you want to copy is for a one-off update, and there's always a specific start & end string
(you've
already
indicated '' as the start string), then a different kind of search in Word might be better. This is especially so since
line
counts
in Word often depend on the current printer driver.

--
Cheers
macropod
[MVP - Microsoft Word]


"Bonsai Bill" wrote in message
...
Thanks Barb, that did the trick! I knew the problem but didn't know the
solution.

I know I may be asking too much but is there a workaround for the coding?
The reason is that my users know zip about macros. Maybe the easiest thing to
do is to include it in the instruction writeup.

"Barb Reinhardt" wrote:

Excel doesn't know you're working with Word objects. You need to tell it.

You'll need to set a reference to the Word objects in the VBE using Tools -
References.

Select "Microsoft Word 11.0 Object Library" (for 2003)

This code should get you started

Sub Test()
Dim WordApp As Word.Application
Dim oDoc As Word.Document

Set WordApp = GetObject(, "Word.Application")
Debug.Print WordApp

If WordApp Is Nothing Then Exit Sub

For Each oDoc In WordApp.Documents
Debug.Print oDoc.Name
Next oDoc

Set WordApp = Nothing

End Sub

--
HTH,
Barb Reinhardt



"Bonsai Bill" wrote:

I need to import a number of text lines from Word file into Excel. Using what
I found from postings here on importing tables and the Word macro recorder I
have the following partial code:
FName = Application _
.GetOpenFilename("Word Files (*.doc), *.doc")
Set oWord = GetObject(FName)
With oWord.Find
.Text = "Seq."
.Replacement.Text = ""
...
End With 'oWord
Selection.Find.Execute
Selection.MoveRight Unit:=wdCharacter, Count:=66, Extend:=wdExtend
Selection.MoveDown Unit:=wdLine, Count:=100, Extend:=wdExtend
Selection.Copy
Cells("A1").Activate
ActiveSheet.Paste

However, Excel VBA doesn't know wdCharacter or wdLine. It doesn't know
wdForward if I try a Move function. The number of lines of text to import is
constant.

Any workarounds would be greatly appreciated!