ExcelBanter - Exporting Excel Sheet With Additional Info Added To A Text File

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Exporting Excel Sheet With Additional Info Added To A Text File (https://www.excelbanter.com/excel-discussion-misc-queries/194482-exporting-excel-sheet-additional-info-added-text-file.html)

Exporting Excel Sheet With Additional Info Added To A Text File

Hi ,

I'm trying to export data from an excel sheet into a text file in a specific format inserting the excel data into the format below (excel column data in between #'s).

dn: cn=#UID#,cn=Users,ou=MyDomain,dc=com
objectClass: inetOrgPerson
objectClass: organizationalPerson
objectClass: person
objectClass: top
givenName: #Fname#
sn: #Lname#
uid: #UID#
userPassword: #Password#

The excel file and column headers are setup as below:

Fname........Lname....................UID......... ................Password
Marie............Lily..................myoune..... ...............9eW2n+st
Rita..............Jain.....................rjain.. .....................yE#eCA2a
Peter...........Craig..................pcraig..... ...................Qutru4r?

Below is the final outcome I'm trying to accomplish in the text file:

dn: cn=myoune,cn=Users,ou=MyDomain,dc=com
objectClass: inetOrgPerson
objectClass: organizationalPerson
objectClass: person
objectClass: top
givenName: Marie
sn: Lily
uid: myoune
userPassword: 9eW2n+st

dn: cn=rjain,cn=Users,ou=MyDomain,dc=com
objectClass: inetOrgPerson
objectClass: organizationalPerson
objectClass: person
objectClass: top
givenName: Rita
sn: Jain
uid: rjain
userPassword: yE#eCA2a

dn: cn=pcraig,cn=Users,ou=MyDomain,dc=com
objectClass: inetOrgPerson
objectClass: organizationalPerson
objectClass: person
objectClass: top
givenName: Peter
sn: Craig
uid: pcraig
userPassword: Qutru4r?

I have over 2,000 records as such to do the export and it is very tiresome to manually create this file. Is there any way to expedite this process and create the text file?

I would really appreciate if anyone can help.

Thank you.

SOLVED:

I've been given a reply to this request and thought I would share the result with this forum as well. I've tested the code and it executed exactly how I was needing the text file to be created and formatted.

Code:

Sub WriteIt()

  WriteFile Range("A2", Cells(Rows.Count, 1).End(xlUp).Address), _

  ThisWorkbook.Path & "\MyFile.txt"

End Sub



Sub WriteFile(fromRange As Range, toFile As String)

  Dim iHandle As Integer, cell As Range

  iHandle = FreeFile

  Open toFile For Output Access Write As #iHandle

  For Each cell In fromRange

    Print #iHandle, "dn: cn=" & cell.Offset(0, 1).Value & ",cn=Users,ou=MyDomain,dc=com"

    Print #iHandle, "objectClass: inetOrgPerson"

    Print #iHandle, "objectClass: organizationalPerson"

    Print #iHandle, "objectClass: person"

    Print #iHandle, "objectClass: Top"

    Print #iHandle, "givenName: " & cell.Value

    Print #iHandle, "sn: " & cell.Offset(0, 1).Value

    Print #iHandle, "uid: " & cell.Offset(0, 2).Value

    Print #iHandle, "userPassword: " & cell.Offset(0, 3).Value

    Print #iHandle, vbCrLf

  Next cell

  Close #iHandle

End Sub

Quote:

Originally Posted by SouthernBoy718 (Post 692594)