Murray Brown
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks very much
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.