Need to create an access table from two excel worksheets.

itzsonu

New Member
Joined
Mar 16, 2009
Messages
2
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.
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

itzsonu

New Member
Joined
Mar 16, 2009
Messages
2
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,521
Messages
5,625,296
Members
416,087
Latest member
drth

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top