There are a lot of saving options in Excel, and we can manipulate the saving options as well as any other. When it comes to this, different saving options are available for different file types.
For the options that are not available directly to us, we can always use VBA to achieve what we want. Such is the case with saving text files with the UTF-8 encoding system. In the example below, we will show how to do this.
Save a Text File with UTF-8 Encoding in VBA
UTF-8, in its essence, is an encoding system. It gives us the option to represent characters in the form of ASCII text, but it also allows international characters, such as Arabic or Chinese.
To use VBA in order to save a text file with UTF-8 encoding, we first need to access the VBA, and we will do that by pressing ALT + F11 on our keyboard. When we do that, there will be a separate window shown, and on this one, we will go to Insert >> Module:
In the module, we are going to insert the following code:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
Sub SaveTextFileWithUTF8Encoding() Dim Path As String Dim Content As String Dim fso As Object Dim obj As Object ' File path Path = "C:\Users\User\Documents\VBA Upwork\Save a Text File with UTF-8 Encoding in VBA\TextFile.txt" ' Content of the file Content = "This is the content that we have in our Text file" ' New FileSystemObject Set fso = CreateObject("Scripting.FileSystemObject") ' Open a text file for writing with UTF-8 encoding Set obj = fso.CreateTextFile(Path, True, True) ' Write the content to the file obj.Write Content ' Close the file obj.Close ' Clean up Set obj = Nothing Set fso = Nothing MsgBox "Text file saved with UTF-8 encoding." End Sub |
This is what this code looks like in the module:
For our code to work, we need to reference a specific library in our VBA project. A library is basically a code structure, that is pre-defined, and we can access them to get multiple options. In VBA, libraries usually give us new functions and objects. In our example, we need a Scripting library. You can find and add libraries by going to the Tools tab in the Module, and then choosing References:
To add a certain library, you can search for them in the window that appears, and add them by clicking on the little box next to the library title:
If you do not have a particular library in the list, you can always add them. Since we do not have a Scripting library installed, in this particular case, we can use Microsoft Object Library, which you can see as chosen in the picture above, to make our code work. This will allow us to use the FileSystemObject (which is a part of the Scripting library) directly.
Now, as for an explanation of the code:
The first part declares four variables: Path and Content as a String, and fso and obj as an Object.
Then we set the values for these variables. The Path will be where our file will be stored (we use the link where our folder is located). The Content variable will store our text, which will be “This is the content that we have in our Text file”. Our fso variable will be used to create an object (basically, our text file), which we call the previously described Scripting library and FileSystemObject. The obj variable will make the text file in our object.
For the final step, we will write the content in our file, and then close that file.
Clean up part is used to delete the created object and file from the memory because these things can take up a lot of space.
Lastly, we will create a message that the file was saved with UTF-8 encoding.
When we execute the code by pressing F5 while in the module, we will get that exact message:
And our file will be saved in the desired folder:
When we open the file, we can see our content is there as well:
