Excel VBA replaced special character in copied string from cell

[Tanquen]

F6 = "Ω_Not_Monitored"

F6Value = ws.Range("F6").Text

Now F6Value = "O_Not_Monitored"

 

[Artik]

But you will probably get the correct value in F8 after using this code

Sub AAA()
    Dim F6Value As String
    Dim ws As Worksheet
    
    Set ws = ActiveSheet
    F6Value = ws.Range("F6").Value
    
    ws.Range("F8").Value = F6Value
End Sub

Artik

 

[Tanquen]

Yeah, I was doing some testing and a MsgBox shows "O_Not_Monitored" but pasting it into cell F7 I get "Ω_Not_Monitored".

My issue is that the VBA code is writing it out to a text file and it's setting it to "O_Not_Monitored".

Also tried this but still no luck.
F6Value = ChrW(937) & "_Not_Monitored"

 

[Tanquen]

It looks like the Ω was there until printing it into the file. Tried the streaming option and it seems to work now.

    ' Open the file for writing only if modifications were made
'    If fileContent <> modifiedContent Then
'        fileNumber = FreeFile
'        Open filePath For Output As #fileNumber
'        Print #fileNumber, modifiedContent
'        Close #fileNumber
'        MsgBox "The file has been successfully modified."
'    Else
'        MsgBox "No modifications were necessary."
'    End If

    ' Check if modifications were made
    If fileContent <> modifiedContent Then
        ' Create an instance of ADODB.Stream
        Set stream = CreateObject("ADODB.Stream")
        
        ' Specify the stream type (binary) and character set (UTF-8)
        stream.Type = 2 ' adTypeText
        stream.Charset = "utf-8"
        
        ' Open the stream and write the content
        stream.Open
        stream.WriteText modifiedContent
        
        ' Save the content to the file
        stream.SaveToFile filePath, 2 ' adSaveCreateOverWrite
        
        ' Close the stream
        stream.Close
        
        ' Clean up
        Set stream = Nothing
        
        MsgBox "The file has been successfully modified."
    Else
        MsgBox "No modifications were necessary."
    End If

 

[Tanquen]

Now its adding a BOM "" to the start of the file that I can't get rid of.

 

[Alex Blakenburg]

I have not used this before and have not been able to test it but try the code below:.
It is using @Domenic solution to convert BOM to NoBOM presented here in post #3:

VBA Create Text file in UTF-8 rather then ANSI

Hello all, I am trying to read a large text file and put some of it in a new text file as a UTF-8 it keeps saving it as ANSI format any help on how to incorporate this in my code? Thank you Sub GetTextFile() Const ForReading = 1, ForWriting = 2 Dim fso, FileIn, FileOut As Object, ArrFileTxt...

www.mrexcel.com

    If fileContent <> modifiedContent Then
        ' Create an instance of ADODB.Stream
        Set stream = CreateObject("ADODB.Stream")
        
        ' Specify the stream type (binary) and character set (UTF-8)
        stream.Type = 2 ' adTypeText
        stream.Charset = "utf-8"
        
        ' Open the stream and write the content
        stream.Open
        stream.WriteText modifiedContent
        
        ' -------------------------------------------------------------
        ' Domenic's code - to convert to NoBOM
        stream.Position = 3 'skip byte order mark
        Dim oStreamUTF8NoBOM As Object
        Set oStreamUTF8NoBOM = CreateObject("ADODB.Stream")
        With oStreamUTF8NoBOM
            .Type = 1 'adTypeBinary
            .Open
            modifiedContent.CopyTo oStreamUTF8NoBOM
        End With
        ' -------------------------------------------------------------
     
        ' Save the content to the file
        oStreamUTF8NoBOM.SaveToFile filePath, 2 ' adSaveCreateOverWrite
        
        ' Close the stream
        stream.Close
        oStreamUTF8NoBOM.Close
        
        ' Clean up
        Set stream = Nothing
        Set oStreamUTF8NoBOM = Nothing
        
        MsgBox "The file has been successfully modified."
    Else
        MsgBox "No modifications were necessary."
    End If

 

[Tanquen]

It's not liking this line. Error = Invalid Qualifier

modifiedContent.CopyTo oStreamUTF8NoBOM

The code runs if I change it to this but the file still has the BOM.

stream.CopyTo oStreamUTF8NoBOM

 

[Tanquen]

It looks like it has a BOM already but VBA breaks it.

The text "" or HEX "ef bb bf" is at the start of the file when viewed in Notpad++ HEX but why is the original not shown in Notepad? Then after any attempt to change the file with VBA the BOM is then part of the visible text in Notepad and it stops the original app from being able to read the file.

_______

So the original files encoding is UTF-8 with BOM and after any VBA edits it is still UTF-8 with BOM but then you see the BOM in Notepad text?

After VBA the file then starts with the text "" and Hex "ef bb bf c3 af c2 bb c2 bf".

VBA can't match the encoding type UTF-8 with BOM?

 

[Tanquen]

Not sure if it will fix all my issues but I was not using the ADODB.Stream option in the ingestion side.

So this:

' Open the file for reading
    fileNumber = FreeFile
    Open filePath For Input As #fileNumber
    fileContent = Input$(LOF(fileNumber), fileNumber)
    Close #fileNumber

Becomes this:

' Open the file using ADODB stream
    With stream
        .charset = "UTF-8"
        .Open
        .LoadFromFile filePath
        fileContent = .ReadText
        .Close
    End With