Copy table data from web page to excel sheet

Question

I am using excel macros to copy calculation result data from a online intranet web calculation website.

For that calculation I have to add many data to webpage. I used macros to automate data entry to webpage and finally I can reach to result page. The calculation result is in tabular form. Now I want to copy that result data to excel sheet in tabular form. I tried web query but that doesn't work as result web page cannot be loaded directly by just address.

Following is the source of html table I want to extract.

 
      <P>
        <BR>
        <TABLE width="80%">
          <TR>
            <TD bgcolor="#ffffff" align="center"><b>Life Calculation Result</b>
            </TD>
          </TR>
        </TABLE>
        <P>
          <TABLE cellspacing="1" cellpadding="0">
            <TR align="center">
              <TH colspan="6"></TH>
              <TH colspan="3" align="center">--- Inboard ---</TH>
              <TH></TH>
              <TH colspan="3" align="center">--- outboard ---</TH>
              <TH></TH>
              <TH colspan="3" align="center">--- System ---</TH>
            </TR>
            <TR align="center">
              <TH>No.</TH>
              <TH width="10"></TH>
              <TH>Displacement</TH>
              <TH width="10"></TH>
              <TH>Preload</TH>
              <TH width="10"></TH>
              <TH>Running
                <BR>Distance</TH>
              <TH width="10"></TH>
              <TH>Total
                <BR>Number of
                <BR>Revolutions</TH>
              <TH width="10"></TH>
              <TH>Running
                <BR>Distance</TH>
              <TH width="10"></TH>
              <TH>Total
                <BR>Number of
                <BR>Revolutions</TH>
              <TH width="10"></TH>
              <TH>Running
                <BR>Distance</TH>
              <TH width="10"></TH>
              <TH>Total
                <BR>Number of
                <BR>Revolutions</TH>
            </TR>
            <TR align="center">
              <TH></TH>
              <TH></TH>
              <TH>[mm]</TH>
              <TH></TH>
              <TH>[N]</TH>
              <TH></TH>
              <TH>[10<sup><font size="-1">4<font></sup>km]</TH>
              <TH></TH>
              <TH>[10<sup><font size="-1">6<font></sup>rev.]</TH>
              <TH></TH>
              <TH>[10<sup><font size="-1">4<font></sup>km]</TH>
              <TH></TH>
              <TH>[10<sup><font size="-1">6<font></sup>rev.]</TH>
              <TH></TH>
              <TH>[10<sup><font size="-1">4<font></sup>km]</TH>
              <TH></TH>
              <TH>[10<sup><font size="-1">6<font></sup>rev.]</TH>
            </TR>
            <TR align="right">
              <TH>(1)</TH>
              <TD></TD>
              <TD>0.594874</TD>
              <TD></TD>
              <TD></TD>
              <TD></TD>
              <TD>3.492e+004</TD>
              <TD></TD>
              <TD>1.705e+005</TD>
              <TD></TD>
              <TD>9.796e+004</TD>
              <TD></TD>
              <TD>4.782e+005</TD>
              <TD></TD>
              <TD>2.710e+004</TD>
              <TD></TD>
              <TD>1.323e+005</TD>
            </TR>
            <TR align="right">
              <TH>(2)</TH>
              <TD></TD>
              <TD>0.604874</TD>
              <TD></TD>
              <TD></TD>
              <TD></TD>
              <TD>4.001e+004</TD>
              <TD></TD>
              <TD>1.953e+005</TD>
              <TD></TD>
              <TD>1.033e+005</TD>
              <TD></TD>
              <TD>5.045e+005</TD>
              <TD></TD>
              <TD>3.042e+004</TD>
              <TD></TD>
              <TD>1.485e+005</TD>
            </TR>
            <TR align="right">
              <TH>(3)</TH>
              <TD></TD>
              <TD>0.614874</TD>
              <TD></TD>
              <TD>0.00</TD>
              <TD></TD>
              <TD>4.751e+004</TD>
              <TD></TD>
              <TD>2.320e+005</TD>
              <TD></TD>
              <TD>1.088e+005</TD>
              <TD></TD>
              <TD>5.314e+005</TD>
              <TD></TD>
              <TD>3.495e+004</TD>
              <TD></TD>
              <TD>1.706e+005</TD>
            </TR>
            <TR align="right">
              <TH>(4)</TH>
              <TD></TD>
              <TD>0.630867</TD>
              <TD></TD>
              <TD>1000.00</TD>
              <TD></TD>
              <TD>3.003e+004</TD>
              <TD></TD>
              <TD>1.466e+005</TD>
              <TD></TD>
              <TD>3.941e+004</TD>
              <TD></TD>
              <TD>1.924e+005</TD>
              <TD></TD>
              <TD>1.813e+004</TD>
              <TD></TD>
              <TD>8.854e+004</TD>
            </TR>
            <TR align="right">
              <TH>(5)</TH>
              <TD></TD>
              <TD>0.639982</TD>
              <TD></TD>
              <TD>2000.00</TD>
              <TD></TD>
              <TD>7.425e+003</TD>
              <TD></TD>
              <TD>3.625e+004</TD>
              <TD></TD>
              <TD>7.893e+003</TD>
              <TD></TD>
              <TD>3.853e+004</TD>
              <TD></TD>
              <TD>4.075e+003</TD>
              <TD></TD>
              <TD>1.989e+004</TD>
            </TR>
            <TR align="right">
              <TH>(6)</TH>
              <TD></TD>
              <TD>0.647481</TD>
              <TD></TD>
              <TD>3000.00</TD>
              <TD></TD>
              <TD>2.523e+003</TD>
              <TD></TD>
              <TD>1.232e+004</TD>
              <TD></TD>
              <TD>2.592e+003</TD>
              <TD></TD>
              <TD>1.266e+004</TD>
              <TD></TD>
              <TD>1.362e+003</TD>
              <TD></TD>
              <TD>6.648e+003</TD>
            </TR>
            <TR align="right">
              <TH>(7)</TH>
              <TD></TD>
              <TD>0.654070</TD>
              <TD></TD>
              <TD>4000.00</TD>
              <TD></TD>
              <TD>1.131e+003</TD>
              <TD></TD>
              <TD>5.521e+003</TD>
              <TD></TD>
              <TD>1.148e+003</TD>
              <TD></TD>
              <TD>5.605e+003</TD>
              <TD></TD>
              <TD>6.068e+002</TD>
              <TD></TD>
              <TD>2.962e+003</TD>
            </TR>
            <TR align="right">
              <TH>(8)</TH>
              <TD></TD>
              <TD>0.660043</TD>
              <TD></TD>
              <TD>5000.00</TD>
              <TD></TD>
              <TD>6.007e+002</TD>
              <TD></TD>
              <TD>2.933e+003</TD>
              <TD></TD>
              <TD>6.065e+002</TD>
              <TD></TD>
              <TD>2.961e+003</TD>
              <TD></TD>
              <TD>3.214e+002</TD>
              <TD></TD>
              <TD>1.569e+003</TD>
            </TR>
            <TR align="right">
              <TH>(9)</TH>
              <TD></TD>
              <TD>0.665559</TD>
              <TD></TD>
              <TD>6000.00</TD>
              <TD></TD>
              <TD>3.570e+002</TD>
              <TD></TD>
              <TD>1.743e+003</TD>
              <TD></TD>
              <TD>3.593e+002</TD>
              <TD></TD>
              <TD>1.754e+003</TD>
              <TD></TD>
              <TD>1.907e+002</TD>
              <TD></TD>
              <TD>9.311e+002</TD>
            </TR>
            <TR align="right">
              <TH>(10)</TH>
              <TD></TD>
              <TD>0.670717</TD>
              <TD></TD>
              <TD>7000.00</TD>
              <TD></TD>
              <TD>2.296e+002</TD>
              <TD></TD>
              <TD>1.121e+003</TD>
              <TD></TD>
              <TD>2.307e+002</TD>
              <TD></TD>
              <TD>1.126e+003</TD>
              <TD></TD>
              <TD>1.226e+002</TD>
              <TD></TD>
              <TD>5.983e+002</TD>
            </TR>
            <TR align="right">
              <TH>(11)</TH>
              <TD></TD>
              <TD>0.675584</TD>
              <TD></TD>
              <TD>8000.00</TD>
              <TD></TD>
              <TD>1.566e+002</TD>
              <TD></TD>
              <TD>7.646e+002</TD>
              <TD></TD>
              <TD>1.571e+002</TD>
              <TD></TD>
              <TD>7.671e+002</TD>
              <TD></TD>
              <TD>8.354e+001</TD>
              <TD></TD>
              <TD>4.078e+002</TD>
            </TR>
            <TR align="right">
              <TH>(12)</TH>
              <TD></TD>
              <TD>0.680208</TD>
              <TD></TD>
              <TD>9000.00</TD>
              <TD></TD>
              <TD>1.117e+002</TD>
              <TD></TD>
              <TD>5.455e+002</TD>
              <TD></TD>
              <TD>1.120e+002</TD>
              <TD></TD>
              <TD>5.469e+002</TD>
              <TD></TD>
              <TD>5.958e+001</TD>
              <TD></TD>
              <TD>2.909e+002</TD>
            </TR>
            <TR align="right">
              <TH>(13)</TH>
              <TD></TD>
              <TD>0.684622</TD>
              <TD></TD>
              <TD>10000.00</TD>
              <TD></TD>
              <TD>8.262e+001</TD>
              <TD></TD>
              <TD>4.034e+002</TD>
              <TD></TD>
              <TD>8.278e+001</TD>
              <TD></TD>
              <TD>4.042e+002</TD>
              <TD></TD>
              <TD>4.404e+001</TD>
              <TD></TD>
              <TD>2.150e+002</TD>
            </TR>
          </TABLE>

