Need to create an access table from two excel worksheets.

[itzsonu]

Hi,

Being new to Excel VBA programming I am going through exploring era. -

I have a requirement as:
- There are two excel worksheets which are getting populated from two URLs.
These worksheets consist of almost same set of records with different column names.

- I need to store these data into a common table into access, which will be having generic columns.

- To achieve this I have to create column mapping using VBA code.
e.g.: In one of the worksheets "TeamName" is called "Workgroup" while in other its called as "Team".
So this needs to be done in VBA before inserting into table.

Now being newbie to excel VBA, i got messed up. I am trying this out from last couple of days... but landed up in no mans land.

Appreciate any help or suggestion.

Thanks.

 

[Fazza]

Welcome. Example of Excel -> Access. HTH, Fazza http://www.mrexcel.com/forum/showthread.php?t=332472

 

[itzsonu]

Thanks for your concern. I have gone through the URL.

Well my requirement is not about inserting a bunch of recordsets into access table. I dont have any issue over there.

e.g. Shee1 contains this info

<table x:str="" style="border-collapse: collapse; width: 1872pt;" width="2496" border="0" cellpadding="0" cellspacing="0"><col style="width: 48pt;" span="39" width="64"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt; width: 48pt;" width="64" height="17">load_date</td> <td class="xl22" style="width: 48pt;" width="64">customer_name</td> <td class="xl22" style="width: 48pt;" width="64">engagement_name</td> <td class="xl22" style="width: 48pt;" width="64">project_name</td> <td class="xl22" style="width: 48pt;" width="64">task_name</td> <td class="xl22" style="width: 48pt;" width="64">global_wrk_grp_name</td> <td class="xl22" style="width: 48pt;" width="64">wrkgrp_name</td> <td class="xl22" style="width: 48pt;" width="64">resource_name</td> <td class="xl22" style="width: 48pt;" width="64">TimeDate</td> <td class="xl22" style="width: 48pt;" width="64">booked_res_name</td> <td class="xl22" style="width: 48pt;" width="64">RegularHours</td> <td class="xl22" style="width: 48pt;" width="64">ApprovedInvestmentHours</td> <td class="xl22" style="width: 48pt;" width="64">SubmittedForApproval</td> <td class="xl22" style="width: 48pt;" width="64">ApprovalStatus</td> <td class="xl22" style="width: 48pt;" width="64">ApprovalStatusDate</td> <td class="xl22" style="width: 48pt;" width="64">appr_res_name</td> <td class="xl22" style="width: 48pt;" width="64">InvoiceStatus</td> <td class="xl22" style="width: 48pt;" width="64">StartTime</td> <td class="xl22" style="width: 48pt;" width="64">EndTime</td> <td class="xl22" style="width: 48pt;" width="64">Billable</td> <td class="xl22" style="width: 48pt;" width="64">BillingRate</td> <td class="xl22" style="width: 48pt;" width="64">CostRate</td> <td class="xl22" style="width: 48pt;" width="64">BillingCurrency</td> <td class="xl22" style="width: 48pt;" width="64">CostCurrency</td> <td class="xl22" style="width: 48pt;" width="64">RateDate</td> <td class="xl22" style="width: 48pt;" width="64">work_loc_grp_name</td> <td class="xl22" style="width: 48pt;" width="64">worklocation_name</td> <td class="xl22" style="width: 48pt;" width="64">wrk_cd_cat_name</td> <td class="xl22" style="width: 48pt;" width="64">wrk_cd_name</td> <td class="xl22" style="width: 48pt;" width="64">ActualStatus</td> <td class="xl22" style="width: 48pt;" width="64">SourceSystem</td> <td class="xl22" style="width: 48pt;" width="64">CALC_Week</td> <td class="xl22" style="width: 48pt;" width="64">CALC_Month</td> <td class="xl22" style="width: 48pt;" width="64">CALC_Billable</td> <td class="xl22" style="width: 48pt;" width="64">CALC_Appr_Invstmt</td> <td class="xl22" style="width: 48pt;" width="64">CALC_Region</td> <td class="xl22" style="width: 48pt;" width="64">CALC_Resource_Ctry</td> <td class="xl22" style="width: 48pt;" width="64">CALC_Wrkgrp</td> <td class="xl22" style="width: 48pt;" width="64">Total</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl23" style="height: 12.75pt;" x:num="39873" align="right" height="17">3/1/2009</td> <td>WMS</td> <td>WMS</td> <td>WMS</td> <td>WMS</td> <td>Sky</td> <td>coe</td> <td>John</td> <td class="xl23" x:num="39840" align="right">1/27/2009</td> <td>John Jacob</td> <td x:num="" align="right">1.5</td> <td x:num="" align="right">1.5</td> <td x:num="" align="right">1</td> <td>A</td> <td class="xl23" x:num="39847.654351851852" align="right">2/3/2009</td> <td>Mike</td> <td x:num="" align="right">0</td> <td class="xl23" x:num="39840.581250000003" align="right">1/27/2009</td> <td class="xl23" x:num="39840.643750000003" align="right">1/27/2009</td> <td x:num="" align="right">1</td> <td x:num="" align="right">0</td> <td x:num="" align="right">0</td> <td>EUR</td> <td>USD</td> <td class="xl23" x:num="39847.812800925924" align="right">2/3/2009</td> <td>Netherlands</td> <td>NLD</td> <td>Application Management Services</td> <td>Customization Care</td> <td x:num="" align="right">1</td> <td>ChangePoint</td> <td x:num="" align="right">5</td> <td x:num="" align="right">1</td> <td x:num="" align="right">1.5</td> <td x:num="" align="right">0</td> <td>USA</td> <td>USA</td> <td>coe</td> <td x:num="" align="right">1.5</td> </tr> </tbody></table>and Sheet2 consists of following info

