This is something that comes up every now and then and I haven't been able to find a solution.
My workflow is 1. copying and pasting text from web pages to Excel; and 2. exporting that data to text files using VBA.
To illustrate this I have simplified the process using the attached xlsm & txt files.
Here is the issue: in cell A1 is the name of the composer Dvořák - this was copied from a web page and the accented characters preserved by Excel - so far so good. When I export that text to a text file using the VBA in the xlsm file the special accent over the 'r' is lost. How can I modify my VBA to preserve that special character (and others)?
I have a feeling my VBA is too simple and that it has something to do with Unicode but that's a bit of a black hole for me.
Hi Kevin,
I tested your code, and the content of that cell was accurately saved in the text file, I was not able to replicate your issue.
Try FileSystemObject to create the text file, might be more reliable.
Sub specialchar()
Dim wbk As Workbook
Dim OutputTxt As String
Dim OutPutFile As Object
Dim StrPath As String
Set wbk = ThisWorkbook
StrPath = "E:TestOutput.txt"
Dim fso As Object
Set fso = CreateObject("Scripting.FileSystemObject")
Set OutPutFile = fso.CreateTextFile(StrPath)
OutPutFile.WriteLine wbk.Worksheets(1).Cells(1).Value
OutPutFile.Close
Set fso = Nothing
Set OutPutFile = Nothing
End Sub
Hi Catalin
I guess that not being able to replicate the issue will be impossible to troubleshoot.
I'll just have to put up with a less than ideal process. That seems to be the case so often these days - software promises a lot but more often than not it fails to deliver exactly what is needed.
Hi Kevin,
If you use Catalin's code but modify this line to open the text file as UniCode rather than ASCII you might find it keeps your accented characters. Works for me anyway.
Set OutPutFile = fso.CreateTextFile(StrPath, True, True)
Cheers
Phil
