snb, your code has two mistakes: Split(.Content, vbCr) should be Split(c02, vbCr), and createObject(""Excel.Application") should be createObject("Excel.Application").
With some sample documents that I created, my code produces a result like this:
And yours:
Extract data after key word
-
- Administrator
- Posts: 78394
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Extract data after key word
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 254
- Joined: 09 May 2020, 14:00
Re: Extract data after key word
Besides the errors Hans has pointed out, I get a permissions denied (error 70) and it debugs to the CreateObject("scripting.filesystemobject") line of the code.
-
- Administrator
- Posts: 78394
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Extract data after key word
snb tries to create a file in the root directory of the C: drive. Recent versions of Windows do not like that.
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 254
- Joined: 09 May 2020, 14:00
Re: Extract data after key word
Hi Hans,
I'm trying to figure this out, but it isn't quite going right for me. In this scenario, I just have one keyword and I'd like to extract the contents after it.
Here are a couple of examples of the paragraphs and this keyword will be in a document only once. That is, with the period immediately following the word Contact. The paragraph is also auto numbered.
1.5.6. Contact. 1CrossingPath@ggmail.com. 777 555-1234.
or
3.4.5. Contact. AlphaCentury@ggmail.com. 222 666-1234.
I would like to retrieve the email and phone number
I'm trying to figure this out, but it isn't quite going right for me. In this scenario, I just have one keyword and I'd like to extract the contents after it.
Here are a couple of examples of the paragraphs and this keyword will be in a document only once. That is, with the period immediately following the word Contact. The paragraph is also auto numbered.
1.5.6. Contact. 1CrossingPath@ggmail.com. 777 555-1234.
or
3.4.5. Contact. AlphaCentury@ggmail.com. 222 666-1234.
I would like to retrieve the email and phone number
Code: Select all
With Selection.Find
.ClearFormatting
.MatchWildcards = True
.Text = "Contact.[ ]{1,}[<0-9.]"
.Execute
End With
Selection.MoveStart Count:=Len("Contact. ")
-
- Administrator
- Posts: 78394
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Extract data after key word
Does this do what you want?
Code: Select all
With Selection.Find
.ClearFormatting
.MatchWildcards = True
.Text = "Contact.[ ]{1,}"
If .Execute Then
Selection.MoveStart Count:=Len("Contact. ")
Selection.MoveEndUntil Cset:=vbCr
myText = Selection.Text
Else
' Not found in this document
End If
End With
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 254
- Joined: 09 May 2020, 14:00
Re: Extract data after key word
That indeed does it just right. Thank you.
-
- 4StarLounger
- Posts: 574
- Joined: 14 Nov 2012, 16:06
Re: Extract data after key word
To filter all emails:
Code: Select all
Sub M_snb()
msgbox join(filter(split(activedocument.content),"@"),vblf)
End Sub