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

Why can’t I seem to get rid of this blog?

I mean, i don’t do anything with it so why have it? yet when it comes time to renew the domains, i do it. When there is an update to WordPress, i apply it. And now, i have transferred the whole thing so that WordPress is managed within my provider’s cpanel.

What kind of sickness is this that i have, writing out a post no one will read?

Excel VBA: Copy a Table Column Unique Data to Another Table Column

I have updated the functionality of my earlier post to only copy over unique values, then I added a sort on the values copied over.


Option Compare Text

'Copy a column from one Table to another
Sub copyUnique()

'Delete current values prior to paste if values exist
If Application.WorksheetFunction.CountA(Range("Table2[[Column1]]")) <> 0 _
Then Range("Table2[[Column1]]").Delete

'Advanced filter for unique entries only
Range("Table1[[#All],[Column3]]").AdvancedFilter Action:=xlFilterInPlace, _
Unique:=True

'Select desired column to copy with destination option
Range("Table1[[Column3]]").Copy _
Destination:=Range("Table2[[Column1]]")

'Remove Filter After Copy
Range("Table1[[#Headers],[Column3]]").Select
ActiveSheet.ShowAllData

'Sort a table column A to Z
Worksheets("Sheet2").ListObjects("Column1").Sort.SortFields.Clear

Worksheets("Sheet2").ListObjects("Column1").Sort.SortFields.Add _
Key:=Range("Table2[[Column1]]"), SortOn:=xlSortOnValues, _
Order:=xlAscending, DataOption:=xlSortNormal

With Worksheets("Sheet2").ListObjects("Column1").Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

End Sub

I am not certain if this is the best way to do it but the code is snappy and works for my needs.

Excel VBA: Copy a Table Column Data to Another Table Column

Update: Added missing ‘Sub End’

On Excel 2010, I created this VBA to copy data from a specific column on a specific table to another table on another spreadsheet to editing the values later without disturbing the original source data.

I am posting it here since I had to do multiple searches for the syntax.

    Sub copyColumn()
    'Delete current values prior to paste if values exist
    If Application.WorksheetFunction.CountA(Range("Table2[Column1]")) <> 0 _
    Then Range("Table2[Column1]").Delete

    'Select desired column to copy with destination option
    Range("Table1[[Column3]]").Copy _
    Destination:=Range("Table2[[Column1]]")
    Sub End

If you find this snippet of code useful, leave a comment.

Posting from my Motorola Droid.

I found this little app in the market yesterday. I don ‘t need it obviuosly, given how often I post. Hmmm… obviuosly there is no spell check in this app, that may be problematic. 🙂

One mo’gain: The road to fiscal responsibility.

Here I am again, trying to implement the annual new year’s resolution of living within a budget. Every year I fail miserably but every year I get back up on that horse and try again.

Last year I purchased “You Need a Budget” (YNAB) and was very ambitious about entering all the data I could for as far back as I could and and drowned under the weight of the volume of the numbers. I could never get caught up to current and so gave up. This year I upgraded to the latest version of YNAB and I am back at it, but now I am only going back to the first of the year so that I have a hope of getting caught up.

We have way more outgoings this year with 2 car payments and a wee little one that we feel compelled to keep surrounded by battery operated plastic toys. Even with that I am hopeful that this is the year we get it under control. Wish us luck, pray for us; we need all the help we can get!

Relationships require commitment…

I think I forget that . Maybe I just assume they will maintain themselves by virtue of past shared experiences but time has proven that friendships fade when not tended to. And to call someone a friend and to not invest that time, what does that communicate to that person you call ‘friend’ when you don’t invest the time?

To my old friends who haven’t heard from me in a while, so, a long while, know I still love you and I will try and do better.