Getting started with access-vba Enable/Disable Shift Key on DB Open Access Error Codes (Form.Error, DataErr)Check if Table Exists in Current DB Create Text Delimited File in Access Vba

Create Text Delimited File in Access Vba

Remarks:

This is worth pointing out:

 Wholeline = Wholeline & aRR(i, j) & Sep

Because recordsets dump to arrays transposed, you will have to read it to the text file backwards. This is actually kind of handy if youre working with dynamic arrays, as it is already transposed for you, so redim'ing the "row count" can be done before any heavy lifting.

Also worth nothing:

You can easily transpose youre array by dumping it into a new one line by line using this syntax:

    Dim xaRR() As String
    ReDim xaRR(q, z)
    xaRR(j, i) = aRR(i, j)

this isnt too relevant to my post, but its worth pointing out.

Example:

Private Sub this()
    Dim rs As DAO.Recordset
    Dim q%: Dim z%
    Set rs = CurrentDb.OpenRecordset("SELECT * FROM Invoice;")

    With rs
        rs.MoveLast
        q = rs.RecordCount
        rs.MoveFirst
        z = rs.Fields.Count
    End With
    
    Dim aRR As Variant
    
    aRR = rs.GetRows(q)
    
    Dim i%: Dim j%: Dim counter#: Dim Sep$: Dim Wholeline$: Dim NewTextFile$: Dim path$: Dim fileNameV$
    
    Sep = "|"
    path = Environ("USERPROFILE") & "\Desktop" & "\"
    fileNameV = "Text007.txt"
    NewTextFile = path & fileNameV
    Open NewTextFile For Output As #2
    For j = LBound(aRR, 2) To UBound(aRR, 2)
        For i = LBound(aRR, 1) To UBound(aRR, 1)
            Wholeline = Wholeline & aRR(i, j) & Sep
        Next i
        Print #2, Wholeline
        Wholeline = vbNullString
    Next j
    Close #2

    rs.Close
    Set rs = Nothing
    Erase aRR
    
End Sub

Contributors

Topic Id: 9051

Example Ids: 28108

This site is not affiliated with any of the contributors.