Need help with a transpose

[Paul]

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: (e-mail address removed)
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]

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

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: (e-mail address removed)
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

 

[Paul]

Fantastic!
Works beautifully.
Thanks very much Dave, really appreciate this.

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

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: (e-mail address removed)
        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- Hide quoted text -

- Show quoted text -