Parse HTML code and save formatted text to Excel cell

I created a TFS hierarchical style report to show 

  • User Stories
    • Test Cases
The report works well, however it lists the Test Step in HTML format. 

When I export the report to Excel through VS, the HTML is not displayed in user friendly format. 

I started to google around for solutions, and these are the steps:
  1. Select the HTML and send to clipboard
  2. Paste back to Excel with "Paste Special" to preserve the format
  3. When paste multiple lines of text, excel puts them in multiple rows, and the vba collects all the rows and puts them back to one row
These are the protocol code:

Private Sub Convert_Click()
Dim rng As Range
Set rng = ActiveSheet.Range("G3")
Worksheet_Change rng, ActiveSheetEnd Sub

Private Sub Worksheet_Change(ByVal Target As Range, ByVal sht As Worksheet)

Dim objData As DataObject ' Set a reference to MS Forms 2.0
Dim sHTML As String
Dim sSelAdd As String
Dim i As Long
Dim txt As String
Application.EnableEvents = False

If Target.Cells.Count = 1 Then

Set objData = New DataObjectsHTML = "<html><DIV>Test Steps</DIV><DIV>Test Results</DIV>" & Target.Text & "</html>"
objData.SetText sHTMLobjData.PutInClipboardsht.PasteSpecial Format:="Unicode Text"
For i = 1 To Selection.Cells.Count
txt = txt & Selection(i) & Chr(10)
Next
Selection.ClearContents Selection(1) = txt
End If

Application.EnableEvents = True

End Sub

Comments

Post a Comment

Popular posts from this blog

Project Professional 2013 Error 12015

Image
Got an error 12015 when opening Project Professional 2013.  Clicking the More info and it shows this: <detail><ServerExecutionFault xmlns="http://Microsoft.Office.Project.Interfaces/" xmlns:i="http://www.w3.org/2001/XMLSchema-instance"><Actor i:nil="true"/><LastError>12015</LastError><Message/><Source/><StackTrace/><TargetSite i:nil="true"/><ExceptionDetails><errinfo xmlns=""><general><class name="ActiveCacheUnsupportedProjectProfessionalVersion"><error id="12015" name="ActiveCacheUnsupportedProjectProfessionalVersion" uid="315f3c6b-46fa-e411-80d9-00155d106504" version="15.0.4569.1504"/></class></general></errinfo></ExceptionDetails></ServerExecutionFault></detail> and the second error message is: According technet ( http://blogs.technet.com/b/p...
Read more

SP optimization with TVF, View

Had problem with a SP, SP has a few parameters SP unions three queries and then filter by a parameter and then return data  One of the three queries links a TVF with a few tables The TVF links a standard view with a few tables The View links several tables, however not all tables are used by the TVF Symptom: One table used by the TVF has many scan count and slows down the SP Optimization options: Bring the parameter to the three queries referenced by the SP - no effect Modify the TVF and pass in the SP parameter - Speed up 4x Replace the view with the base table and remove unused tables - Speed up 4x
Read more

SSRS issue on SQL Server 2012 SP3 -The version of the report server database is either in a format that is not valid, or it cannot be read

When windows update pushed SQL Server 2012 SP3 to the database server on the weekend, we received an error on SSRS: "The version of the report server database is either in a format that is not valid, or it cannot be read." I googled around and found this post on TechNet: https://social.technet.microsoft.com/wiki/contents/articles/32360.sspr-troubleshooting-the-version-of-the-report-server-database-is-either-in-a-format-that-is-not-valid-or-it-cannot-be-read.aspx I was surprised that a major SP would contain such an issue as most IT shops won't bother with the many CUs and choose to stay with SPs. The fix is upgrade to SP3 CU6 for us. Microsoft recommends to go with the latest CU, which is CU9. List of SQL Server Versions for reference:  http://sqlserverbuilds.blogspot.ca/
Read more