none
Text to HTML with UFT-8 Encoding RRS feed

  • Question

  • Hi,

    The requirement is to convert all the text fields in excel to HTML with UFT - 8 encoding..I have used the below code but, this code does not have UTF-8 encoding...

    Function fnConvert2HTML(myCell As Range) As String
        Dim bldTagOn, itlTagOn, ulnTagOn, colTagOn As Boolean
        Dim i, chrCount As Integer
        Dim chrCol, chrLastCol, htmlTxt As String
        
        bldTagOn = False
        itlTagOn = False
        ulnTagOn = False
        colTagOn = False
        chrCol = "NONE"
        htmlTxt = ""
        chrCount = myCell.Characters.Count
        
        For i = 1 To chrCount
            With myCell.Characters(i, 1)
                If (.Font.Color) Then
                    chrCol = fnGetCol(.Font.Color)
                    If Not colTagOn Then
                        htmlTxt = htmlTxt & ""
                        colTagOn = True
                    Else
                        If chrCol <> chrLastCol Then htmlTxt = htmlTxt & ""
                    End If
                Else
                    chrCol = "NONE"
                    If colTagOn Then
                        htmlTxt = htmlTxt & ""
                        colTagOn = False
                    End If
                End If
                chrLastCol = chrCol
                
                If .Font.Bold = True Then
                    If Not bldTagOn Then
                        htmlTxt = htmlTxt & ""
                        bldTagOn = True
                    End If
                Else
                    If bldTagOn Then
                        htmlTxt = htmlTxt & ""
                        bldTagOn = False
                    End If
                End If
        
                If .Font.Italic = True Then
                    If Not itlTagOn Then
                        htmlTxt = htmlTxt & ""
                        itlTagOn = True
                    End If
                Else
                    If itlTagOn Then
                        htmlTxt = htmlTxt & ""
                        itlTagOn = False
                    End If
                End If
        
                If .Font.Underline > 0 Then
                    If Not ulnTagOn Then
                        htmlTxt = htmlTxt & ""
                        ulnTagOn = True
                    End If
                Else
                    If ulnTagOn Then
                        htmlTxt = htmlTxt & ""
                        ulnTagOn = False
                    End If
                End If
                
                If (Asc(.Text) = 10) Then
                    htmlTxt = htmlTxt & "
    "
                Else
                    htmlTxt = htmlTxt & .Text
                End If
            End With
        Next
        
        If colTagOn Then
            htmlTxt = htmlTxt & ""
            colTagOn = False
        End If
        If bldTagOn Then
            htmlTxt = htmlTxt & ""
            bldTagOn = False
        End If
        If itlTagOn Then
            htmlTxt = htmlTxt & ""
            itlTagOn = False
        End If
        If ulnTagOn Then
            htmlTxt = htmlTxt & ""
            ulnTagOn = False
        End If
        htmlTxt = htmlTxt & ""
        'fnConvert2HTML = htmlTxt
    End Function
    Function fnGetCol(strCol As String) As String   
        Dim rVal, gVal, bVal As String
        strCol = Right("000000" & Hex(strCol), 6)
        bVal = Left(strCol, 2)
        gVal = Mid(strCol, 3, 2)
        rVal = Right(strCol, 2)
        fnGetCol = rVal & gVal & bVal
    End Function

    Please can anybody help on this....

    Tuesday, April 7, 2015 11:31 AM

All replies

  • I don't understand your code.  It seems you should be creating CSS and class statements to contain formatting info.  Anyway,  I found this on the web:

    Dim fsT As Object
    Set fsT = CreateObject("ADODB.Stream")
    fsT.Type = 2 'Specify stream type - we want To save text/string data.
    fsT.Charset = "utf-8" 'Specify charset For the source text data.
    fsT.Open 'Open the stream And write binary data To the object
    fsT.WriteText "special characters: äöüß"
    fsT.SaveToFile sFileName, 2 'Save binary data To disk

    Tuesday, April 7, 2015 12:20 PM
  • UTF is not MS Outlook format. You can find it in Mozilla Tunderbird for ex..

    In this side ypu can find little translator for contacts in UTF to Win

    Zamień kodowanie wizytówki z Tunderbird dla Outlooka

    Also you can use ext control for transform in Developer.


    Oskar Shon, Office System MVP - www.VBATools.pl
    if Helpful; Answer when a problem solved

    Sunday, April 12, 2015 10:15 PM
    Answerer
  • Hi,

    Thanks for your response....Please can you help me set the font type and the font size (as Arial "18" )for the converted HTML data in the above code mentioned by me?

    Monday, April 13, 2015 5:59 AM