pass it on

Excel VBA: Cleaning up multiline HTML Text in a cell

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. 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:

https://stackoverflow.com/questions/9999713/html-text-with-tags-to-formatted-text-in-an-excel-cell

What was hard to find was a method to grab that de-HTMLed text from the clipboard. Turns out I would have to leverage “Microsoft Forms 2.0 Object Library” by adding it manually. stackoverflow.com to the rescue again:

https://stackoverflow.com/questions/9022245/get-text-from-clipboard-using-gettext-avoid-error-on-empty-clipboard

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.

Sub HTMLTextCleanup()

Dim Ie As Object
Dim DataObj As MSForms.DataObject

Set Ie = CreateObject("InternetExplorer.Application")
Set DataObj = New MSForms.DataObject

With Ie
    .Visible = False
    .Navigate "about:blank"
    .document.body.InnerHTML = ActiveCell.Value
        'Transform the text in the selected cell to remove the HTML
    .ExecWB 17, 0
        'Select contents of browser render
    .ExecWB 12, 2
        'Copy the contents of browser render in the clipboard
    Selection.ClearContents
        'Delete HTML text from the active cell
    ActiveCell.Select
    DataObj.GetFromClipboard
    ActiveCell.FormulaR1C1 = DataObj.GetText(1)
        'Grab the clipboard text and paste it into the cell
        'This makes sure that all the text is copied into one cell
        'even if paragraph breaks are present
    .Quit
End With
End Sub

Putting this out in the world in case it can help someone else do the same.

Excel Cut-n-Paste Slow

So, I was doing a cut-n-paste maybe 50 cells and it was taking forever. I did this multiple times in all sorts of documents and it always took forever. Though I am not certain what started the behavior, Googling it was proving less than useful until Chief93 over at superuser.com led me to the solution. It was the dang default printer. Somehow it got set to a printer that I no longer have setup on the system. My goodness, how aggravating! I reset the default to the current printer and, viola, cut-n-paste is working as expected again. Thanks Chief93, where ever you are…

Reference: https://superuser.com/questions/397032/excel-freezes-when-copying-cutting-to-paste-elsewhere

Excel VBA: Getting Rid of Merged Cells

I dislike merged cells in an Excel table. Any attempt to format and prettify Excel tables annoys me. It is a pet peeve. Give me a spreadsheet and I am going to expect to be able to be able to dig into data and manipulate it. A simple way to thwart my ability to do this is to add in a bunch of merged cell into your table to make it more “presentable”. I just had such a situation and found a post over at stackoverflow that presents a simple VBA solution that will remove the merge cells and duplicate any data that was in that merged cell into he newly liberated unmerged cells.

Take a gander: http://stackoverflow.com/questions/9215022/unmerging-excel-rows-and-duplicate-data

Quote of the day.

from back in the day…

“Art is innate in the artist, like an instinct that seizes and makes a tool out of the human being. The thing in the final analysis that wills something in him is not he, the personal man, but the aim of the art.” Carl Jung

Quote of the day.

Today’s quote comes from Mark Stephenson over at ‘The First Epistle of Mark’ called ‘Exposure and Contamination’:

I think many young Christians, many emergent, think they are strong enough to be in the world and not be contaminated by it. They think too highly of themselves. Our pride comes before our fall. We have rejected “churchianity” and “christianese” in favor of diving headlong into the world. And maybe this is where Jesus calls us to be. But many of us are not ready to be there. We are over-exposed to the world and under-exposed to the voice, presence and power of God. And so, right and left, we are being contaminated. And instead of spreading the gospel, we are infecting the next generation of disciples with eight parts world, one part God.

Do we understand how much we allow culture, absent of God, to influence us verses how much we allow God’s influence in our lives? It is a question I often ponder.

Quote of the day.

Jason Clark’s follow up entry to his post yesterday called “Cultural Neutering” has given me the quote of the day:

“Christians should not embrace a postmodern worldview; we must not adapt to postmodernity . . . but we do need to incarnate the timeless in the timely.�

I think Jason is quoting Duffy Robbins here, but I am not certain. Still, it sums up in a catchy little phrase the whole of my post “Creating a Relevant and Distinct Culture“ from yesterday.
This post, called “Failings of Cultural Responses” balances out some of the statements Jason made yesterday.

Quote of the day.

A friend of a friend (my sister) sort of connection has brought me to today’s quote of the day:

“until recently, i had a “good” job. a “real” job. meaning a job that was turning me into a serious asshole, a cheater, a manipulater of people for my own “successful” ends, and a man abandoning his own wife and son for these demands that kept popping up. because, “hey, a man’s gotta eat”. i now think these thoughts and several like them about “responsibility” and “success” are lies of oppression that we should heed no longer. and in not heeding them, you will become a fool to the world to trust in your god alone to be your provider and protector.” – Jon Perez, from his “1 corinthians chapter three” post.

Viva la Revolution! Makes one wonder what the true opiate of the masses is? Was Karl right or have the we found a better drug for society, the American dream?

Karl Marx