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]
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!
:
Hi Bill,
Instead of:
Range("A1").Activate
ActiveSheet.Paste
you should be able to use:
ActiveSheet.Range("A1").Paste
--
Cheers
macropod
[MVP - Microsoft Word]
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!
:
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]
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.
:
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]
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.
:
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
:
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!
