Extract text strings from xls cells containing outlook items
 

Hi
I am exporting mail bodies from Outlook into column B in an xls sheet
First four rows in each cell look like example below. Length of these rows
vary.

What I need is a macro that can loop throu rows and:

- copy text "Username: ......." to col C
- copy text "Error in: ......." to col D
- copy text "Error message: .." to col E
Only text after : would be perfect, but not neccesary
(column B will be deleted when copying is completed)

My programming "skills" are limited - any help would be highly appreciated
Thank you!
Ulf


-----------------------------------

Error Caught in Application_Error event

Username: 01234asdfg

Error in: https://www.xxxxxxx.com/Main/Event/r...x?ItemId=12345

Error Message: Invalid length for a Base-64 char array.



Stack Trace: at System.Convert.FromBase64String(String s)

at System.Web.UI.ObjectStateFormatter.Deserialize(Str ing inputString)

at
System.Web.UI.ObjectStateFormatter.System.Web.UI.I StateFormatter.Deserialize(String serializedState)

at System.Web.UI.Util.DeserializeWithAssert(IStateFor matter formatter,
String serializedState)

at System.Web.UI.HiddenFieldPageStatePersister.Load()



TargetSite: Byte[] FromBase64String(System.String)


-----------------------------------
This is the nice solution I found for exporting from Outlook to Excel
http://blogs.techrepublic.com.com/msoffice/?p=744

Extract text strings from xls cells containing outlook items
 

On Wed, 11 Feb 2009 08:15:01 -0800, ulfb
wrote:

Hi
I am exporting mail bodies from Outlook into column B in an xls sheet
First four rows in each cell look like example below. Length of these rows
vary.

What I need is a macro that can loop throu rows and:

- copy text "Username: ......." to col C
- copy text "Error in: ......." to col D
- copy text "Error message: .." to col E
Only text after : would be perfect, but not neccesary
(column B will be deleted when copying is completed)

My programming "skills" are limited - any help would be highly appreciated
Thank you!
Ulf


-----------------------------------

Error Caught in Application_Error event

Username: 01234asdfg

Error in: https://www.xxxxxxx.com/Main/Event/r...x?ItemId=12345

Error Message: Invalid length for a Base-64 char array.



Stack Trace: at System.Convert.FromBase64String(String s)

at System.Web.UI.ObjectStateFormatter.Deserialize(Str ing inputString)

at
System.Web.UI.ObjectStateFormatter.System.Web.UI. IStateFormatter.Deserialize(String serializedState)

at System.Web.UI.Util.DeserializeWithAssert(IStateFor matter formatter,
String serializedState)

at System.Web.UI.HiddenFieldPageStatePersister.Load()



TargetSite: Byte[] FromBase64String(System.String)


-----------------------------------
This is the nice solution I found for exporting from Outlook to Excel
http://blogs.techrepublic.com.com/msoffice/?p=744

Here's one solution using a User Defined Function (UDF) and Regular
Expressions.

It does assume, as you wrote, that ALL of the lines are in a single cell.

It also assumes that the entire phrase to be extracted is on the same line in
that cell. If the phrase could "run over" more than one line, we would need to
know how to tell that in order to make appropriate changes.

Set up your worksheet with labels in column 1.

The label must be EXACTLY the word or words that precedes the colon (:).

So, in your example:

C1: Username
D1: Error in
E1: Error message

Then enter the formula:

C2: =ReExtr($B2,C$1)
Fill right D2:E2

Then select C2:E2 and fill down as far as required.

(This will initially return a #NAME! error)

To enter the UDF, <alt-F11 opens the VB Editor. Ensure your project is
highlighted in the project explorer window, then Insert/Module and paste the
code below into the window that opens.

=======================================
Option Explicit
Function ReExtr(rg As Range, Extr As String) As String
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.MultiLine = True
re.ignorecase = True
re.Global = True
re.Pattern = "[\s\S]*" & Extr & ":\s*(.*)[\s\S]*"
ReExtr = re.Replace(rg, "$1")
End Function
================================
--ron

Extract text strings from xls cells containing outlook items
 

Thank you Ron - you solved my problem!
/ulf




"Ron Rosenfeld" wrote:

On Wed, 11 Feb 2009 08:15:01 -0800, ulfb
wrote:

Hi
I am exporting mail bodies from Outlook into column B in an xls sheet
First four rows in each cell look like example below. Length of these rows
vary.

What I need is a macro that can loop throu rows and:

- copy text "Username: ......." to col C
- copy text "Error in: ......." to col D
- copy text "Error message: .." to col E
Only text after : would be perfect, but not neccesary
(column B will be deleted when copying is completed)

My programming "skills" are limited - any help would be highly appreciated
Thank you!
Ulf


-----------------------------------

Error Caught in Application_Error event

Username: 01234asdfg

Error in: https://www.xxxxxxx.com/Main/Event/r...x?ItemId=12345

Error Message: Invalid length for a Base-64 char array.



Stack Trace: at System.Convert.FromBase64String(String s)

at System.Web.UI.ObjectStateFormatter.Deserialize(Str ing inputString)

at
System.Web.UI.ObjectStateFormatter.System.Web.UI. IStateFormatter.Deserialize(String serializedState)

at System.Web.UI.Util.DeserializeWithAssert(IStateFor matter formatter,
String serializedState)

at System.Web.UI.HiddenFieldPageStatePersister.Load()



TargetSite: Byte[] FromBase64String(System.String)


-----------------------------------
This is the nice solution I found for exporting from Outlook to Excel
http://blogs.techrepublic.com.com/msoffice/?p=744

Here's one solution using a User Defined Function (UDF) and Regular
Expressions.

It does assume, as you wrote, that ALL of the lines are in a single cell.

It also assumes that the entire phrase to be extracted is on the same line in
that cell. If the phrase could "run over" more than one line, we would need to
know how to tell that in order to make appropriate changes.

Set up your worksheet with labels in column 1.

The label must be EXACTLY the word or words that precedes the colon (:).

So, in your example:

C1: Username
D1: Error in
E1: Error message

Then enter the formula:

C2: =ReExtr($B2,C$1)
Fill right D2:E2

Then select C2:E2 and fill down as far as required.

(This will initially return a #NAME! error)

To enter the UDF, <alt-F11 opens the VB Editor. Ensure your project is
highlighted in the project explorer window, then Insert/Module and paste the
code below into the window that opens.

=======================================
Option Explicit
Function ReExtr(rg As Range, Extr As String) As String
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.MultiLine = True
re.ignorecase = True
re.Global = True
re.Pattern = "[\s\S]*" & Extr & ":\s*(.*)[\s\S]*"
ReExtr = re.Replace(rg, "$1")
End Function
================================
--ron

Extract text strings from xls cells containing outlook items
 

On Wed, 11 Feb 2009 22:51:02 -0800, ulfb
wrote:

Thank you Ron - you solved my problem!
/ulf

You're welcome. Glad to help. Thanks for the feedback.
--ron