Displaying HTML in an Excel cell

Question

Hi,

I am trying to create a utility that exports data from a 3rd party application into a text file and then launches Excel and opens an .xlsm file that serves as a report template. I have VBA code behind the .xlsm file that opens the text file and reads data from it, placing each piece of data in its designated location in the template. It is working just how I'd like it to, except for the following:

One of the fields I am exporting is entered in the 3rd party application by users as shown below. The application has no facility for entering numbered lists, so this is entered as straight text...

... even though the user enters this a straight text, the application seems to automatically add HTML tags to it. When I export the data from this field, I get the following....

... When I copy this into a cell in Excel, Excel does not interpret the HTML tags, so the text is shown exactly as is (with HMTL tags visible).

Can anyone here tell me if there is a way I can make Excel interpret these HTML tags so that the data will be displayed in Excel just as it is in the application? Or will I have to write VBA code to remove these HTML tags and add conventional carriage returns and linefeeds (vbCrLF)

Any suggestions will be greatly appreciated.

Thanks in advance,
Paul

Answer 1

Try macro

Sub ConvertHTMLToText()

Dim htmlContent As String

Dim plainText As String

' HTML content to be converted

htmlContent = Range("J6").Value

' Remove HTML tags

plainText = Replace(htmlContent, "", vbCrLf)

plainText = Replace(plainText, "<", "<")

plainText = Replace(plainText, ">", ">")

plainText = Replace(plainText, "&", "&")

plainText = Replace(plainText, "", "")

plainText = Replace(plainText, "", "")

' Place the plain text in cell J6

Range("J6").Value = plainText

End Sub

Answer 2

Could you share a test file? Then I can have a try on it.

For sharing file, you may upload it to OneDrive or any other cloud drive and then post the link here.  *Please make sure you have removed any sensitive or private information in the sample file before uploading.

Answer 3

HI Snow Lu -

Thank you for your reply. I have attached a sample file on which I am facing the challenge I described. The HTML I exported from the 3rd party application is in celll J6.

Approval_For_Ticket_5405_11039-1.xlsx

I'd like it to be displayed in Excel just as it is in the 3rd party application (I included a screenshot in my original post).

I believe I have the following options:

1. Write some script in the export mechanism from the 3rd party application to strip the HTML tags before exporting
2. Write some VBA in Excel to strip the HTML tags before copying the content into J6
3. If it is possible, come up with a way in Excel that I can display this value correctly as-is (with the HTML tags)

I am hoping that if #3 is possible, it might be less labor intensive (require less code) than options #1 and #2. If you can help me determine if #3 is possible, I would really appreciate it.

Thanks again!

Paul

Answer 4

Thank you Snow ! That worked great for me !

I appreciate your help!

Best regards,

Paul