It looks like the layout is very nice:
Option Explicit
Sub testme()
Dim CurWks As Worksheet
Dim NewWks As Worksheet
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim oRow As Long
Dim myStep As Long
Dim myStr As String
Dim CommaPos As Long
Set CurWks = Worksheets("Sheet1")
Set NewWks = Worksheets.Add
myStep = 8
With CurWks
FirstRow = 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
oRow = 1
For iRow = FirstRow To LastRow Step myStep
myStr = Mid(.Cells(iRow, "A").Value, 15)
CommaPos = InStr(1, myStr & ",", ",", vbTextCompare)
myStr = Left(myStr, CommaPos - 1)
NewWks.Cells(oRow, "A").Value = myStr
NewWks.Cells(oRow, "B").Value _
= Trim(Mid(.Cells(iRow + 2, "A").Value, 28, 19))
oRow = oRow + 1
Next iRow
End With
End Sub
Paul wrote:
Hi everyone,
I not too good a VB and still learing, but I need help with the
following;
I have this output.
7 lines followed by a blank in a text file.
These repeat for about 3000 times all with different cn= name and last
changed date.
the 2nd line and subsequent lines are indented.
I want to return only the cn= name and the date into two columns cn in
col A and date in col B
and remove all the other text. I can remove the text if someone can
assist me with the code.
Object DN: cn=yeotest,ou=people,o=awb
EMail:
Last Changed Date: 2008-08-24 21:51:42 Z
Password Status: Enabled, Set
Distribution Password Status: Set
Simple Password Status: Set
Password Policy DN: cn=Universal Passwords,cn=Password
Policies,cn=Security
Object DN: cn=yeoadmin,ou=people,o=awb
EMail: [NONE]
Last Changed Date: 2008-08-25 03:15:54 Z
Password Status: Enabled, Set
Distribution Password Status: Set
Simple Password Status: Set
Password Policy DN: cn=Universal Passwords,cn=Password
Policies,cn=Security
Object DN: cn=commsapprover,ou=people,o=awb
EMail: [NONE]
Last Changed Date: [UNKNOWN]
Password Status: Enabled, Not set
Distribution Password Status: Not set
Simple Password Status: Not set
Password Policy DN: cn=Universal Passwords,cn=Password
Policies,cn=Security
this is pretty much the output I want to see.
Col A Col B
yeotest 2008-08-24 21:51:42
yeoadmin 2008-08-25 03:15:54
commsapprover [UNKNOWN]
Would appreciate any help on this.
Cheers Paul
--
Dave Peterson