Answer 1

Copy your code as it is from the

<table> ... </table>

tag.

Then switch to Excel, pick a cell and press Command-v or use Edit > Paste. Excel knows HTML tables and will format the table just right.

Answer 2

Without an URL it is guesswork.

You can get all the TABLE tag elements with, say using internet explorer browser to scrape,

IE.document.getElementsByTagName("TABLE")

You then select a particular table by index; Let's say index 0 i.e. the first table in the collection.

IE.document.getElementsByTagName("TABLE")(0)

If you have added references (VBE > Tools > References > Microsoft Internet Controls and HTML Object Library) you can put this table into an HTMLTable object variable:

Dim nTable As HTMLTable
Set nTable = IE.document.getElementByTagName("TABLE")(0)

You can then select tr tags in the table, to get the table rows

nTable.getElementsByTagName("tr")

and within each row you can loop its cells either via td tags or along the row length:

nTable.getElementsByTagName("tr")(i).getElementsByTagName("td")

Where i is the current index of the loop over the rows collections from the step before.

When looping the table cells, which are the inner loop items obtained by the td tags, you can then access their .innerText property to get the values.

Examples of what are contained in the rows of the outer loop (tr tags):

Examples of what is within individual rows (td tags) in the table cells:

So, when you outer loop the rows, and then inner loop the cells, you will get the values shown in the image directly above.

Example of where this is done in full, bearing in mind according to each webpage you have to tweak the loop somewhat: https://stackoverflow.com/a/50888649/6241235