Removing Data prior to a Colon running a Macro

[Marsman]

how would i set my script to remove all the data prior to the Colon in this macro?



Email Example:

Hahahah

Please be sure to check the API Results below for errors.

Thank you!

API Results : API Call Successful Status OK

Power Supply Node : 2C06

MAC Address : 00:00:08:14:00:00

Power Supply Type : StandBy

Power Supply Name : 2C06_-A

Power Supply Number : A

No. of Batteries : 1 String Of 3

AC Output Voltage Rating : 90VAC

House Number :

Address : Elm St

City : Smalltown

State : DM

Zip : XXXXX

Latitude : 27.7509

Longitude : -83.8767

<tbody>
</tbody>

With Mysheet
.Range("A1") = "Subject"
.Range("B1") = "Received"
.Range("D1") = "Category"
.Range("E1") = "Continuity API Results"
.Range("F1") = "Power Supply Node"
.Range("G1") = "MAC ADDRESS"
.Range("H1") = "POWER SUPPLY TYPE"
.Range("I1") = "POWER SUPPLY NAME"
.Range("J1") = "POWER SUPPLY NUMBER"
.Range("K1") = "NO. OF BATTERIES"
.Range("L1") = "AC OUTPUT VOLTAGE RATING"
.Range("M1") = "HOUSE NUMBER"
.Range("N1") = "ADDRESS"
.Range("O1") = "CITY"
.Range("P1") = "STATE"
.Range("Q1") = "ZIP"
.Range("R1") = "LATITUDE"
.Range("S1") = "LONGITUDE"




End With


For Each Item In myfolder.Items
Set x = Item
With Mysheet
Lines = Split(x.Body, Chr(10))


.Range("A" & MyRow) = x.Subject
.Range("B" & MyRow) = Format(x.ReceivedTime, "DD/MM/YYYY")


.Range("D" & MyRow) = Lines(0)
.Range("E" & MyRow) = Lines(6)
.Range("F" & MyRow) = Lines(7)
.Range("G" & MyRow) = Lines(8)
.Range("H" & MyRow) = Lines(9)
.Range("I" & MyRow) = Lines(10)
.Range("J" & MyRow) = Lines(11)
.Range("K" & MyRow) = Lines(12)
.Range("L" & MyRow) = Lines(13)
.Range("M" & MyRow) = Lines(14)
.Range("N" & MyRow) = Lines(15)
.Range("O" & MyRow) = Lines(16)
.Range("P" & MyRow) = Lines(17)
.Range("Q" & MyRow) = Lines(18)
.Range("R" & MyRow) = Lines(19)
.Range("S" & MyRow) = Lines(20)


Export Result:

Subject

Received

Category

Continuity API Results

Power Supply Node

MAC ADDRESS

POWER SUPPLY TYPE

POWER SUPPLY NAME

POWER SUPPLY NUMBER

NO. OF BATTERIES

AC OUTPUT VOLTAGE RATING

HOUSE NUMBER

ADDRESS

CITY

STATE

ZIP

LATITUDE

LONGITUDE

RE: Beltway_Power_Supply_Updates

27/04/2017

Hahahah

API Results : API Call Successful Status OK

Power Supply Node : PR368

MAC Address : 00:00:08:14:00:00

Power Supply Type : StandBy

Power Supply Name : 2C06_-A

Power Supply Number : A

No. of Batteries : 1 String Of 3

AC Output Voltage Rating : 90VAC

House Number :

Address : Elm St

City : Smalltown

State : DM

Zip : XXXXX

Latitude : 27.7509

Longitude : -83.8767

<tbody>
</tbody>

would like to see:

Subject

Received

Category

Continuity API Results

Power Supply Node

MAC ADDRESS

POWER SUPPLY TYPE

POWER SUPPLY NAME

POWER SUPPLY NUMBER

NO. OF BATTERIES

AC OUTPUT VOLTAGE RATING

HOUSE NUMBER

ADDRESS

CITY

STATE

ZIP

LATITUDE

LONGITUDE

RE: Power_Supply_Updates

27/04/2017

Hahahah

API Call Successful Status OK

