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
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.
|
Similar Threads
Threaded Mode
