VBA

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

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.