PR368

00:00:08:14:00:00

StandBy

2C06_-A

A

1 String Of 3

90VAC

Elm St

Smalltown

DM

XXXXX

27.7509

-83.8767

<tbody>
</tbody>

 

[mole999]

Try if your value was in AW4

> =TRIM(TEXT(MID(AW4,FIND(":",AW4)+1,255),""))

 

[Marsman]

Where would I place that with in the code below?

Sub Exp_Email()


resp = MsgBox("Export details for all items in this folder? This may take some time and may appear unresponsive.", vbYesNo + vbQuestion)
If resp = vbNo Then
MsgBox "Cancelled", vbOKOnly + vbInformation, "Outlook"
Exit Sub
End If


Dim myfolder As Outlook.Folder
Dim MyExcel As Object
Dim Myworkbook As Object
Dim Mysheet As Object
Dim MyRow As Integer
Dim Lines As Variant


MyRow = 2


Set myfolder = Application.ActiveExplorer.CurrentFolder
Dim a As String


Set MyExcel = CreateObject("Excel.Application")
Set Myworkbook = MyExcel.Workbooks.Add


Set Mysheet = Myworkbook.sheets("Sheet1")


On Error Resume Next
Dim x As Variant


With Mysheet
.Range("A1") = "Subject"
.Range("B1") = "Received"
.Range("D1") = "Category"
.Range("E1") = "API Results"
.Range("F1") = "Power Supply Node"
.Range("G1") = "MAC ADDRESS"
.Range("H1") = "POWER SUPPLY TYPE"
.Range("I1") = "POWER SUPPLY NAME"
.Range("J1") = "POWER SUPPLY NUMBER"
.Range("K1") = "NO. OF BATTERIES"
.Range("L1") = "AC OUTPUT VOLTAGE RATING"
.Range("M1") = "HOUSE NUMBER"
.Range("N1") = "ADDRESS"
.Range("O1") = "CITY"
.Range("P1") = "STATE"
.Range("Q1") = "ZIP"
.Range("R1") = "LATITUDE"
.Range("S1") = "LONGITUDE"




End With


For Each Item In myfolder.Items
Set x = Item
With Mysheet
Lines = Split(x.Body, Chr(10))


.Range("A" & MyRow) = x.Subject
.Range("B" & MyRow) = Format(x.ReceivedTime, "DD/MM/YYYY")


.Range("D" & MyRow) = Lines(0)
.Range("E" & MyRow) = Lines(6)
.Range("F" & MyRow) = Lines(7)
.Range("G" & MyRow) = Lines(8)
.Range("H" & MyRow) = Lines(9)
.Range("I" & MyRow) = Lines(10)
.Range("J" & MyRow) = Lines(11)
.Range("K" & MyRow) = Lines(12)
.Range("L" & MyRow) = Lines(13)
.Range("M" & MyRow) = Lines(14)
.Range("N" & MyRow) = Lines(15)
.Range("O" & MyRow) = Lines(16)
.Range("P" & MyRow) = Lines(17)
.Range("Q" & MyRow) = Lines(18)
.Range("R" & MyRow) = Lines(19)
.Range("S" & MyRow) = Lines(20)




End With
MyRow = MyRow + 1
Next Item


MsgBox "Finished " & MyRow - 2 & " Records Exported"
MyExcel.Visible = True
'
Set MyExcel = Nothing
Set Myworkbook = Nothing
Set Mysheet = Nothing
Set x = Nothing




End Sub

 

[mole999]

maually placing it in G7 and looking at F7, then macro recorder gives me > ActiveCell.FormulaR1C1 = "=TRIM(TEXT(MID(RC[-1],FIND("":"",RC[-1])+1,255),""""))"

I didn't take it beyond looking at > MAC Address : 00:00:08:14:00:00 and producing > 00:00:08:14:00:00

 

[Russell Hauf]

For those rows where you don't want the info before the colon, you could try something like this:

.Range("E" & MyRow) = Mid(Lines(6), InStr(Lines(6), ":")+1)

 

[Marsman]

that was the Code I was looking for Russell.

thanks all for your input.

- Darin