MS Access VBA export to TXT ERROR

Question

I have a Microsoft Access VBA file as shown below.

Sub ExportTextFileDelimited(FileName As String, _
    DataSet As String, _
    Delimiter As String, _
    TextQualifier As String, _
    WithFieldNames As Boolean)
    On Error GoTo ExportTextFile_Err

    Dim cnn As ADODB.Connection
    Dim rst As New ADODB.Recordset

    Dim Directory As String
    Dim MyString As String, strSQL As String
    Dim strDS As String
    Dim I As Integer

    Open FileName For Output As #1
    Set cnn = CurrentProject.Connection

    rst.Open DataSet, cnn, adOpenForwardOnly, adLockReadOnly
    If WithFieldNames Then
        For I = 0 To rst.Fields.Count - 1
            MyString = MyString & TextQualifier & rst(I).Name & TextQualifier & Delimiter
        Next I
        MyString = Left(MyString, Len(MyString) - 1)
        Print #1, MyString
    End If
    rst.MoveFirst
    Do While Not rst.EOF
        MyString = ""
        For I = 0 To rst.Fields.Count - 1
            'check for text datatype (202)
            If rst(I).Type = 202 Then
                MyString = MyString & TextQualifier & _
                rst(I)
            Else
                MyString = MyString & rst(I)
            End If
        Next I
            MyString = Left(MyString, Len(MyString) - 2)
        Print #1, MyString
        rst.MoveNext
    Loop

ExportTextFile_Exit:
    ' Close text file.
    Close #1
    rst.Close
    Set cnn = Nothing
    Exit Sub
ExportTextFile_Err:
    MsgBox Err.Description
    Resume ExportTextFile_Exit
End Sub

This code is working fine and it outputs a Query.txt file with the following data:

Please take a look at and refer to this example image.

My question is, how do I apply maximum width for both AMAL and SAMANTHA GAMAGE data?

Answer 1

What you are asking for here is usually referred to as "Fixed Width" (or "Fixed Length"), which is often considered to be a different type of text file than a Comma Delimmited (or CSV) file. Although you can mix the two concepts together in the same file.

To get a String value to be a specified length you need to use VBA's Space Function.

Dim s as String
s = "AMAL"
s = s & Space(15 - len(s)) 'Add spaces to end
s = Space(15 - len(s)) & s 'Add spaces to beginning

It's unclear from your post why you are adding spaces only to this one field and how you are going to determine how long that field should be (ie, how many spaces to add). Your function really isn't setup properly for this feature. There are a large variety of ways to redesign this function so that it would know which fields to add spaces to and how many spaces to add to that field. And which way you choose really depends on your individual needs.

Just to give you some idea how you would use this in your function:

Dim iMaxLength as Integer
Do While Not rst.EOF
    If Len(rst("UserName").Value) > iMaxLength Then iMaxLength = Len(rst("UserName").Value)
    rst.MoveNext
Loop

Do While Not rst.EOF
    rst.movefirst
    MyString = ""
    For I = 0 To rst.Fields.Count - 1
        'check for text datatype (202)
        If rst(I).Type = 202 Then
            If rst(I).Name = "UserName" Then
                MyString = MyString & TextQualifier & _
                rst(I) & Space(iMaxLength - Len(rst(I))) 
            Else
                MyString = MyString & TextQualifier & _
                rst(I)
            End If
        Else
            MyString = MyString & rst(I)
        End If
    Next I
        MyString = Left(MyString, Len(MyString) - 2)
    Print #1, MyString
    rst.MoveNext
Loop