Pull tabular data from multiple web pages into workbooks

Question

I am trying to write a macro in VBA that will pull data tables a number of web pages into excel. A new workbook would be created for each web page/table.

There is one table for each web page, and each page has the same layout. The url for each page has the same format, with a numerical identifier at the end of the url.

Unfortunately the site itself is password protected.

So far I have been able to record a macro where I create a web query for a single page/table. At this point I am having trouble figuring out how to make the process repeat for a large number of pages, and how to write the macro in such a way that it creates a new workbook for each page. It would be great if the user could input a list of identifiers (the number at the end of the url), run the macro, and have it produce a separate file for each page.

Sub productiondata()
'
' productiondata Macro
' pulls production table from dmr site for well number 19918
    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;https://www.dmr.nd.gov/oilgas/feeservices/getwellprod.asp?filenumber=19918" _
        , Destination:=Range("$A$1"))
        .Name = "getwellprod.asp?filenumber=19918"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlSpecifiedTables
        .WebFormatting = xlWebFormattingNone
        .WebTables = "3"
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
End Sub

Thank you in advance for any guidance you can provide!

Update (6/24/14):

I've managed to make some changes to the code so that it will now create a new workbook and import the data into that new workbook:

Sub productiondata()
'create new workbook named after the value in cell A1
Dim Aname As String
Aname = ActiveWorkbook.Sheets(1).Range("A1").Value
Workbooks.Add
ActiveWorkbook.SaveAs Filename:=Aname & ".xls"
' productiondata Macro
' pulls production table from dmr site for well number 25158
    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;https://www.dmr.nd.gov/oilgas/feeservices/getwellprod.asp?filenumber=25158" _
        , Destination:=Range("$A$1"))
        .Name = "getwellprod.asp?filenumber=25158"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlSpecifiedTables
        .WebFormatting = xlWebFormattingNone
        .WebTables = "3"
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
End Sub

I am now trying to figure out how I can change the web query so that it will append the number in Cell A1 to the url.

Update (6/24/2014):

