Combining multi-sheet spreadsheets into one (while keeping the separate sheets)

[viperx100]

Hi,

I have a collection of different excel files, each containing up to 3 worksheets. The 1st one is instructions (not always there), the 2nd one has data labeled SHP, and the 3 one S+C. One of the problems is that not all of the excel files have all 3 speadsheets (some have 1 or 2). What kind of a macro would I need to pull all of these files into 1 big excel file (keeping in mind that they are in their own respected directories labeled by the state, a dash and a 3 digit number, e.g. AK-500, FL-600, etc. All of those directories are in one main directory.) It is important that the respected worksheets combine the data that belongs to them SHP with SHP and S+C with S+C. The data starts at the 8th row for the SHP and S+C sheets. Thanks, I am attaching a preview of one of the excel data files:

<table border="0" cellpadding="0" cellspacing="0" width="2162"><col style="mso-width-source:userset;mso-width-alt:2267;width:47pt" width="62"> <col style="mso-width-source:userset;mso-width-alt:2816;width:58pt" width="77"> <col style="mso-width-source:userset;mso-width-alt:2267;width:47pt" width="62"> <col style="mso-width-source:userset;mso-width-alt:6582;width:135pt" width="180"> <col style="width:47pt" width="62"> <col style="mso-width-source:userset;mso-width-alt:5376;width:110pt" width="147"> <col style="mso-width-source:userset;mso-width-alt:3584;width:74pt" width="98"> <col style="mso-width-source:userset;mso-width-alt:2230;width:46pt" width="61"> <col style="mso-width-source:userset;mso-width-alt:2121;width:44pt" width="58"> <col style="mso-width-source:userset;mso-width-alt:2230;width:46pt" width="61"> <col style="mso-width-source:userset;mso-width-alt:2121;width:44pt" width="58"> <col style="mso-width-source:userset;mso-width-alt:1718;width:35pt" width="47"> <col style="mso-width-source:userset;mso-width-alt:2267;width:47pt" width="62"> <col style="mso-width-source:userset;mso-width-alt:2048;width:42pt" width="56"> <col style="mso-width-source:userset;mso-width-alt:2084; width:43pt" span="6" width="57"> <col style="mso-width-source:userset;mso-width-alt:2304;width:47pt" width="63"> <col style="width:47pt" width="62"> <col style="mso-width-source:userset;mso-width-alt:1974;width:41pt" width="54"> <col style="mso-width-source:userset;mso-width-alt:2084;width:43pt" width="57"> <col style="mso-width-source:userset;mso-width-alt:2450;width:50pt" width="67"> <col style="mso-width-source:userset;mso-width-alt:2084;width:43pt" width="57"> <col style="mso-width-source:userset;mso-width-alt:5046;width:104pt" width="138"> <col style="width:47pt" width="62"> <col style="mso-width-source:userset;mso-width-alt:3913;width:80pt" width="107"> <col style="width:47pt" width="62"> <tbody><tr style="height:18.0pt" height="24"> <td class="xl76" style="height:18.0pt;width:47pt" height="24" width="62">Field Office:</td> <td class="xl77" style="border-left:none;width:58pt" width="77">Anchorage</td> <td class="xl78" style="width:47pt" width="62">
</td> <td class="xl78" style="width:135pt" width="180">
</td> <td class="xl79" style="width:47pt" width="62">
</td> <td class="xl80" style="width:110pt" width="147">
</td> <td class="xl80" style="width:74pt" width="98">
</td> <td class="xl80" style="width:46pt" width="61">
</td> <td class="xl80" style="width:44pt" width="58">
</td> <td class="xl81" style="width:46pt" width="61">
</td> <td class="xl80" style="width:44pt" width="58">
</td> <td class="xl82" style="width:35pt" width="47">
</td> <td class="xl82" style="width:47pt" width="62">
</td> <td class="xl82" style="width:42pt" width="56">
</td> <td class="xl82" style="width:43pt" width="57">
</td> <td class="xl82" style="width:43pt" width="57">
</td> <td class="xl82" style="width:43pt" width="57">
</td> <td class="xl82" style="width:43pt" width="57">
</td> <td class="xl81" style="width:43pt" width="57">
</td> <td class="xl80" style="width:43pt" width="57">
</td> <td class="xl83" style="width:47pt" width="63">
</td> <td class="xl83" style="width:47pt" width="62">
</td> <td class="xl83" style="width:41pt" width="54">
</td> <td class="xl84" style="width:43pt" width="57">
</td> <td class="xl79" style="width:50pt" width="67">
</td> <td class="xl79" style="width:43pt" width="57">
</td> <td class="xl79" style="width:104pt" width="138">
</td> <td class="xl83" style="width:47pt" width="62">
</td> <td class="xl83" style="width:80pt" width="107">
</td> <td class="xl83" style="width:47pt" width="62">
</td> </tr> <tr style="height:23.25pt" height="31"> <td class="xl86" style="height:23.25pt;border-top:none; width:47pt" height="31" width="62">CoC Number:</td> <td class="xl87" style="border-top:none;border-left:none">AK-500</td> <td class="xl78" style="width:47pt" width="62">
</td> <td class="xl78" style="width:135pt" width="180">
</td> <td class="xl79">
</td> <td class="xl80">
</td> <td class="xl80">
</td> <td class="xl80">
</td> <td class="xl80">
</td> <td class="xl81">
</td> <td class="xl80">
</td> <td class="xl88">
</td> <td class="xl88">
</td> <td class="xl88">
</td> <td class="xl88">
</td> <td class="xl88">
</td> <td class="xl88">
</td> <td class="xl88">
</td> <td class="xl81">
</td> <td class="xl80">
</td> <td class="xl83">
</td> <td class="xl83">
</td> <td class="xl83">
</td> <td class="xl84" style="width:43pt" width="57">
</td> <td class="xl79">
</td> <td class="xl79">
</td> <td class="xl79">
</td> <td class="xl83">
</td> <td class="xl83">
</td> <td class="xl83">
</td> </tr> <tr style="height:18.75pt" height="25"> <td class="xl89" style="height:18.75pt;border-top:none; width:47pt" height="25" width="62">CoC Name:</td> <td class="xl87" style="border-top:none;border-left:none">Anchorage CoC</td> <td class="xl78" style="width:47pt" width="62">
</td> <td class="xl78" style="width:135pt" width="180">
</td> <td class="xl79">
</td> <td class="xl80">
</td> <td class="xl80">
</td> <td class="xl80">
</td> <td class="xl80">
</td> <td class="xl81">
</td> <td class="xl80">
</td> <td class="xl88">
</td> <td class="xl88">
</td> <td class="xl88">
</td> <td class="xl88">
</td> <td class="xl88">
</td> <td class="xl88">
</td> <td class="xl88">
</td> <td class="xl81">
</td> <td class="xl80">
</td> <td class="xl83">
</td> <td class="xl83">
</td> <td class="xl83">
</td> <td class="xl84" style="width:43pt" width="57">
</td> <td class="xl79">
</td> <td class="xl79">
</td> <td class="xl79">
</td> <td class="xl83">
</td> <td class="xl83">
</td> <td class="xl83">
</td> </tr> <tr style="height:18.75pt" height="25"> <td class="xl84" style="height:18.75pt;width:47pt" height="25" width="62">
</td> <td class="xl84" style="width:58pt" width="77">
</td> <td class="xl84" style="width:47pt" width="62">
</td> <td class="xl90" style="width:135pt" width="180">
</td> <td class="xl91">
</td> <td class="xl88">
</td> <td class="xl88">
</td> <td class="xl88">
</td> <td class="xl88">
</td> <td class="xl92">
</td> <td class="xl88">
</td> <td class="xl88">
</td> <td class="xl88">
</td> <td class="xl88">
</td> <td class="xl88">
</td> <td class="xl88">
</td> <td class="xl88">
</td> <td class="xl88">
</td> <td class="xl92">
</td> <td class="xl80">
</td> <td class="xl83">
</td> <td class="xl83">
</td> <td class="xl83">
</td> <td class="xl84" style="width:43pt" width="57">
</td> <td class="xl91">
</td> <td class="xl91">
</td> <td class="xl91">
</td> <td class="xl83">
</td> <td class="xl83">
</td> <td class="xl83">
</td> </tr> <tr style="height:16.5pt" height="22"> <td class="xl93" colspan="9" style="height:16.5pt;mso-ignore:colspan" height="22">S+C Grants Expiring in Calendar Year 2012 (time period beginning January 1 and ending December 31)</td> <td class="xl94"> </td> <td class="xl94"> </td> <td class="xl94"> </td> <td class="xl94"> </td> <td class="xl94"> </td> <td class="xl94"> </td> <td class="xl94"> </td> <td class="xl94"> </td> <td class="xl94"> </td> <td class="xl94"> </td> <td class="xl94"> </td> <td class="xl94"> </td> <td class="xl94"> </td> <td class="xl95"> </td> <td class="xl95"> </td> <td class="xl95"> </td> <td class="xl95"> </td> <td class="xl115">Capacity Review</td> <td class="xl116" style="width:47pt" width="62"> </td> <td class="xl120">Final Review</td> <td class="xl121" style="width:47pt" width="62"> </td> </tr> <tr style="height:18.75pt" height="25"> <td class="xl96" style="height:18.75pt;width:47pt" height="25" width="62">
</td> <td class="xl96" style="width:58pt" width="77">
</td> <td class="xl96" style="width:47pt" width="62">
</td> <td class="xl97" style="width:135pt" width="180">
</td> <td class="xl97" style="width:47pt" width="62">
</td> <td class="xl97" style="width:110pt" width="147">
</td> <td class="xl97" style="width:74pt" width="98">
</td> <td class="xl98">
</td> <td class="xl88">
</td> <td class="xl88">
</td> <td class="xl88">
</td> <td class="xl88">
</td> <td class="xl92">
</td> <td class="xl88">
</td> <td class="xl88">
</td> <td class="xl88">
</td> <td class="xl88">
</td> <td class="xl88">
</td> <td class="xl88">
</td> <td class="xl88">
</td> <td class="xl88">
</td> <td class="xl92">
</td> <td class="xl80">
</td> <td class="xl83">
</td> <td class="xl83">
</td> <td class="xl83">
</td> <td class="xl96" style="width:104pt" width="138">
</td> <td class="xl96" style="width:47pt" width="62">
</td> <td class="xl96" style="width:80pt" width="107">
</td> <td class="xl96" style="width:47pt" width="62">
</td> </tr> <tr style="height:102.0pt" height="136"> <td class="xl99" style="height:102.0pt;width:47pt" height="136" width="62">CoC Number</td> <td class="xl100" style="width:58pt" width="77">CoC Name</td> <td class="xl101" style="border-left:none;width:47pt" width="62">Field Office</td> <td class="xl100" style="width:135pt" width="180">Grantee Name</td> <td class="xl102" style="width:47pt" width="62">EIN</td> <td class="xl103" style="border-left:none;width:110pt" width="147">Project Name</td> <td class="xl103" style="border-left:none;width:74pt" width="98">Project Number</td> <td class="xl103" style="border-left:none;width:46pt" width="61">Component</td> <td class="xl104" style="border-left:none;width:44pt" width="58">Effective Date (mm/yyyy)</td> <td class="xl104" style="border-left:none;width:46pt" width="61">Grant Term in Years (actual or proposed)</td> <td class="xl104" style="border-left:none;width:44pt" width="58">Was This Project Extended?</td> <td class="xl104" style="border-left:none;width:35pt" width="47">Is This a 2005 Grant?</td> <td class="xl104" style="border-left:none;width:47pt" width="62">Is this a 1st time renewal?</td> <td class="xl104" style="border-left:none;width:42pt" width="56">SRO Units</td> <td class="xl104" style="border-left:none;width:43pt" width="57">0 BR Units</td> <td class="xl104" style="border-left:none;width:43pt" width="57">1 BR Units</td> <td class="xl104" style="border-left:none;width:43pt" width="57">2 BR Units</td> <td class="xl104" style="border-left:none;width:43pt" width="57">3 BR Units</td> <td class="xl104" style="border-left:none;width:43pt" width="57">4 BR Units</td> <td class="xl104" style="border-left:none;width:43pt" width="57">5 BR Units</td> <td class="xl104" style="border-left:none;width:47pt" width="63">6+ BR Units</td> <td class="xl105" style="border-left:none;width:47pt" width="62">Is the total number of units more than what is in the original grant agreement?</td> <td class="xl106" style="width:41pt" width="54">Expected Expiration Date</td> <td class="xl107" style="border-left:none;width:43pt" width="57">Total Units Eligible for Renewal</td> <td class="xl108" style="width:50pt" width="67">Total Grant Amount </td> <td class="xl107" style="width:43pt" width="57">Comments</td> <td class="xl117" style="width:104pt" width="138">Recommend for Rejection?
(if yes, specify in Comments)</td> <td class="xl118" style="border-left:none;width:47pt" width="62">Comments
(maximum 400 characters)</td> <td class="xl122" style="width:80pt" width="107">Recommend Rejection?
(if yes, specify Rejection # below)</td> <td class="xl123" style="border-left:none;width:47pt" width="62">Comments
(maximum 400 characters)</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl87" style="height:15.0pt" height="20">AK-500</td> <td class="xl87" style="border-top:none;border-left:none">Anchorage CoC</td> <td class="xl77" style="border-top:none;border-left:none">Anchorage</td> <td class="xl87" style="border-top:none">Alaska Housing Finance Corporation</td> <td class="xl87" style="border-left:none">92-0047291</td> <td class="xl87" style="border-left:none">AK-500 - REN - ACMH SPC</td> <td class="xl87" style="border-left:none">AK0003C0C001003</td> <td class="xl87" style="border-left:none">SRA</td> <td class="xl109" style="border-left:none">2011</td> <td class="xl110" style="border-left:none">1</td> <td class="xl110" style="border-left:none">No</td> <td class="xl110" style="border-left:none">No</td> <td class="xl110" style="border-left:none">No</td> <td class="xl110" style="border-left:none"> </td> <td class="xl110" style="border-left:none"> </td> <td class="xl110" style="border-left:none">24</td> <td class="xl110" style="border-left:none">2</td> <td class="xl110" style="border-left:none">2</td> <td class="xl110" style="border-left:none"> </td> <td class="xl110" style="border-left:none"> </td> <td class="xl110" style="border-left:none"> </td> <td class="xl110" style="border-left:none">No</td> <td class="xl112" style="width:41pt" width="54">2012</td> <td class="xl113" style="border-left:none">28</td> <td class="xl114">298,560</td> <td class="xl111" style="border-left:none"> </td> <td class="xl125" style="width:104pt" width="138">No</td> <td class="xl119" style="border-left:none;width:47pt" width="62"> </td> <td class="xl124" style="border-left:none;width:80pt" width="107"> </td> <td class="xl124" style="border-left:none;width:47pt" width="62"> </td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl87" style="height:15.0pt;border-top:none" height="20">AK-500</td> <td class="xl87" style="border-top:none;border-left:none">Anchorage CoC</td> <td class="xl77" style="border-top:none;border-left:none">Anchorage</td> <td class="xl87" style="border-top:none">Alaska Housing Finance Corporation</td> <td class="xl87" style="border-top:none;border-left:none">92-0047291</td> <td class="xl87" style="border-top:none;border-left:none">AK-500 - REN - Four A's SPC</td> <td class="xl87" style="border-top:none;border-left:none">AK0007C0C001003</td> <td class="xl87" style="border-top:none;border-left:none">SRA</td> <td class="xl109" style="border-top:none;border-left:none">2011</td> <td class="xl110" style="border-top:none;border-left:none">1</td> <td class="xl110" style="border-top:none;border-left:none">No</td> <td class="xl110" style="border-top:none;border-left:none">No</td> <td class="xl110" style="border-top:none;border-left:none">No</td> <td class="xl110" style="border-top:none;border-left:none"> </td> <td class="xl110" style="border-top:none;border-left:none">1</td> <td class="xl110" style="border-top:none;border-left:none">7</td> <td class="xl110" style="border-top:none;border-left:none">2</td> <td class="xl110" style="border-top:none;border-left:none">1</td> <td class="xl110" style="border-top:none;border-left:none"> </td> <td class="xl110" style="border-top:none;border-left:none"> </td> <td class="xl110" style="border-top:none;border-left:none"> </td> <td class="xl110" style="border-top:none;border-left:none">No</td> <td class="xl112" style="width:41pt" width="54">2012</td> <td class="xl113" style="border-left:none">11</td> <td class="xl114">120,864</td> <td class="xl111" style="border-left:none"> </td> <td class="xl125" style="border-top:none;width:104pt" width="138">No</td> <td class="xl119" style="border-top:none;border-left:none;width:47pt" width="62"> </td> <td class="xl124" style="border-top:none;border-left:none;width:80pt" width="107"> </td> <td class="xl124" style="border-top:none;border-left:none;width:47pt" width="62"> </td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl87" style="height:15.0pt;border-top:none" height="20">AK-500</td> <td class="xl87" style="border-top:none;border-left:none">Anchorage CoC</td> <td class="xl77" style="border-top:none;border-left:none">Anchorage</td> <td class="xl87" style="border-top:none">Alaska Housing Finance Corporation</td> <td class="xl87" style="border-top:none;border-left:none">92-0047291</td> <td class="xl87" style="border-top:none;border-left:none">AK-500 - REN - Coming Home II</td> <td class="xl87" style="border-top:none;border-left:none">AK0029C0C001001</td> <td class="xl87" style="border-top:none;border-left:none">SRA</td> <td class="xl109" style="border-top:none;border-left:none">2011</td> <td class="xl110" style="border-top:none;border-left:none">1</td> <td class="xl110" style="border-top:none;border-left:none">No</td> <td class="xl110" style="border-top:none;border-left:none">No</td> <td class="xl110" style="border-top:none;border-left:none">No</td> <td class="xl110" style="border-top:none;border-left:none"> </td> <td class="xl110" style="border-top:none;border-left:none"> </td> <td class="xl110" style="border-top:none;border-left:none">13</td> <td class="xl110" style="border-top:none;border-left:none">4</td> <td class="xl110" style="border-top:none;border-left:none">2</td> <td class="xl110" style="border-top:none;border-left:none"> </td> <td class="xl110" style="border-top:none;border-left:none"> </td> <td class="xl110" style="border-top:none;border-left:none"> </td> <td class="xl110" style="border-top:none;border-left:none">No</td> <td class="xl112" style="width:41pt" width="54">2012</td> <td class="xl113" style="border-left:none">19</td> <td class="xl114">214,392</td> <td class="xl111" style="border-left:none"> </td> <td class="xl125" style="border-top:none;width:104pt" width="138">No</td> <td class="xl119" style="border-top:none;border-left:none;width:47pt" width="62"> </td> <td class="xl124" style="border-top:none;border-left:none;width:80pt" width="107"> </td> <td class="xl124" style="border-top:none;border-left:none;width:47pt" width="62"> </td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl87" style="height:15.0pt;border-top:none" height="20">AK-500</td> <td class="xl87" style="border-top:none;border-left:none">Anchorage CoC</td> <td class="xl77" style="border-top:none;border-left:none">Anchorage</td> <td class="xl87" style="border-top:none">Alaska Housing Finance Corporation</td> <td class="xl87" style="border-top:none;border-left:none">92-0047291</td> <td class="xl87" style="border-top:none;border-left:none">AK-500-New-RurAL CAP PSH</td> <td class="xl87" style="border-top:none;border-left:none">AK06C600001</td> <td class="xl87" style="border-top:none;border-left:none">SRA</td> <td class="xl109" style="border-top:none;border-left:none">2007</td> <td class="xl110" style="border-top:none;border-left:none">5</td> <td class="xl110" style="border-top:none;border-left:none">No</td> <td class="xl110" style="border-top:none;border-left:none">No</td> <td class="xl110" style="border-top:none;border-left:none">Yes</td> <td class="xl110" style="border-top:none;border-left:none">5</td> <td class="xl110" style="border-top:none;border-left:none"> </td> <td class="xl110" style="border-top:none;border-left:none"> </td> <td class="xl110" style="border-top:none;border-left:none"> </td> <td class="xl110" style="border-top:none;border-left:none"> </td> <td class="xl110" style="border-top:none;border-left:none"> </td> <td class="xl110" style="border-top:none;border-left:none"> </td> <td class="xl110" style="border-top:none;border-left:none"> </td> <td class="xl110" style="border-top:none;border-left:none">Yes</td> <td class="xl112" style="width:41pt" width="54">2012</td> <td class="xl113" style="border-left:none">5</td> <td class="xl114">89,100</td> <td class="xl111" style="border-left:none"> </td> <td class="xl125" style="border-top:none;width:104pt" width="138">No</td> <td class="xl119" style="border-top:none;border-left:none;width:47pt" width="62"> </td> <td class="xl124" style="border-top:none;border-left:none;width:80pt" width="107"> </td> <td class="xl124" style="border-top:none;border-left:none;width:47pt" width="62"> </td> </tr> </tbody></table>

 

[viperx100]

Is there a way to use Access to pull the excel files directly into the database and do this while creating a table with the results? I know that this might be the wrong forum for this question.