Exporting Query in UTF-8 format with VBA

  • Thread starter Thread starter Albert S.
  • Start date Start date
A

Albert S.

Hello,
We need to export a query to a text file using UTF-8 encoding through VBA. I
see that the query text export wizard has the capability to switch encoding,
but I don't see this as being part of VBA? I see there is an object called
system.text.UTF8Encoding, but I am not sure how to apply this to our
procedure. We need to export to text in an exact format where each field is
separated by a carraige return. The query is pulling the data from
SQLServer2005. I tried the export wizard, but the format is incorrect for our
needs. Also, the UTF-8 export seemed to actually be in UTF-16.
Here is the code so far. Thanks for any help!

Private Sub cmdWrite_Click()
Dim FileNumber As Integer
Dim strLine As String
Dim rs As DAO.Recordset
Dim i As Integer
Dim strMakrPath As String
Dim strFullPath As String
Dim strFormatedFile As String

strFormatedFile = "FileName"
FileNumber = FreeFile
strMakrPath = "C:\MARC\marcmakr.exe"

Set rs = CurrentDb.OpenRecordset("qryMARC", dbOpenDynaset, dbSeeChanges)
Open "C:\MARC\TF" & strFormatedFile & ".txt" For Output As #FileNumber

Do While Not rs.EOF
For i = 0 To 18
strLine = "" & Nz(rs.Fields(i))
If Len(strLine) > 0 Then
Print #FileNumber, strLine
End If
Next i
Print #FileNumber,
rs.MoveNext
Loop

strFullPath = strMakrPath & " " & "C:\MARC\TF" & strFormatedFile & ".txt"
& " " & "C:\MARC\TF" & strFormatedFile & ".mrc" & " " & "C:\MARC\text21.txt"
Shell strFullPath, vbHide

End Sub
 
Back
Top