Here is the source code for an example of one of the password protected pages (https://www.dmr.nd.gov/oilgas/feeservices/getwellprod.asp?filenumber=22809) I am trying to pull a table from:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
    "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html lang="en">

<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<meta name="GENERATOR" content="Microsoft FrontPage 4.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<title>Retrieve Well Production Data</title>
<link rel="stylesheet" type="text/css" href="/css/stateBanner.css">
<link rel="stylesheet" type="text/css" href="/css/oilGasDefaults.css">
<link rel="stylesheet" type="text/css" href="/css/jqueryOGDUI/css/custom-theme/jquery-ui-1.9.1.custom.css" >
<style type="text/css">
    .menuBar {
        width: 155px;
        font: 10px verdana,sans-serif;
    }
    .menuBtn {
        width: 100%;
    }
    .menuHeader {
        background-color: #FFFF00;
        padding: 1px;
        margin: 1px; 
        font-size: larger; 
        font-weight: bold;
        text-align: center;
    }
</style>
<script language="javascript" type="text/javascript" src="/includes/oilandgas.js"></script>
<script language="javascript" type="text/javascript" src="/includes/left-nav.js"></script>
<script language="javascript" type="text/javascript" src="/css/jqueryOGDUI/js/jquery-1.8.2.js"></script>
<script language="javascript" type="text/javascript" src="/css/jqueryOGDUI/js/jquery-ui-1.9.1.custom.js"></script>
<script>
$(function() {
    $( "#premiumServiceBtn" ).button().click(function() { location.href='../subscriptionservice.asp'; });
    $( "#scoutTicketBtn" ).button().click(function() { location.href='getscoutticket.asp'; });
    $( "#digImgLogsBtn" ).button().click(function() { location.href='getlogs.asp'; });
    $( "#fieldProdBtn" ).button().click(function() { location.href='fieldprodinj.asp'; });
    $( "#launchIMSWellBBtn" ).button().click(function() { location.href='javascript:launchIMSSubWell("22809");'; });    $( "#getWellFileBBtn" ).button().click(function() { location.href='/oilgas/FeeServices/wfiles/22/W22809.pdf'; });
});
</script>
</head>

<body onload="document.forms[0].elements[0].focus(); document.forms[0].elements[0].select()">
<div style="margin:0; padding:0; width:100%; background:#fff; height:26px; border-bottom:1px solid #ccc"><div style="float:left"><span style="display:none">[</span><a href="http://www.nd.gov/"><img style="border-width:0" src="/images/ndgov-banner.gif" height="25" alt="nd.gov - The Official Portal for North Dakota State Government" /></a><span style="display:none">]</span></div><div style="float:right"><span style="display:none">[</span><a href="http://www.ndtourism.com/"><img style="border-width:0" src="/images/tourism-banner-blue.gif" width="95" height="25" alt="North Dakota: Legendary. Follow the trail of legends" /></a><span style="display:none">]</span></div></div>
<table border="0" cellpadding="0" cellspacing="0" width="100%" summary="NDIC Get Well Production Header Table" height="0%">
<tr>
<td width="0%" valign="top" align="left" height="0%">
    <div class="menuBar">
        <div class="menuHeader">Related Links</div>
        <button id="premiumServiceBtn" class="menuBtn">Premium Services</button>
        <button id="scoutTicketBtn" class="menuBtn">Scout Ticket Data</button>
        <button id="digImgLogsBtn" class="menuBtn">Digital & Image Logs</button>
        <button id="fieldProdBtn" class="menuBtn">Field Production</button>
<button id="launchIMSWellBBtn" class="menuBtn">Map This Well</button><button id="getWellFileBBtn" class="menuBtn">Get Well File</button>
    </div>
    <div id="nonFocus"></div>
 </td>
 <td height="0%" width="80%">
  <script Language="JavaScript"><!--
function FrontPage_Form1_Validator(theForm)
{

  if (theForm.FileNumber.value == "")
  {
    alert("Please enter a value for the \"File Number\" field.");
    theForm.FileNumber.focus();
    return (false);
  }

  if (theForm.FileNumber.value.length > 5)
  {
    alert("Please enter at most 5 characters in the \"File Number\" field.");
    theForm.FileNumber.focus();
    return (false);
  }

  var checkOK = "0123456789-";
  var checkStr = theForm.FileNumber.value;
  var allValid = true;
  for (i = 0;  i < checkStr.length;  i++)
  {
    ch = checkStr.charAt(i);
    for (j = 0;  j < checkOK.length;  j++)
      if (ch == checkOK.charAt(j))
        break;
    if (j == checkOK.length)
    {
      allValid = false;
      break;
    }
  }
  if (!allValid)
  {
    alert("Please enter only digit characters in the \"File Number\" field.");
    theForm.FileNumber.focus();
    return (false);
  }
  return (true);
}
//--></script><form method="POST" action="/oilgas/feeservices/getwellprod.asp" onsubmit="return FrontPage_Form1_Validator(this)" name="FrontPage_Form1">
  <h2 align="center">Get Well Production History Data</h2>
  <p align="center"><Label for="fileno">Enter File Number:</Label>              
  <input type="text" name="FileNumber" size="5" id="fileno"
  value="0" maxlength="5"></p>
  <p align="center"><input type="submit" value="Get Monthly Production Data"
  name="B1"></p>
  </form>
 </td>
 <td valign="top" align="right" nowrap width="20%" height="0%"><a href="https://www.dmr.nd.gov/oilgas/"><img border="0" src="https://www.dmr.nd.gov/oilgas/images/ogdmrlogosmall.gif"  width="108" height="67" alt="Return to Oil & Gas Home Page"></a></td>
 </tr>
</table>

<table border="0" cellpadding="0" cellspacing="0" width="98%" align="center" summary="Well data content table" height="0%">
<tr>
<td>
<div>NDIC File No: <b>22809</b> &#xa0;&#xa0;&#xa0; API No: <b>33-023-00836-00-00</b> &#xa0;&#xa0;&#xa0; CTB No: <b>122809</b><br>
Well Type: <b><span title="Oil & Gas">OG</span></b> &#xa0;&#xa0;&#xa0; Well Status: <b><span title="Active">A</span></b> &#xa0;&#xa0;&#xa0; Status Date: <b>10/5/2012</b> &#xa0;&#xa0;&#xa0; Wellbore type: <b>Horizontal</b><br>
Location: <b>NWNE 15-163-99</b> &#xa0;&#xa0;&#xa0; Footages: <b>298 FNL 1998 FEL</b> &#xa0;&#xa0;&#xa0; Latitude: <b>48.951490</b> &#xa0;&#xa0;&#xa0; Longitude: <b>-103.516702</b><br>
Current Operator: <b>SAMSON RESOURCES COMPANY</b><br>
Current Well Name: <b>BAJA  1522-04TFH</b><br>
Elevation(s):<b>&#xa0;2117 <span title="Kelly Bushing">KB</span>&#xa0; </b><b>&#xa0;2097 <span title="Graded Ground Level">GR</span>&#xa0; </b><b>&#xa0;2091 <span title="Original Ground Level">GL</span>&#xa0; </b> &#xa0;&#xa0; Total Depth: <b>17485 </b> &#xa0;&#xa0;&#xa0; Field: <a href=/oilgas/FeeServices/OrderIndex.asp?SELECTFIELD=AMBROSE><b><span title="Go to the AMBROSE Field Order Index">AMBROSE</span></b></a><br>
Spud Date(s):&#xa0;&#xa0;<b>7/9/2012</b><br>
Casing String(s):  <b>9.625" 1528'&#xa0;&#xa0;</b> <b>7" 8304'&#xa0;&#xa0;</b><br>
Completion Data<br>
&#xa0;&#xa0;&#xa0;Pool: <b>BAKKEN</b> &#xa0;&#xa0;&#xa0; Perfs: <b>8304-17485</b> &#xa0;&#xa0;&#xa0; Comp: <b>10/5/2012</b> &#xa0;&#xa0;&#xa0; Status: <b><span title="Producing by Artificial Lift">AL</span></b> &#xa0;&#xa0;&#xa0; Date: <b>10/22/2012</b> &#xa0;&#xa0;&#xa0; Spacing: <b><span title="1280 Acres, 2 Sections
Secs 15&22-163-99">2SEC</span></b><br>
Cumulative Production Data<br>
&#xa0;&#xa0;&#xa0;Pool: <b>BAKKEN</b> &#xa0;&#xa0;&#xa0; Cum Oil: <b>37552</b> &#xa0;&#xa0;&#xa0; Cum MCF Gas: <b>39854</b> &#xa0;&#xa0;&#xa0; Cum Water: <b>44995</b> &#xa0;&#xa0;&#xa0; [<a href="javascript: launchDecline('well','22809|BAKKEN');"><span title="Use the interactive curve program for the BAKKEN Pool (Requires Adobe Flash player)">Interactive Performance Curve</span></a>]
 [<a href="javascript:launchPDFPerfCurve('W7E0B2C4D22809');"><span title="Generate a PDF performance curve for the BAKKEN pool for this well">PDF Curve</span></a>]<br>
Production Test Data<br>
&#xa0;&#xa0;&#xa0;<span title="Initial Production Test">IP Test</span> Date: <b>10/22/2012</b> &#xa0;&#xa0;&#xa0; Pool: <b>BAKKEN</b> &#xa0;&#xa0;&#xa0; IP Oil: <b>315</b> &#xa0;&#xa0;&#xa0; IP MCF: <b>308</b> &#xa0;&#xa0;&#xa0; IP Water: <b>364</b><br>
</td></tr><td>Monthly Production Data<br>
<table style="border:1px solid saddlebrown;" cellpadding="4" cellspacing="0" width="100%" align="center" summary="Monthly production data content table" height="0%">
<thead><th style="border:1px solid saddlebrown;"><span title="Pool oil and/or gas was produced from">Pool</span></th><th style="border:1px solid saddlebrown;"><span title="Month and Year produced">Date</span></th><th style="border:1px solid saddlebrown;"><span title="Number of days the well produced during the month">Days</span></th><th style="border:1px solid saddlebrown;"><span title="Barrels of oil produced">BBLS Oil</span></th><th style="border:1px solid saddlebrown;"><span title="Barrels of oil sold">Runs</span></th><th style="border:1px solid saddlebrown;"><span title="Barrels of water produced">BBLS Water</th><th style="border:1px solid saddlebrown;"><span title="Thousand cubic feet of gas produced">MCF Prod</span></th><th style="border:1px solid saddlebrown;"><span title="Thousand cubic feet of gas sold">MCF Sold</span></th><th style="border:1px solid saddlebrown;"><span title="Thousand cubic feet of gas vented or flared">Vent/Flare</span></th></thead>
<tr><td style="border:1px solid saddlebrown;">BAKKEN</td><td style="border:1px solid saddlebrown;" align="right">4-2014</td><td style="border:1px solid saddlebrown;" align="right">3</td><td style="border:1px solid saddlebrown;" align="right">26</td><td style="border:1px solid saddlebrown;" align="right">408</td><td style="border:1px solid saddlebrown;" align="right">0</td><td style="border:1px solid saddlebrown;" align="right">61</td><td style="border:1px solid saddlebrown;" align="right">61</td><td style="border:1px solid saddlebrown;" align="right">0</td></tr>
<tr><td style="border:1px solid saddlebrown;">BAKKEN</td><td style="border:1px solid saddlebrown;" align="right">3-2014</td><td style="border:1px solid saddlebrown;" align="right">22</td><td style="border:1px solid saddlebrown;" align="right">790</td><td style="border:1px solid saddlebrown;" align="right">250</td><td style="border:1px solid saddlebrown;" align="right">958</td><td style="border:1px solid saddlebrown;" align="right">958</td><td style="border:1px solid saddlebrown;" align="right">652</td><td style="border:1px solid saddlebrown;" align="right">100</td></tr>
<tr><td style="border:1px solid saddlebrown;">BAKKEN</td><td style="border:1px solid saddlebrown;" align="right">2-2014</td><td style="border:1px solid saddlebrown;" align="right">7</td><td style="border:1px solid saddlebrown;" align="right">359</td><td style="border:1px solid saddlebrown;" align="right">240</td><td style="border:1px solid saddlebrown;" align="right">549</td><td style="border:1px solid saddlebrown;" align="right">274</td><td style="border:1px solid saddlebrown;" align="right">147</td><td style="border:1px solid saddlebrown;" align="right">69</td></tr>
<tr><td style="border:1px solid saddlebrown;">BAKKEN</td><td style="border:1px solid saddlebrown;" align="right">1-2014</td><td style="border:1px solid saddlebrown;" align="right">17</td><td style="border:1px solid saddlebrown;" align="right">609</td><td style="border:1px solid saddlebrown;" align="right">469</td><td style="border:1px solid saddlebrown;" align="right">998</td><td style="border:1px solid saddlebrown;" align="right">604</td><td style="border:1px solid saddlebrown;" align="right">283</td><td style="border:1px solid saddlebrown;" align="right">161</td></tr>
<tr><td style="border:1px solid saddlebrown;">BAKKEN</td><td style="border:1px solid saddlebrown;" align="right">12-2013</td><td style="border:1px solid saddlebrown;" align="right">26</td><td style="border:1px solid saddlebrown;" align="right">927</td><td style="border:1px solid saddlebrown;" align="right">1453</td><td style="border:1px solid saddlebrown;" align="right">1370</td><td style="border:1px solid saddlebrown;" align="right">1190</td><td style="border:1px solid saddlebrown;" align="right">558</td><td style="border:1px solid saddlebrown;" align="right">382</td></tr>
<tr><td style="border:1px solid saddlebrown;">BAKKEN</td><td style="border:1px solid saddlebrown;" align="right">11-2013</td><td style="border:1px solid saddlebrown;" align="right">25</td><td style="border:1px solid saddlebrown;" align="right">980</td><td style="border:1px solid saddlebrown;" align="right">651</td><td style="border:1px solid saddlebrown;" align="right">1543</td><td style="border:1px solid saddlebrown;" align="right">1145</td><td style="border:1px solid saddlebrown;" align="right">660</td><td style="border:1px solid saddlebrown;" align="right">245</td></tr>
<tr><td style="border:1px solid saddlebrown;">BAKKEN</td><td style="border:1px solid saddlebrown;" align="right">10-2013</td><td style="border:1px solid saddlebrown;" align="right">31</td><td style="border:1px solid saddlebrown;" align="right">1125</td><td style="border:1px solid saddlebrown;" align="right">1181</td><td style="border:1px solid saddlebrown;" align="right">1049</td><td style="border:1px solid saddlebrown;" align="right">1335</td><td style="border:1px solid saddlebrown;" align="right">818</td><td style="border:1px solid saddlebrown;" align="right">246</td></tr>
<tr><td style="border:1px solid saddlebrown;">BAKKEN</td><td style="border:1px solid saddlebrown;" align="right">9-2013</td><td style="border:1px solid saddlebrown;" align="right">29</td><td style="border:1px solid saddlebrown;" align="right">1177</td><td style="border:1px solid saddlebrown;" align="right">1182</td><td style="border:1px solid saddlebrown;" align="right">1213</td><td style="border:1px solid saddlebrown;" align="right">1347</td><td style="border:1px solid saddlebrown;" align="right">1059</td><td style="border:1px solid saddlebrown;" align="right">0</td></tr>
<tr><td style="border:1px solid saddlebrown;">BAKKEN</td><td style="border:1px solid saddlebrown;" align="right">8-2013</td><td style="border:1px solid saddlebrown;" align="right">31</td><td style="border:1px solid saddlebrown;" align="right">1409</td><td style="border:1px solid saddlebrown;" align="right">1378</td><td style="border:1px solid saddlebrown;" align="right">1534</td><td style="border:1px solid saddlebrown;" align="right">1315</td><td style="border:1px solid saddlebrown;" align="right">126</td><td style="border:1px solid saddlebrown;" align="right">899</td></tr>
<tr><td style="border:1px solid saddlebrown;">BAKKEN</td><td style="border:1px solid saddlebrown;" align="right">7-2013</td><td style="border:1px solid saddlebrown;" align="right">29</td><td style="border:1px solid saddlebrown;" align="right">1378</td><td style="border:1px solid saddlebrown;" align="right">1582</td><td style="border:1px solid saddlebrown;" align="right">1531</td><td style="border:1px solid saddlebrown;" align="right">1247</td><td style="border:1px solid saddlebrown;" align="right">0</td><td style="border:1px solid saddlebrown;" align="right">688</td></tr>
<tr><td style="border:1px solid saddlebrown;">BAKKEN</td><td style="border:1px solid saddlebrown;" align="right">6-2013</td><td style="border:1px solid saddlebrown;" align="right">30</td><td style="border:1px solid saddlebrown;" align="right">1864</td><td style="border:1px solid saddlebrown;" align="right">2063</td><td style="border:1px solid saddlebrown;" align="right">2387</td><td style="border:1px solid saddlebrown;" align="right">2073</td><td style="border:1px solid saddlebrown;" align="right">0</td><td style="border:1px solid saddlebrown;" align="right">959</td></tr>
<tr><td style="border:1px solid saddlebrown;">BAKKEN</td><td style="border:1px solid saddlebrown;" align="right">5-2013</td><td style="border:1px solid saddlebrown;" align="right">24</td><td style="border:1px solid saddlebrown;" align="right">1732</td><td style="border:1px solid saddlebrown;" align="right">1587</td><td style="border:1px solid saddlebrown;" align="right">2809</td><td style="border:1px solid saddlebrown;" align="right">1738</td><td style="border:1px solid saddlebrown;" align="right">0</td><td style="border:1px solid saddlebrown;" align="right">937</td></tr>
<tr><td style="border:1px solid saddlebrown;">BAKKEN</td><td style="border:1px solid saddlebrown;" align="right">4-2013</td><td style="border:1px solid saddlebrown;" align="right">30</td><td style="border:1px solid saddlebrown;" align="right">2184</td><td style="border:1px solid saddlebrown;" align="right">2071</td><td style="border:1px solid saddlebrown;" align="right">2448</td><td style="border:1px solid saddlebrown;" align="right">2008</td><td style="border:1px solid saddlebrown;" align="right">0</td><td style="border:1px solid saddlebrown;" align="right">851</td></tr>
<tr><td style="border:1px solid saddlebrown;">BAKKEN</td><td style="border:1px solid saddlebrown;" align="right">3-2013</td><td style="border:1px solid saddlebrown;" align="right">15</td><td style="border:1px solid saddlebrown;" align="right">1075</td><td style="border:1px solid saddlebrown;" align="right">1096</td><td style="border:1px solid saddlebrown;" align="right">1795</td><td style="border:1px solid saddlebrown;" align="right">1306</td><td style="border:1px solid saddlebrown;" align="right">0</td><td style="border:1px solid saddlebrown;" align="right">759</td></tr>
<tr><td style="border:1px solid saddlebrown;">BAKKEN</td><td style="border:1px solid saddlebrown;" align="right">2-2013</td><td style="border:1px solid saddlebrown;" align="right">28</td><td style="border:1px solid saddlebrown;" align="right">2786</td><td style="border:1px solid saddlebrown;" align="right">2955</td><td style="border:1px solid saddlebrown;" align="right">2119</td><td style="border:1px solid saddlebrown;" align="right">2754</td><td style="border:1px solid saddlebrown;" align="right">0</td><td style="border:1px solid saddlebrown;" align="right">1673</td></tr>
<tr><td style="border:1px solid saddlebrown;">BAKKEN</td><td style="border:1px solid saddlebrown;" align="right">1-2013</td><td style="border:1px solid saddlebrown;" align="right">22</td><td style="border:1px solid saddlebrown;" align="right">2831</td><td style="border:1px solid saddlebrown;" align="right">2261</td><td style="border:1px solid saddlebrown;" align="right">3278</td><td style="border:1px solid saddlebrown;" align="right">3404</td><td style="border:1px solid saddlebrown;" align="right">0</td><td style="border:1px solid saddlebrown;" align="right">2208</td></tr>
<tr><td style="border:1px solid saddlebrown;">BAKKEN</td><td style="border:1px solid saddlebrown;" align="right">12-2012</td><td style="border:1px solid saddlebrown;" align="right">26</td><td style="border:1px solid saddlebrown;" align="right">3017</td><td style="border:1px solid saddlebrown;" align="right">3358</td><td style="border:1px solid saddlebrown;" align="right">3137</td><td style="border:1px solid saddlebrown;" align="right">3729</td><td style="border:1px solid saddlebrown;" align="right">0</td><td style="border:1px solid saddlebrown;" align="right">2639</td></tr>
<tr><td style="border:1px solid saddlebrown;">BAKKEN</td><td style="border:1px solid saddlebrown;" align="right">11-2012</td><td style="border:1px solid saddlebrown;" align="right">30</td><td style="border:1px solid saddlebrown;" align="right">5156</td><td style="border:1px solid saddlebrown;" align="right">5015</td><td style="border:1px solid saddlebrown;" align="right">4955</td><td style="border:1px solid saddlebrown;" align="right">5099</td><td style="border:1px solid saddlebrown;" align="right">0</td><td style="border:1px solid saddlebrown;" align="right">3942</td></tr>
<tr><td style="border:1px solid saddlebrown;">BAKKEN</td><td style="border:1px solid saddlebrown;" align="right">10-2012</td><td style="border:1px solid saddlebrown;" align="right">30</td><td style="border:1px solid saddlebrown;" align="right">8127</td><td style="border:1px solid saddlebrown;" align="right">7743</td><td style="border:1px solid saddlebrown;" align="right">11322</td><td style="border:1px solid saddlebrown;" align="right">8267</td><td style="border:1px solid saddlebrown;" align="right">0</td><td style="border:1px solid saddlebrown;" align="right">7390</td></tr>
</table></div>

</td>
</tr>
</table>
<p align="center"><font size="-1">[<a href="/default.asp">DMR Home</a>] [<a href="/oilgas/disclaimer.asp">Disclaimer</a>] [<a href="/oilgas/privacy.asp">Privacy</a>] [<a href="/oilgas/security.asp">Security</a>] [<a href="/oilgas/feeservices/feedback.asp">Feedback</a>] [<a href="/oilgas/feeservices/myaccount.asp">My Account Information</a>]</font></p>
</body>

</html>

I have been able to pull the table into excel with the macro above, but I am having a hard time figuring out how to create a loop that will repeat this process multiple times, given a list of identifiers (the number attached to the end of the url), and create a new workbook for each page.

Recording a macro is little bit too little effort shown on your side. In principle Stack Overflow will not write code for you. It will help you with the code you have already written. For the beginning I'd recommend you to do some more research, you can start with links in http://stackoverflow.com/a/24217457/2626313 — xmojmr, Jun 24 '14 at 05:34
Isn't recording a macro a good place to start though? Sorry but I am very new to VBA and I don't know of a better starting point. As far as research goes, I have spent the past 3 days combing SO and other sites for some guidance on this question. Unfortunately the page you linked this question to isn't relevant. — user3767619, Jun 24 '14 at 15:25
recording a macro is good place to start. Your new coding effort is better (in the original code you have basically only modified 1 string constant, that was my point). Do you think this http://stackoverflow.com/a/24379595/2626313 page is more relevant? BTW your link example when you don't provide a password is of very little use for us. Can you copy the page to some public place? Or even better can you specify more precisely what exactly is the #1 problem. Do you need to learn how to use [Loop statement](http://msdn.microsoft.com/en-us/library/eked04a7.aspx)? — xmojmr, Jun 24 '14 at 16:10
Yes I think I do need to learn how to use a loop statement in this case, thanks for the link. I updated the question with the source code for an example of one of these webpages. I think the #1 problem at this point is figuring out how to use a do...loop to repeat this process for a list of identifiers I have in an worksheet. I can also generate a list of urls for those identifiers in excel if that would make the loop easier to write. — user3767619, Jun 24 '14 at 19:06

score 0 · Answer 1 · edited May 23 '17 at 11:57

1 in the long run you should consider reading a book or following a forum recommended in Stack Overflow question https://stackoverflow.com/questions/7523102/what-is-the-best-way-to-master-vba-macros-for-ms-office-applications

2 Excel VBA = Excel Object Model (the bricks) + Visual Basic for Applications (a glue)

In order to succeed with the manipulation of the Excel Object Model you must know how to use Visual Basic in general (create a loop, use an array, concatenate strings etc.)

Out of the many "Excel VBA tutorials" available http://www.excel-easy.com/vba.html seems to give quick intro and http://www.angelfire.com/biz7/julian_s/julian/julians_macros.htm seems to provide many copy/paste ready code snippets

3 For scraping more complex web tables (e.g. multi-paged, password protected, special button click needed etc.) you may find useful VBA mapping for the Selenium browser automation tool https://code.google.com/p/selenium-vba

Pressing F1 in the Visual Basic editor with cursor placed on a term you need get explained is also very fast learning way as the official Microsoft's Excel Developer reference documentation is very good

Pull tabular data from multiple web pages into workbooks

1 Answers1

Linked