Hi,
I have two tables. I should merge the two tables and form an output table.
<table style="border-collapse: collapse; width: 203pt;" width="271" border="0" cellpadding="0" cellspacing="0"><col style="width: 35pt;" width="47"> <col style="width: 69pt;" width="92"> <col style="width: 51pt;" width="68"> <col style="width: 48pt;" width="64"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; width: 35pt;" width="47" height="20">PID</td> <td class="xl65" style="width: 69pt;" width="92">Project Name</td> <td class="xl65" style="width: 51pt;" width="68">Status</td> <td style="vertical-align: top;">
</td><td class="xl65" style="width: 48pt;" width="64">Hours</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">1111</td> <td class="xl65">AAA</td> <td class="xl65">Approved</td> <td style="vertical-align: top;">
</td><td class="xl65">56</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">1111</td> <td class="xl65">AAA</td> <td class="xl65">Completed</td> <td style="vertical-align: top;">
</td><td class="xl65">565</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">2222</td> <td class="xl65">BBB</td> <td class="xl65">On Hold</td> <td style="vertical-align: top;">
</td><td class="xl65">34</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">2222</td> <td class="xl65">BBB</td> <td class="xl65">Completed</td> <td style="vertical-align: top;">
</td><td class="xl65">56</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">3333</td> <td class="xl65">CCC</td> <td class="xl65">Completed</td> <td style="vertical-align: top;">
</td><td class="xl65">645</td> </tr> </tbody></table>
<table style="border-collapse: collapse; width: 222pt;" width="296" border="0" cellpadding="0" cellspacing="0"><col style="width: 48pt;" width="64"> <col style="width: 69pt;" width="92"> <col style="width: 57pt;" width="76"> <col style="width: 48pt;" width="64"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; width: 48pt;" width="64" height="20">PID</td> <td class="xl65" style="border-left: medium none; width: 69pt;" width="92">Project Name</td> <td class="xl65" style="border-left: medium none; width: 57pt;" width="76">Status</td> <td style="vertical-align: top;">
</td><td class="xl65" style="border-left: medium none; width: 48pt;" width="64">Expense</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="border-top: medium none; height: 15pt;" height="20">1111</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">AAA</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">Approved</td> <td style="vertical-align: top;">
</td><td class="xl65" style="border-top: medium none; border-left: medium none;">34534</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="border-top: medium none; height: 15pt;" height="20">1111</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">AAA</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">Completed</td> <td style="vertical-align: top;">
</td><td class="xl65" style="border-top: medium none; border-left: medium none;">754734</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="border-top: medium none; height: 15pt;" height="20">2222</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">BBB</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">On Hold</td> <td style="vertical-align: top;">
</td><td class="xl65" style="border-top: medium none; border-left: medium none;">646734</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="border-top: medium none; height: 15pt;" height="20">2222</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">BBB</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">Completed</td> <td style="vertical-align: top;">
</td><td class="xl65" style="border-top: medium none; border-left: medium none;">84843</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="border-top: medium none; height: 15pt;" height="20">4444</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">DDD</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">Completed</td> <td style="vertical-align: top;">
</td><td class="xl65" style="border-top: medium none; border-left: medium none;">45345</td> </tr> </tbody></table>
The output should be
<table style="border-collapse: collapse; width: 251pt;" width="335" border="0" cellpadding="0" cellspacing="0"><col style="width: 35pt;" width="47"> <col style="width: 69pt;" width="92"> <col style="width: 51pt;" width="68"> <col style="width: 48pt;" width="64" span="2"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; width: 35pt;" width="47" height="20">PID</td> <td class="xl65" style="width: 69pt;" width="92">Project Name</td> <td class="xl65" style="width: 51pt;" width="68">Status</td> <td style="vertical-align: top;">
</td><td class="xl65" style="width: 48pt;" width="64">Hours</td> <td class="xl65" style="width: 48pt;" width="64">Expense</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">1111</td> <td class="xl65">AAA</td> <td class="xl65">Approved</td> <td style="vertical-align: top;">
</td><td class="xl65">56</td> <td class="xl65">34534</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">1111</td> <td class="xl65">AAA</td> <td class="xl65">Completed</td> <td style="vertical-align: top;">
</td><td class="xl65">565</td> <td class="xl65">754734</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">2222</td> <td class="xl65">BBB</td> <td class="xl65">On Hold</td> <td style="vertical-align: top;">
</td><td class="xl65">34</td> <td class="xl65">646734</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">2222</td> <td class="xl65">BBB</td> <td class="xl65">Completed</td> <td style="vertical-align: top;">
</td><td class="xl65">56</td> <td class="xl65">84843</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">3333</td> <td class="xl65">CCC</td> <td class="xl65">Completed</td> <td style="vertical-align: top;">
</td><td class="xl65">645</td> <td class="xl65">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">4444</td> <td class="xl65">DDD</td> <td class="xl65">Completed</td> <td style="vertical-align: top;">
</td><td class="xl65">
</td> <td class="xl65">45345</td> </tr> </tbody></table>
The fields PID, Project Name and Status are common for both the tables. Using this we need to match and merge the values.
Note that even if there is no match, those values should also be present in the output table.
I tried to establish a connection and write a query.
Below is the code
Set cnConn = CreateObject("ADODB.Connection")
With cnConn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=" & strAppPath & strPlanViewOutputFile & ";Extended Properties=Excel 8.0;"
.Open
End With
strQuery = "SELECT PlanView.[PV ID], PlanView.[Project Name], PlanView.[Status], PlanView.[Expense], Actuals.[Hours] FROM [Sheet5$B2:L10] PlanView, [Sheet4$B1:H10] Actuals Where PlanView.[PV ID] = Actuals.[PV ID] And PlanView.[Project Name] = Actuals.[Project Name] And PlanView.[Status] = Actuals.[Status]"
Set rsRecordSet = CreateObject("ADODB.Recordset")
With rsRecordSet
.ActiveConnection = cnConn
.Source = strQuery
.Open
End With
If rsRecordSet.EOF = True And rsRecordSet.BOF = True Then
GoTo TakeNextRecord
End If
counter = 2
rsRecordSet.MoveFirst
Do Until rsRecordSet.EOF = True
xlTargetWrkSht.Cells(counter, 1) = Trim$(rsRecordSet("PV ID").Value)
xlTargetWrkSht.Cells(counter, 2) = Trim$(rsRecordSet("Project Name").Value)
xlTargetWrkSht.Cells(counter, 3) = Trim$(rsRecordSet("Status").Value)
counter = counter + 1
rsRecordSet.MoveNext
Loop
The problem i face here is the query i form
I get an error if i add the where clause. If i remove the where clause, it works. For example, below code works.
strQuery = "SELECT PlanView.[PV ID], PlanView.[Project Name], PlanView.[Status], PlanView.[Expense] FROM [Sheet5$B2:L10] PlanView, [Sheet4$B1:H10] Actuals
Please let me know what can be done. Its not necessary that we need to use the connection and query the tables. If there is any new approach, please let me know.
Any help will be hightly appreciated.
Thanks in advance,
Jaya
I have two tables. I should merge the two tables and form an output table.
<table style="border-collapse: collapse; width: 203pt;" width="271" border="0" cellpadding="0" cellspacing="0"><col style="width: 35pt;" width="47"> <col style="width: 69pt;" width="92"> <col style="width: 51pt;" width="68"> <col style="width: 48pt;" width="64"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; width: 35pt;" width="47" height="20">PID</td> <td class="xl65" style="width: 69pt;" width="92">Project Name</td> <td class="xl65" style="width: 51pt;" width="68">Status</td> <td style="vertical-align: top;">
</td><td class="xl65" style="width: 48pt;" width="64">Hours</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">1111</td> <td class="xl65">AAA</td> <td class="xl65">Approved</td> <td style="vertical-align: top;">
</td><td class="xl65">56</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">1111</td> <td class="xl65">AAA</td> <td class="xl65">Completed</td> <td style="vertical-align: top;">
</td><td class="xl65">565</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">2222</td> <td class="xl65">BBB</td> <td class="xl65">On Hold</td> <td style="vertical-align: top;">
</td><td class="xl65">34</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">2222</td> <td class="xl65">BBB</td> <td class="xl65">Completed</td> <td style="vertical-align: top;">
</td><td class="xl65">56</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">3333</td> <td class="xl65">CCC</td> <td class="xl65">Completed</td> <td style="vertical-align: top;">
</td><td class="xl65">645</td> </tr> </tbody></table>
<table style="border-collapse: collapse; width: 222pt;" width="296" border="0" cellpadding="0" cellspacing="0"><col style="width: 48pt;" width="64"> <col style="width: 69pt;" width="92"> <col style="width: 57pt;" width="76"> <col style="width: 48pt;" width="64"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; width: 48pt;" width="64" height="20">PID</td> <td class="xl65" style="border-left: medium none; width: 69pt;" width="92">Project Name</td> <td class="xl65" style="border-left: medium none; width: 57pt;" width="76">Status</td> <td style="vertical-align: top;">
</td><td class="xl65" style="border-left: medium none; width: 48pt;" width="64">Expense</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="border-top: medium none; height: 15pt;" height="20">1111</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">AAA</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">Approved</td> <td style="vertical-align: top;">
</td><td class="xl65" style="border-top: medium none; border-left: medium none;">34534</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="border-top: medium none; height: 15pt;" height="20">1111</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">AAA</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">Completed</td> <td style="vertical-align: top;">
</td><td class="xl65" style="border-top: medium none; border-left: medium none;">754734</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="border-top: medium none; height: 15pt;" height="20">2222</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">BBB</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">On Hold</td> <td style="vertical-align: top;">
</td><td class="xl65" style="border-top: medium none; border-left: medium none;">646734</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="border-top: medium none; height: 15pt;" height="20">2222</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">BBB</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">Completed</td> <td style="vertical-align: top;">
</td><td class="xl65" style="border-top: medium none; border-left: medium none;">84843</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="border-top: medium none; height: 15pt;" height="20">4444</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">DDD</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">Completed</td> <td style="vertical-align: top;">
</td><td class="xl65" style="border-top: medium none; border-left: medium none;">45345</td> </tr> </tbody></table>
The output should be
<table style="border-collapse: collapse; width: 251pt;" width="335" border="0" cellpadding="0" cellspacing="0"><col style="width: 35pt;" width="47"> <col style="width: 69pt;" width="92"> <col style="width: 51pt;" width="68"> <col style="width: 48pt;" width="64" span="2"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; width: 35pt;" width="47" height="20">PID</td> <td class="xl65" style="width: 69pt;" width="92">Project Name</td> <td class="xl65" style="width: 51pt;" width="68">Status</td> <td style="vertical-align: top;">
</td><td class="xl65" style="width: 48pt;" width="64">Hours</td> <td class="xl65" style="width: 48pt;" width="64">Expense</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">1111</td> <td class="xl65">AAA</td> <td class="xl65">Approved</td> <td style="vertical-align: top;">
</td><td class="xl65">56</td> <td class="xl65">34534</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">1111</td> <td class="xl65">AAA</td> <td class="xl65">Completed</td> <td style="vertical-align: top;">
</td><td class="xl65">565</td> <td class="xl65">754734</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">2222</td> <td class="xl65">BBB</td> <td class="xl65">On Hold</td> <td style="vertical-align: top;">
</td><td class="xl65">34</td> <td class="xl65">646734</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">2222</td> <td class="xl65">BBB</td> <td class="xl65">Completed</td> <td style="vertical-align: top;">
</td><td class="xl65">56</td> <td class="xl65">84843</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">3333</td> <td class="xl65">CCC</td> <td class="xl65">Completed</td> <td style="vertical-align: top;">
</td><td class="xl65">645</td> <td class="xl65">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">4444</td> <td class="xl65">DDD</td> <td class="xl65">Completed</td> <td style="vertical-align: top;">
</td><td class="xl65">
</td> <td class="xl65">45345</td> </tr> </tbody></table>
The fields PID, Project Name and Status are common for both the tables. Using this we need to match and merge the values.
Note that even if there is no match, those values should also be present in the output table.
I tried to establish a connection and write a query.
Below is the code
Set cnConn = CreateObject("ADODB.Connection")
With cnConn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=" & strAppPath & strPlanViewOutputFile & ";Extended Properties=Excel 8.0;"
.Open
End With
strQuery = "SELECT PlanView.[PV ID], PlanView.[Project Name], PlanView.[Status], PlanView.[Expense], Actuals.[Hours] FROM [Sheet5$B2:L10] PlanView, [Sheet4$B1:H10] Actuals Where PlanView.[PV ID] = Actuals.[PV ID] And PlanView.[Project Name] = Actuals.[Project Name] And PlanView.[Status] = Actuals.[Status]"
Set rsRecordSet = CreateObject("ADODB.Recordset")
With rsRecordSet
.ActiveConnection = cnConn
.Source = strQuery
.Open
End With
If rsRecordSet.EOF = True And rsRecordSet.BOF = True Then
GoTo TakeNextRecord
End If
counter = 2
rsRecordSet.MoveFirst
Do Until rsRecordSet.EOF = True
xlTargetWrkSht.Cells(counter, 1) = Trim$(rsRecordSet("PV ID").Value)
xlTargetWrkSht.Cells(counter, 2) = Trim$(rsRecordSet("Project Name").Value)
xlTargetWrkSht.Cells(counter, 3) = Trim$(rsRecordSet("Status").Value)
counter = counter + 1
rsRecordSet.MoveNext
Loop
The problem i face here is the query i form
I get an error if i add the where clause. If i remove the where clause, it works. For example, below code works.
strQuery = "SELECT PlanView.[PV ID], PlanView.[Project Name], PlanView.[Status], PlanView.[Expense] FROM [Sheet5$B2:L10] PlanView, [Sheet4$B1:H10] Actuals
Please let me know what can be done. Its not necessary that we need to use the connection and query the tables. If there is any new approach, please let me know.
Any help will be hightly appreciated.
Thanks in advance,
Jaya
