Solved How do I convert color from forecolor to html?

[zelarra821]

Hello.

I have prepared an example of what I want to achieve.

I have a long text field with rich formatting that, after updating, saves in some fields the color of the text and the background of the text that the user has given it. Well, I want to apply that color to a string that unites the name of the product with the note (adding the color of the product to this note), but when trying to generate the code in HTML I have no way to get the correct color.

Can someone shed some light for me?

Thanks.

 

[June7]

Need to convert decimal to hex. I have a table for a quick reference of colors I routinely use. Otherwise VBA custom function can convert between bases. https://www.excelforum.com/excel-fo...convert-a-nine-digit-number-to-base-32-a.html

Function BaseConvert(num, FromBase As Integer, _
ToBase As Integer, Optional DecPlace As Long) _
As String

'by Ron Rosenfeld

Dim LDI As Integer 'Leading Digit Index
Dim i As Integer, j As Integer
Dim Temp, Temp2
Dim Digits()
Dim r
Dim DecSep As String

DecSep = "."

On Error GoTo HANDLER

If FromBase > 62 Or ToBase > 62 Or FromBase < 2 Or ToBase < 2 Then
    BaseConvert = "Base out of range"
    Exit Function
End If

If InStr(1, num, "E") And FromBase = 10 Then
    num = CDec(num)
End If

'Convert to Base 10
LDI = InStr(1, num, DecSep) - 2
If LDI = -2 Then LDI = Len(num) - 1

j = LDI

Temp = Replace(num, DecSep, "")
For i = 1 To Len(Temp)
    Temp2 = Mid(Temp, i, 1)
    Select Case Temp2
        Case "A" To "Z"
            Temp2 = Asc(Temp2) - 55
        Case "a" To "z"
            Temp2 = Asc(Temp2) - 61
    End Select
    If Temp2 >= FromBase Then
        BaseConvert = "Invalid Digit"
        Exit Function
    End If
r = CDec(r + Temp2 * FromBase ^ j)
j = j - 1
Next i

If r <> 0 Then LDI = Fix(CDec(Log(r) / Log(ToBase)))
    If r < 1 Then LDI = 0

    ReDim Digits(LDI)

    For i = UBound(Digits) To 0 Step -1
        Digits(i) = Format(Fix(r / ToBase ^ i))
        r = CDbl(r - Digits(i) * ToBase ^ i)
        Select Case Digits(i)
            Case 10 To 35
                Digits(i) = Chr(Digits(i) + 55)
            Case 36 To 62
                Digits(i) = Chr(Digits(i) + 61)
        End Select
    Next i

    Temp = StrReverse(Join(Digits, "")) 'Integer portion
    ReDim Digits(DecPlace)
 
    If r <> 0 Then
        Digits(0) = DecSep
        For i = 1 To UBound(Digits)
            Digits(i) = Format(Fix(r / ToBase ^ -i))
            r = CDec(r - Digits(i) * ToBase ^ -i)
            Select Case Digits(i)
                Case 10 To 35
                    Digits(i) = Chr(Digits(i) + 55)
                Case 36 To 62
                    Digits(i) = Chr(Digits(i) + 61)
            End Select
        Next i
    End If

BaseConvert = Temp & Join(Digits, "")

Exit Function
HANDLER: MsgBox ("Error: " & Err.Number & " " & Err.Description & vbLf & _
"Number being converted: " & num)

End Function

The real trick will be locating and replacing codes in the HTML string.

 

[zelarra821]

Ok, thanks. And now the question is: "how do i use it?", cause I need

FromBase As Integer, _
ToBase As Integer

but I don't know what I have to write there.

 

[June7]

Copy function into a VBA general module and save.
Build query or call from another VBA procedure or from a textbox.

SELECT Table.*,
Replace(Replace([NameProductResult],[ForeColor],BaseConvert([ForeColor],10,16)),[BackColor],BaseConvert([BackColor],10,16)) AS HTML
FROM [Table];

 

[zelarra821]

In the end I solved it another way.

With the mid and instr functions I have searched for the characters that determine the color in html and I have extracted the string that represents the color of the text and the background, saving both values in the table. Since the color may not appear, I add a conditional so that it does not give an error. Then, to show the color, the only precaution is to enter an if in case there is no color, either text, background, or both, setting the value to auto.

Thank you so much.

All the best.

 

[June7]

Replace() would not need If condition. If the value is found it would be replaced, if not there, then nothing changes. So, once you have the color codes extracted to table, could use Replace() for the substitution.

 

[zelarra821]

I have not used the function that you indicated, because it has not worked for me.

If in HTML, for the color you have to write "color=#FFFF style", what I do is look for the position of = and style, and extract the color code with those two position values, which may give an error, which is why which I add the if. How could it be that there is not that color or this style, I have to put the if so that it takes the car.

I don't know if I explain myself.

 

[June7]

Did you mean #FFFFFF ?

Concatenating additional text in the substitution is simple.

Replace(Replace([NameProductResult],[ForeColor], "#" & BaseConvert([ForeColor],10,16)),[BackColor], "#" & BaseConvert([BackColor],10,16))

Review https://www.w3schools.com/html/html_colors.asp

 

[zelarra821]

I understand what you are saying, but I have tried every possible way, including the one you propose, and none of them worked for me.

For that reason, I had to look for another alternative, and it is the one I indicated before.

Thank you so much.

 

[June7]

So how do you find the HTML hex color code?

Want to share your final code?

 

[zelarra821]

I have not used the function that you indicated, because it has not worked for me.

If in HTML, for the color you have to write "color=#FFFF style", what I do is look for the position of = and style, and extract the color code with those two position values, which may give an error, which is why which I add the if. How could it be that there is not that color or this style, I have to put the if so that it takes the car.

I don't know if I explain myself.

 

[June7]

Yes, but you "look" where and how? In a query or a VBA procedure or a textbox expression? Where do you get the hex codes?

I am working with your db and the Replace function and BaseConvert functions do work to modify string. So why does that result not work for you? Where do you use this string that it fails? How is your string result different that it does work?

Now I see your concatenation code using PlainText() function. That's certainly another way to accomplish if you want to be limited to those specific HTML tags. Also, what if user applies more colors on different segments of text? These HTML strings can get fairly complex.

 

[zelarra821]

I do it in VBA. When I update the product name, I take the value of the entered text which appears to me as HTML. This string is what I search for the code using instr and mid, adding a previous if with instr in case the searched text does not exist: what I want to find is = and style on the one hand (color=), and : and "> on the other hand another (background-color. With instr I get the position, and with mid I get the color code taking loud cut points previously taken. Once you have the code, all that remains is to build the chain in HTML adding the color codes obtained .

 

[June7]

Appreciate you describing your code but pasting actual code into post would be most helpful to other readers.

 

[zelarra821]

Ok, I'll do it then, cause I'm busy now. Thanks

 

[zelarra821]

Done

 

[June7]

Now I am confused. Your original database showed decimal color codes in the string. Yet the RichText editor creates HEX code. And current db does not show any conversion taking place. So this conversion scenario was irrelevant? I am not understanding why you need to do this at all.

 

[zelarra821]

I don't know. The only thing I know that I don't get it work, but if I do in the way I show it works for me and that's so what I need