{"id":215,"date":"2018-09-05T23:41:08","date_gmt":"2018-09-05T23:41:08","guid":{"rendered":"http:\/\/revivalprojects.com\/journal\/?p=215"},"modified":"2018-09-05T23:41:13","modified_gmt":"2018-09-05T23:41:13","slug":"excel-vba-cleaning-up-multiline-html-text-in-a-cell","status":"publish","type":"post","link":"http:\/\/revivalprojects.com\/journal\/2018\/09\/05\/excel-vba-cleaning-up-multiline-html-text-in-a-cell\/","title":{"rendered":"Excel VBA: Cleaning up multiline HTML Text in a cell"},"content":{"rendered":"\n<p>The interwebs really let me down today. I had this one simple thing I wanted to do with a database extract that needed some editing. Man on man, did it take much more work than anticipated.<\/p>\n\n\n\n<p>Some of the fields in the extract contain HTML formatting the cannot be used in an Excel spreadsheet report. Turns out I was able to find the method for cleaning out the HTML pretty easily. I found a stackoverflow.com conversation that helped with this:<\/p>\n\n\n\n<p><a href=\"https:\/\/stackoverflow.com\/questions\/9999713\/html-text-with-tags-to-formatted-text-in-an-excel-cell\">https:\/\/stackoverflow.com\/questions\/9999713\/html-text-with-tags-to-formatted-text-in-an-excel-cell<\/a><\/p>\n\n\n\n<p>What was hard to find was a method to grab that de-HTMLed text from the clipboard. Turns out I would have to leverage \u201cMicrosoft Forms 2.0 Object Library\u201d by adding it manually. stackoverflow.com to the rescue again:<\/p>\n\n\n\n<p><a href=\"https:\/\/stackoverflow.com\/questions\/9022245\/get-text-from-clipboard-using-gettext-avoid-error-on-empty-clipboard\">https:\/\/stackoverflow.com\/questions\/9022245\/get-text-from-clipboard-using-gettext-avoid-error-on-empty-clipboard<\/a><\/p>\n\n\n\n<p>Ended up with the following macro which will clean up HMTL tags in the text of a selected cell and insert the cleaned up text into that same cell.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>Sub HTMLTextCleanup()\n\nDim Ie As Object\nDim DataObj As MSForms.DataObject\n\nSet Ie = CreateObject(\"InternetExplorer.Application\")\nSet DataObj = New MSForms.DataObject\n\nWith Ie\n    .Visible = False\n    .Navigate \"about:blank\"\n    .document.body.InnerHTML = ActiveCell.Value\n        'Transform the text in the selected cell to remove the HTML\n    .ExecWB 17, 0\n        'Select contents of browser render\n    .ExecWB 12, 2\n        'Copy the contents of browser render in the clipboard\n    Selection.ClearContents\n        'Delete HTML text from the active cell\n    ActiveCell.Select\n    DataObj.GetFromClipboard\n    ActiveCell.FormulaR1C1 = DataObj.GetText(1)\n        'Grab the clipboard text and paste it into the cell\n        'This makes sure that all the text is copied into one cell\n        'even if paragraph breaks are present\n    .Quit\nEnd With\nEnd Sub<\/code><\/pre>\n\n\n\n<p>Putting this out in the world in case it can help someone else do the same.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>The interwebs really let me down today. I had this one simple thing I wanted to do with a database extract that needed some editing. Man on man, did it take much more work than anticipated. Some of the fields in the extract contain HTML formatting the cannot be used in an Excel spreadsheet report. [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_exactmetrics_skip_tracking":false,"_exactmetrics_sitenote_active":false,"_exactmetrics_sitenote_note":"","_exactmetrics_sitenote_category":0,"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","enabled":false},"version":2}},"categories":[3,5,9],"tags":[],"class_list":["post-215","post","type-post","status-publish","format-standard","hentry","category-geeking","category-pass-it-on","category-vba"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p4pNDf-3t","jetpack_likes_enabled":true,"_links":{"self":[{"href":"http:\/\/revivalprojects.com\/journal\/wp-json\/wp\/v2\/posts\/215","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/revivalprojects.com\/journal\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/revivalprojects.com\/journal\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/revivalprojects.com\/journal\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/revivalprojects.com\/journal\/wp-json\/wp\/v2\/comments?post=215"}],"version-history":[{"count":1,"href":"http:\/\/revivalprojects.com\/journal\/wp-json\/wp\/v2\/posts\/215\/revisions"}],"predecessor-version":[{"id":218,"href":"http:\/\/revivalprojects.com\/journal\/wp-json\/wp\/v2\/posts\/215\/revisions\/218"}],"wp:attachment":[{"href":"http:\/\/revivalprojects.com\/journal\/wp-json\/wp\/v2\/media?parent=215"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/revivalprojects.com\/journal\/wp-json\/wp\/v2\/categories?post=215"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/revivalprojects.com\/journal\/wp-json\/wp\/v2\/tags?post=215"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}