<table x:str="" style="border-collapse: collapse; width: 1577pt;" width="2102" border="0" cellpadding="0" cellspacing="0"><col style="width: 48pt;" width="64"> <col style="width: 114pt;" width="152"> <col style="width: 119pt;" width="158"> <col style="width: 48pt;" span="27" width="64"> <tbody><tr style="height: 54pt;" height="72"> <td class="xl24" style="height: 54pt; width: 48pt;" width="64" height="72">RESOURCEID</td> <td class="xl25" style="width: 114pt;" width="152">SDATE</td> <td class="xl25" style="width: 119pt;" width="158">EDATE</td> <td class="xl25" style="width: 48pt;" width="64">EMPLOYEETYPE</td> <td class="xl25" style="width: 48pt;" width="64">OBS</td> <td class="xl25" style="width: 48pt;" width="64">COMPANY</td> <td class="xl25" style="width: 48pt;" width="64">MANAGER</td> <td class="xl25" style="width: 48pt;" width="64">HCMID</td> <td class="xl25" style="width: 48pt;" width="64">FIRSTNAME</td> <td class="xl25" style="width: 48pt;" width="64">LASTNAME</td> <td class="xl25" style="width: 48pt;" width="64">PROJECTNUMBER</td> <td class="xl25" style="width: 48pt;" width="64">RESOURCELEGALENTITY</td> <td class="xl25" style="width: 48pt;" width="64">PROJECTNAME</td> <td class="xl25" style="width: 48pt;" width="64">TASKNAME</td> <td class="xl25" style="width: 48pt;" width="64">CHARGECODE</td> <td class="xl25" style="width: 48pt;" width="64">TASKTYPE</td> <td class="xl25" style="width: 48pt;" width="64">UTILIZATIONFLAG</td> <td class="xl25" style="width: 48pt;" width="64">UTILIZATIONPERCENTAGE</td> <td class="xl25" style="width: 48pt;" width="64">HOURSBOOKED</td> <td class="xl25" style="width: 48pt;" width="64">UTILIZED</td> <td class="xl26" style="width: 48pt;" width="64">Billable</td> <td class="xl26" style="width: 48pt;" width="64">Work Group</td> <td class="xl26" style="width: 48pt;" width="64">Week Num</td> <td class="xl26" style="width: 48pt;" width="64">Month</td> <td class="xl26" style="width: 48pt;" width="64">Billable Hours</td> <td class="xl26" style="width: 48pt;" width="64">Approved Investment</td> <td class="xl26" style="width: 48pt;" width="64">Resource Country</td> <td class="xl26" style="width: 48pt;" width="64">Region</td> <td class="xl26" style="width: 48pt;" width="64">OBS</td> <td class="xl27" style="width: 48pt;" width="64">TOTAL</td> </tr> <tr style="height: 25.5pt;" height="34"> <td class="xl28" style="height: 25.5pt; width: 48pt;" width="64" height="34">John</td> <td class="xl29" style="width: 114pt;" x:num="39783" width="152" align="right">12/01/2008 12:00:00 AM</td> <td class="xl30" style="width: 119pt;" x:num="39783" width="158" align="right">Dec 1, 2008 12:00:00 AM</td> <td class="xl31" style="width: 48pt;" width="64">Employee</td> <td class="xl31" style="width: 48pt;" width="64">USA</td> <td class="xl31" style="width: 48pt;" width="64">0093</td> <td class="xl31" style="width: 48pt;" width="64">Mike</td> <td class="xl31" style="width: 48pt;" width="64">1823</td> <td class="xl31" style="width: 48pt;" width="64">John</td> <td class="xl31" style="width: 48pt;" width="64">Jacob</td> <td class="xl31" style="width: 48pt;" width="64">30159862</td> <td class="xl31" style="width: 48pt;" width="64">0093</td> <td class="xl31" style="width: 48pt;" width="64">WMS</td> <td class="xl31" style="width: 48pt;" width="64">WMS</td> <td class="xl31" style="width: 48pt;" width="64">NBILLCS</td> <td class="xl31" style="width: 48pt;" width="64">NB</td> <td class="xl32" style="width: 48pt;" x:num="" width="64">1</td> <td class="xl33" style="width: 48pt;" x:num="" width="64" align="right">100</td> <td class="xl34" style="width: 48pt;" x:num="" width="64">60.00</td> <td class="xl34" style="width: 48pt;" x:num="" width="64">60.00</td> <td x:num="" align="right">0</td> <td>coe</td> <td x:num="" align="right">49</td> <td x:num="" align="right">12</td> <td x:num="" align="right">0</td> <td x:num="" align="right">60</td> <td>USA</td> <td>USA</td> <td>USA</td> <td x:num="" align="right">60</td> </tr> </tbody></table>


Where both sheets contain information of hour logs of employees from two different sources. Thats why columns name are differing though they contain the same info.

My requirement is;
- I have to store these info in a generic access table with generic column name.
- The problem is how to create column mapping to insert data into table.
- Also, preventing insertion of duplicate record.
- I was thinking of one more option, Is it possible to create a new worksheet where in I can populate the data from Sheet1 & Sheet2 then store in the table.
I dont know whether it's feasible or not.

If my requirement is clear enough, do pass some valuable suggestion.

Any help would be greatly appreciated.

Thanks.