-1

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>
Shekhar Gupta
  • 1
  • 1
  • Possible duplicate of [Export html table data to Excel using JavaScript / JQuery is not working properly in chrome browse](https://stackoverflow.com/questions/22317951/export-html-table-data-to-excel-using-javascript-jquery-is-not-working-properl) – HaveSpacesuit May 23 '17 at 17:03

2 Answers2

0

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.

M3ghana
  • 1,231
  • 1
  • 9
  • 19
  • Thanks for your answer but that's not I am looking for. I do not have one table to copy. Tables comes under continuous loop, so there are 100's of table. Your idea wont be good here. PS: Your Idea doesn't work anyways. – Shekhar Gupta Dec 17 '14 at 08:05
0

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

Rows

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

Example cells within a row

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

QHarr
  • 83,427
  • 12
  • 54
  • 101