Remove line numbers to VBA and replace with spaces Ms Access Gurus

Donate to this site, thank you!

Home Articles Code Tools

Save Unicode Characters to a File from fields in your Access database

Do you want to make a file from data that has Unicode characters? How do you do it? A text file created by standard means won't properly write the information. Use an ADODB.Stream, and write from fields directly.

If you're running this code from Excel, use cell references for the data

Use VBA to Save Unicode characters stored in database fields to a file

Quick Jump

Download VBA Reference Backstory Share Tutoring Donate

Goto the Very Top  

Download

Download zipped BAS file you can import into your Access projects and customize: mod_File_SaveUnicode_s4p__BAS.zip

Download sample database with Merry Christmas in different languages: File_SaveUnicode_Merry_s4p__ACCDB.zip

Remember to UNBLOCK files you download to remove the Mark of the Web. Here are steps to do that: https://msaccessgurus.com/MOTW_Unblock.htm

Goto Top  

VBA

Standard module 

'*************** Code Start *****************************************************
' Purpose  : Create (or replace) a file with Unicode characters using ADODB.Stream
' Author   : crystal (strive4peace)
' This code: https://msaccessgurus.com/VBA/File_SaveUnicode.htm
' Code List: https://msaccessgurus.com/code.htm
' LICENSE  :
'   You may freely use and share this code, but not sell it.
'   Keep attribution. Mark your changes. Use at your own risk
'---------------------------------------------------------------------------------------
'                              File_SaveUnicode_s4p
'---------------------------------------------------------------------------------------
'
Public Sub File_SaveUnicode_s4p() 
' create a file with Unicode characters directly from fields
'  the Translation field might have Unicode characters
' file is created in CurrentProject.Path
   On Error GoTo Proc_Err 
   
   Dim sSQL As String _ 
      ,sPathFile As String _ 
      ,sMsg As String 

   Dim db As DAO.Database _ 
      ,rs As DAO.Recordset 
      
   'early binding
   '  Microsoft ActiveX Data Objects 6.1 Library
   ' Dim MyStream As ADODB.Stream
   'late binding
   Dim MyStream As Object  'ADODB.Stream
   Set MyStream = CreateObject( "ADODB.Stream") 
   
   sPathFile = CurrentProject.Path &  "\MerryChristmas_DifferentLanguages.txt"
   'delete file if it already exists
   If Dir(sPathFile) <>  "" Then 
      Kill sPathFile 
      DoEvents 
   End If 

   sSQL =  "SELECT T.PhraseID" _ 
      &  ", L.Languag" _ 
      &  ", T.Translation" _ 
      &  ", L.pReadingOrder" _ 
      &  " FROM tLanguages AS L " _ 
      &  " INNER JOIN tTranslation AS T ON L.LangID = T.LangID" _ 
      &  " WHERE(T.PhraseID = 1)" _ 
      &  " ORDER BY L.Languag;"

   Set db = CurrentDb 
   Set rs = db.OpenRecordset(sSQL,dbOpenDynaset) 
      
   With MyStream 
      .Type = 2  'adTypeText
      .Charset =  "utf-8"
      .Open 
      .WriteText  "-- Merry Christmas in different languages --"
      .WriteText Chr(13) & Chr(10) 
      'loop through records
      Do While Not rs.EOF 
         'space and then language
         .WriteText Space(5) & rs!Languag 'could have used a string variable for this
         'new line
         .WriteText Chr(13) & Chr(10) 
         'translation and then another new line
         .WriteText rs!Translation 'THIS is why the ADO stream is needed! 
         .WriteText Chr(13) & Chr(10) 
         rs.MoveNext 
      Loop 
      rs.Close 
      'save and close the file
       .SaveToFile sPathFile 
       .Close 
   End With  'MyStream
   
   sMsg = sPathFile &  " is created, " _ 
      & vbCrLf &  "Do you want to open it?"
      
   If MsgBox(sMsg,vbYesNo, "Open File?") = vbYes Then 
      Call Shell( "Explorer.exe" &  " " & sPathFile,vbNormalFocus) 
   End If 

Proc_Exit: 
   On Error Resume Next 
   'release object variables
   If Not rs Is Nothing Then 
      rs.Close 
      Set rs = Nothing 
   End If 
   Set db = Nothing 
   Set MyStream = Nothing 
   Exit Sub 
  
Proc_Err: 
   MsgBox Err.Description,,_ 
        "ERROR " & Err.Number _ 
        &  "   File_SaveUnicode_s4p "

   Resume Proc_Exit 
   Resume 
End Sub 
'*************** Code End *******************************************************
Code was generated with colors using the free Color Code add-in for Access

Goto Top  

Reference

Help: ADO programmer's reference topics

Help: ADO objects and interfaces

Help: Stream object (ADO)

Help: WriteText method (ADO)

Help: Application.CurrentDb method (Access)

Help: Database.OpenRecordset method (DAO)

Help: Shell function

Goto Top  

Backstory

I started using ADODB streams back in 2009 to write caption files for the videos I post on YouTube so that 'foreign' characters will be what they are actually are, and not a box or a question mark.

When I first faced this issue, I reached out to others. Thanks to Tony Jollans, a Word MVP, who told me how to do it! His example used .Charset = "Unicode" but I changed it to "utf-8" for my caption files, which are in SRT (SubRip) format for YouTube.

Share with others

Here's the link for this page in case you want to copy it and share it with someone:

https://msaccessgurus.com/VBA/File_SaveUnicode.htm

or in old browsers:
http://www.msaccessgurus.com/VBA/File_SaveUnicode.htm

Get Tutoring with Access

Let's connect and team-develop your application together. I teach you how to do it yourself. My goal is to empower you.

While we build something great together, I'll pull in code and features from my vast libraries as needed, cutting out lots of development time. And you'll get links to great resources.

Do you want to step up your application? Let's connect, I can help you make it better. Email me at training@msAccessGurus.com

~ crystal

the simplest way is best, but usually the hardest to see

Goto Top