troubleshooting Question

Access VBA Export Readable .txt file

Avatar of Murray Brown
Murray BrownFlag for United Kingdom of Great Britain and Northern Ireland asked on
VBA
3 Comments1 Solution12 ViewsLast Modified:
Hi. I was given the following code by an expert. The output file is comma delimited. How do I create a text file that is readable in columns? Is that even possible? Thanks

Sub oTestExport()
    
    OutputToText ("Select * From t_Arms")
    
End Sub


Function OutputToText(ByVal oSQL As String)

    Dim Recordset As DAO.Recordset
    
    Dim mfile As Integer
    Dim strLine As String
    Dim oPathAndFile As String: oPathAndFile = CurrentProject.Path & "\" & "Text.txt"
    
    strLine = vbNullString
    
    Set rst = CurrentDb.OpenRecordset(oSQL, dbOpenSnapshot)
    
    mfile = FreeFile 'Returns value of 1
    'Open "C:\Temp\Recordset.txt" For Output As #mfile
    Open oPathAndFile For Output As #mfile
    'headers
    For i = 0 To rst.Fields.Count - 1
        strLine = strLine & rst.Fields(i).Name & ","
    Next
    strLine = Left(strLine, Len(strLine) - 1)
    Print #mfile, strLine
    
    With rst
        While Not .EOF
        strLine = vbNullString
        For i = 0 To .Fields.Count - 1
        strLine = strLine & rst.Fields(i) & ","
        Next
        strLine = Left(strLine, Len(strLine) - 1)
        Print #mfile, strLine
        .MoveNext
        Wend
        Close #mfile
    End With
    
End Function
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 3 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros