Export user data to single text file

[Evilbob]

Hi,

I hope someone can help me. I have a spreadsheet containing user information for 2000 users that I need to export to single text file in a specific format. Each entry in the text file needs the fields in a very specific format. The fields in the sheet are (e.g):

USER_ID	USER_LAST_NAME	USER_FIRST_NAME	EMAIL	USER_CATEGORY5
SMI0001	SMITH	John	johns@smith.com	AA-02

These need to be exported to a section with the following layout, with a line between each entry. The USER_SITE is always the same, as is the USER_PROFILE.

*** DOCUMENT BOUNDARY ***
FORM=LDUSER
.USER_ID. |aXXSMI0001
.USER_FIRST_NAME. |aJohn
.USER_LAST_NAME. |aSMITH
.USER_SITE. |aXX-SITE
.USER_PROFILE. |aONLINE
.USER_CATEGORY5. |aAA-02
.USER_ADDR1_BEGIN.
.EMAIL. |ajohns@smith.com
.USER_ADDR1_END.

I'm hoping there is something like a mail merge I can use to generate this file, or failing that a VBA script but I'm at a loss to know where to start. Can anyone suggest something please?

Thanks!

 

[John_w]

Try this macro, which creates "User data.txt" in the same folder as the workbook. It's not clear if "*** DOCUMENT BOUNDARY ***" and "FORM=LDUSER" should be repeated for each Excel row; in the code the former is written once and the latter is repeated.

Public Sub Create_Text_File()

    Dim data As Variant, r As Long
    
    With ActiveSheet
        data = .Range("A2:E" & .Cells(.Rows.Count, "A").End(xlUp).Row).Value
    End With
    
    Open ThisWorkbook.Path & "\User data.txt" For Output As #1
    Print #1, "*** DOCUMENT BOUNDARY ***"
    For r = 1 To UBound(data)
        Print #1, "FORM=LDUSER" & vbCrLf & _
                  ".USER_ID. |aXX" & data(r, 1) & vbCrLf & _
                  ".USER_FIRST_NAME. |a" & data(r, 3) & vbCrLf & _
                  ".USER_LAST_NAME. |a" & data(r, 2) & vbCrLf & _
                  ".USER_SITE. |aXX-SITE" & vbCrLf & _
                  ".USER_PROFILE. |aONLINE" & vbCrLf & _
                  ".USER_CATEGORY5. |a" & data(r, 5) & vbCrLf & _
                  ".USER_ADDR1_BEGIN." & vbCrLf & _
                  ".EMAIL. |a" & data(r, 4) & vbCrLf & _
                  ".USER_ADDR1_END." & vbCrLf
    Next
    Close #1
    
End Sub

 

[Evilbob]

Thank you so much, John! That is exactly what I needed.