1

Using VBA, I export an Excel spreadsheet as a web page.

The source code of the exported html file is quite messed up. There are multiple line breaks in between. For eg:

 <td colspan="3" class="xl681522" style="height:17.1pt" height="22">Select X
  and Y</td>
<td class="xl731522" style="width:253pt" width="337">Hello World
 and my country</td>

How can I tidy up the html source code, like:

<td colspan="3" class="xl681522" style="height:17.1pt" height="22">Select X and Y</td>
<td class="xl731522" style="width:253pt" width="337">Hello World and my country</td>
Community
  • 1
  • 1
user1492667
  • 139
  • 3
  • 13
  • 1
    I don't think it already exists but you may have a look at : http://stackoverflow.com/questions/5327512/convert-html-to-plain-text-in-vba and http://www.vbaexpress.com/forum/showthread.php?t=31831 – JMax Dec 05 '12 at 08:54

1 Answers1

0

This might help or simply CTRL + H then search <*> and replace with " " (blank) :

'PURPOSE: Find & Replace a list of text/values throughout entire workbook from a table
'SOURCE: www.TheSpreadsheetGuru.com/the-code-vault, I used <p*> to <p> in the table
Dim sht As Worksheet
Dim fndList As Integer
Dim rplcList As Integer
Dim tbl As ListObject
Dim myArray As Variant

'Create variable to point to your table
  Set tbl = Worksheets("Sheet1").ListObjects("Table1")

'Create an Array out of the Table's Data
  Set TempArray = tbl.DataBodyRange
  myArray = Application.Transpose(TempArray)

'Designate Columns for Find/Replace data
  fndList = 1
  rplcList = 2

'Loop through each item in Array lists
  For x = LBound(myArray, 1) To UBound(myArray, 2)
    'Loop through each worksheet in ActiveWorkbook (skip sheet with table in it)
      For Each sht In ActiveWorkbook.Worksheets
        If sht.Name <> tbl.Parent.Name Then

          sht.Cells.Replace What:=myArray(fndList, x), Replacement:=myArray(rplcList, x), _
            LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
            SearchFormat:=False, ReplaceFormat:=False

        End If
      Next sht
  Next x

End Sub
Hyperion
  • 33
  • 6
  • Welcome on Stack Overflow, while this may answer the question, it is better to give explanations and not only code for your answer. – gogaz Jun 01 '20 at 10:39