Solved

How to convert html to excel using vba

Posted on 2011-09-26
8
1,455 Views
Last Modified: 2012-05-12
How do I convert html to excel using vba?

Basically I have a string that contains html, and I would like to copy and paste it to another sheet as excel (in columns).

Here is an example html that i would like to convert.

<html>
      <head>
            <title>UtiliQuote - Use Your Power</title>
      </head>
      <body bgcolor="C0C0C0" leftmargin="0" topmargin="0">
            <table width="100%" border="0">
                        <tr>
                              <td align="center">4315592015</td>
                              <td align="center">account_OK</td>            
                              <td align="center">usage_KO</td>
                        </tr>
                        <tr>
                              <td align="center">NameOfClien</td>
                              <td align="center">Address</td>
                              <td align="center"></td>
                              <td align="center">IL</td>
                              <td align="center">60000</td>
                              <td align="center">1234567890</td>
                              <td align="center">12</td>
                              
                              <td align="center"></td>
                              <td align="center"></td>
                              <td align="center"></td>
                              <td align="center"></td>
                        </tr>
                        <tr>
                              <td align="center">0</td>
                              <td align="center">account_KO</td>            
                              <td align="center">usage_KO</td>
                        </tr>
                        <tr>
                              <td align="center">0</td>
                              <td align="center">account_KO</td>            
                              <td align="center">usage_KO</td>
                        </tr>
                        <tr>
                              <td align="center">0</td>
                              <td align="center">account_KO</td>            
                              <td align="center">usage_KO</td>
                        </tr>
                        <tr>
                              <td align="center">0</td>
                              <td align="center">account_KO</td>            
                              <td align="center">usage_KO</td>
                        </tr>
                        <tr>
                              <td align="center">0</td>
                              <td align="center">account_KO</td>            
                              <td align="center">usage_KO</td>
                        </tr>
                        <tr>
                              <td align="center">0</td>
                              <td align="center">account_KO</td>            
                              <td align="center">usage_KO</td>
                        </tr>
                        <tr>
                              <td align="center">0</td>
                              <td align="center">account_KO</td>            
                              <td align="center">usage_KO</td>
                        </tr>
                        <tr>
                              <td align="center">0</td>
                              <td align="center">account_KO</td>            
                              <td align="center">usage_KO</td>
                        </tr>
                        <tr>
                              <td align="center">0</td>
                              <td align="center">account_KO</td>            
                              <td align="center">usage_KO</td>
                        </tr>
            </table>
      </body>
</html>
0
Comment
Question by:consoleboy
  • 4
  • 3
8 Comments
 
LVL 7

Expert Comment

by:m4trix
ID: 36617359
It's doable, but not super easy.  What is the source of the html? If it is from online, is there a reason you can't or don't want to import it as a web query?
0
 

Author Comment

by:consoleboy
ID: 36620739
the web service is very flaky, so using web query is out of the question because it hangs excel 5 out of 10 times.

So I build a .net excel extension that returns the html as a string.
0
 
LVL 7

Expert Comment

by:m4trix
ID: 36622383
strange, I can't say I've ever found Excel's web query to be "flaky" myself.  Next question then, is the HTML always going to be in that format? If the format of the data is always similar then it's much easier to create something to parse it because it doesn't need to be generic
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

Author Comment

by:consoleboy
ID: 36630530
i meant to say that sometimes the web service itself is flaky.
It does not respond or returns error messages (even when sending the same request).

So to get around that we put together an excel c# extension that retrurns a string with the html.

The information returned from the html varies (number of rows), but the n umberare always the same.

0
 
LVL 26

Expert Comment

by:redmondb
ID: 36631610
consoleboy,

Couple of quick and (very) dirty possibilities...

(1) Output the string to a new html file. Open the html file in Excel. Copy and paste the data.

(2) Copy the string to the clipboard. Paste it to the required location. The "dirty" part of this is that Excel doesn't parse all the formatting (specifically the background colour).

Regards,
Brian.
0
 
LVL 7

Accepted Solution

by:
m4trix earned 500 total points
ID: 36642282
here's a really simple solution. It does not consider formatting at all, it simply takes an HTML table and spits it out into an excel sheet, row for row, column for column.

Sub readHTML(html)
    Dim xmlObj As New MSXML2.DOMDocument
    Dim tRow As MSXML2.IXMLDOMNode
    Dim tCell As MSXML2.IXMLDOMNode
    Dim row As Integer, col As Integer
    
    row = 1

    'Load the HTML into an XML object
    xmlObj.LoadXML html
    
    'Loop through all childnodes of the "table" tag (should all be table rows [<tr>])
    For Each tRow In xmlObj.getElementsByTagName("table").Item(0).ChildNodes
        If tRow.nodeName = "tr" Then
            col = 1
            ' Loop through each cell in the row, and output it to excel
            For Each tCell In tRow.ChildNodes
                If tCell.nodeName = "td" Or tCell.nodeName = "th" Then
                    Cells(row, col) = tCell.Text
                    col = col + 1
                End If
            Next tCell
            row = row + 1
        End If
    Next tRow
    
    Set tCell = Nothing
    Set tRow = Nothing
    Set xmlObj = Nothing
End Sub

Open in new window


You can see an example in the attached file. Node that the first sub ("testingSub") is just there so I could read the HTML into a single string to mimic what you would likely have. The first sheet shows the output when I run the macro with your sample html data

 Book1.xlsm
0
 
LVL 7

Expert Comment

by:m4trix
ID: 36642839
PS, you may need to add the "Microsoft XML" reference - I used v6.0, but you can select whatever the highest version you have
0
 

Author Closing Comment

by:consoleboy
ID: 36711387
Wonderful! Works beautifully.
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
VBA: insert new column and re-adapat string with lower letterS 4 31
Excel formula Sumif not working 4 30
VBA Help 18 47
Excel - DATEDIF error #NUM 6 26
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

832 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question