Save a .TXT file from Excel via VBA in UTF-8

%3CLINGO-SUB%20id%3D%22lingo-sub-2412051%22%20slang%3D%22en-US%22%3ESave%20a%20.TXT%20file%20from%20Excel%20via%20VBA%20in%20UTF-8%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2412051%22%20slang%3D%22en-US%22%3E%3CP%3ESave%20a%20.TXT%20file%20from%20Excel%20via%20VBA%20in%20this%20attached%20file%20and%20select%20%2F%20force%20%2F%20hack%20%2F%20default%20some%20parameter%20(anywhere%20in%20the%20system%2C%20registry%2C%20Excel%2C%20VBA%2C%20etc.)%20to%20control%20character%20Encoding%20to%20export%20as%20utf-8.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EDevice%20and%20OS%20platform%3A%3C%2FSTRONG%3E%3CBR%20%2F%3EIntel%20Core%20i5-4590%20CPU%20%40%203.3GHz%20with%208%20GB%20RAM.%3CBR%20%2F%3EWindows%2010%20Pro%2064-bit%20OS%20X64-based%20processor%20(3-month-old%20software%20installation)%3CBR%20%2F%3EVersion%202004%20(OS%20build%2019041.985)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EExcel%20product%20name%20and%20version%20number%3A%3CBR%20%2F%3EMicrosoft%20365%3A%20Excel%20Version%202105%20(Build%2014026.20246%20Click-to-Run)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EFile%20Example%3A%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CA%20href%3D%22http%3A%2F%2Feaglesvn.com%2Fdev%2Fexcel_example.zip%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3Ehttp%3A%2F%2Feaglesvn.com%2Fdev%2Fexcel_example.zip%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EWorkflow%3A%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E1)%20Open%20attached%20XLSM%20(utf-8%20encoded)%20Template%20(Excel2GIFT)%20containing%20a%20VBA%20script%20that%20exports%20a%20TXT%20file%20in%20Moodle%20GIFT%20Format.%3C%2FP%3E%3CP%3E2)%20I%20then%20enter%20data%20on%20Sheet1%20which%20populates%20a%20multiple-choice%20quiz%20template%20on%20the%20GIFT%20Exporter%20Sheet%20via%20simple%20cell%20linking.%3C%2FP%3E%3CP%3E3)%26nbsp%3B%20I%20then%20'Clear%20Contents'%20of%20empty%20%22Question%22%20Cells%20and%20run%20the%20VBA%20Script%20to%20export%20a%20TXT%20file.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUp%20to%20here%2C%20there%20are%20no%20problems.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CU%3EHowever%20recently%2C%20the%20exported%20GIFT%20format%20TXT%20file%20is%20now%20encoded%20using%20ANSI%2C%20thus%20corrupting%20the%20utf-8%20based%20certain%20Vietnamese%20characters%20ANSI%20doesn't%20support.%3C%2FU%3E%3CEM%3E%20I%20am%20NOT%20sure%20if%20this%20issue%20began%20after%20some%20update.%3C%2FEM%3E%20During%20export%2C%20the%20'save%20as'%20window%3D%26gt%3B%3CEM%3Etools%3C%2FEM%3E%20does%20NOT%20offer%20the%20possibility%20to%20edit%20Character%20Encoding%20-%20instead%20it%20says%20'%3CEM%3Emap%20network%20drive%3C%2FEM%3E...'%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20spent%20a%20week%20researching%20this%20with%20little%20progress%20on%20what%20should%20be%20a%20simple%20task%20-%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESave%20a%20.TXT%20file%20from%20Excel%20via%20VBA%20in%20this%20attached%20file%20and%20select%20%2F%20force%26nbsp%3B%20%2F%20hack%20%2F%20default%20some%20parameter%20(anywhere%20in%20the%20system%2C%20registry%2C%20Excel%2C%20VBA%2C%20etc.)%20to%20control%20character%20Encoding%20to%20export%20as%20utf-8.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20submit%20to%20the%20Braintrust%20of%20this%20learned%20community%20for%20a%20solution...%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2412051%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20Scripts%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
New Contributor

Save a .TXT file from Excel via VBA in this attached file and select / force / hack / default some parameter (anywhere in the system, registry, Excel, VBA, etc.) to control character Encoding to export as utf-8.

 

Device and OS platform:
Intel Core i5-4590 CPU @ 3.3GHz with 8 GB RAM.
Windows 10 Pro 64-bit OS X64-based processor (3-month-old software installation)
Version 2004 (OS build 19041.985)

 

Excel product name and version number:
Microsoft 365: Excel Version 2105 (Build 14026.20246 Click-to-Run)

 

File Example:
http://eaglesvn.com/dev/excel_example.zip

 

Workflow:

1) Open attached XLSM (utf-8 encoded) Template (Excel2GIFT) containing a VBA script that exports a TXT file in Moodle GIFT Format.

2) I then enter data on Sheet1 which populates a multiple-choice quiz template on the GIFT Exporter Sheet via simple cell linking.

3)  I then 'Clear Contents' of empty "Question" Cells and run the VBA Script to export a TXT file.

 

Up to here, there are no problems.

 

However recently, the exported GIFT format TXT file is now encoded using ANSI, thus corrupting the utf-8 based certain Vietnamese characters ANSI doesn't support. I am NOT sure if this issue began after some update. During export, the 'save as' window=>tools does NOT offer the possibility to edit Character Encoding - instead it says 'map network drive...'

 

I've spent a week researching this with little progress on what should be a simple task -

 

Save a .TXT file from Excel via VBA in this attached file and select / force  / hack / default some parameter (anywhere in the system, registry, Excel, VBA, etc.) to control character Encoding to export as utf-8.

 

I submit to the Braintrust of this learned community for a solution...

0 Replies