Avatar of Murray Brown
Murray BrownFlag for United Kingdom of Great Britain and Northern Ireland

asked on 

Access VBA Export Readable .txt file

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

Open in new window

VBA

Avatar of undefined
Last Comment
Murray Brown
ASKER CERTIFIED SOLUTION
Avatar of John Tsioumpris
John Tsioumpris
Flag of Greece image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of ste5an
ste5an
Flag of Germany image

What is your use-case?

Cause manually writing CSV isn't that simple as it depends on your data, cause you need to handle the separator (escaping) when it is in the data.

Caveat: At least older Access version are not Unicode-aware, when using Open, Print.
Avatar of Murray Brown
Murray Brown
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

thanks very much
VBA
VBA

Visual Basic for Applications (VBA) enables building user-defined functions (UDFs), automating processes and accessing Windows API and other low-level functionality through dynamic-link libraries (DLLs). VBA is closely related to Visual Basic and uses the Visual Basic Runtime Library, but it can normally only run code within a host application rather than as a standalone program. It can, however, be used to control one application from another via OLE Automation. VBA is built into most Microsoft Office applications.

17K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo