Create SQL Query in VBA and display result as table in Worksheet

[apissharif]

Hi,

I need the VBA script in excel that will do a SQL Query, for this case I need to select a value where there are 2-3 tags and between certain period then display the result as a table in a worksheet.

Example I have a database with 3 types of tags "Tag A", "Tag B", and "Tag C", and each tag have a value with different timestamp. the database looks like this:

No. Timestamp Tags Value
1 1-Jan-2010 Tag A 18
2 1-Jan-2010 Tag C 20
3 2-Jan-2010 Tag A 20
4 3-Jan-2010 Tag B 17
5 3-Jan-2010 Tag C 19
6 4-Jan-2010 Tag B 18
7 4-Jan-2010 Tag A 20
8 5-Jan-2010 Tag A 22
9 5-Jan-2010 Tag B 18
10 5-JAn-2010 Tag C 20

Now I want to create VBA script to select "Value" from Tag A, B, and C from 1 Jan to 4 Jan and the result at worksheet is like this:

Timestamps Tag A Tag B Tag C
1-Jan-2010 18 20
2-Jan-2010 20
3-Jan-2010 17 19
4-Jan-2010 20 18

Anybody got an idea how to do this? Thanks.


<table x:str="" style="border-collapse: collapse; width: 256pt;" border="0" cellpadding="0" cellspacing="0" width="341"><col style="width: 48pt;" width="64"> <col style="width: 97pt;" width="129"> <col style="width: 63pt;" width="84"> <col style="width: 48pt;" width="64"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; width: 48pt;" width="64" height="17">
</td> <td class="xl24" style="border-left: medium none; width: 97pt;" width="129">
</td> <td class="xl24" style="border-left: medium none; width: 63pt;" width="84">
</td> <td class="xl24" style="border-left: medium none; width: 48pt;" width="64">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt; border-top: medium none;" height="17">
</td> <td class="xl22" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl23" style="border-top: medium none; border-left: medium none;" x:num="39966.333333333336">
</td> <td class="xl22" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt; border-top: medium none;" height="17">
</td> <td class="xl22" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl23" style="border-top: medium none; border-left: medium none;" x:num="39965.333333333336">
</td> <td class="xl22" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt; border-top: medium none;" height="17">
</td> <td class="xl22" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl23" style="border-top: medium none; border-left: medium none;" x:num="39966.333333333336">
</td> <td class="xl22" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt; border-top: medium none;" height="17">
</td> <td class="xl22" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl23" style="border-top: medium none; border-left: medium none;" x:num="39965.333333333336">
</td> <td class="xl22" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt; border-top: medium none;" height="17">
</td> <td class="xl22" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl23" style="border-top: medium none; border-left: medium none;" x:num="39967.333333333336">
</td> <td class="xl22" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt; border-top: medium none;" height="17">
</td> <td class="xl22" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl23" style="border-top: medium none; border-left: medium none;" x:num="39965.333333333336">
</td> <td class="xl22" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt; border-top: medium none;" height="17">
</td> <td class="xl22" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl23" style="border-top: medium none; border-left: medium none;" x:num="39967.333333333336">
</td> <td class="xl22" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt; border-top: medium none;" height="17">
</td> <td class="xl22" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl23" style="border-top: medium none; border-left: medium none;" x:num="39965.333333333336">
</td> <td class="xl22" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt; border-top: medium none;" height="17">
</td> <td class="xl22" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl23" style="border-top: medium none; border-left: medium none;" x:num="39966.333333333336">
</td> <td class="xl22" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt; border-top: medium none;" height="17">
</td> <td class="xl22" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl23" style="border-top: medium none; border-left: medium none;" x:num="39967.333333333336">
</td> <td class="xl22" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt; border-top: medium none;" height="17">
</td> <td class="xl22" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl23" style="border-top: medium none; border-left: medium none;" x:num="39965.333333333336">
</td> <td class="xl22" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt; border-top: medium none;" height="17">
</td> <td class="xl22" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl23" style="border-top: medium none; border-left: medium none;" x:num="39966.333333333336">
</td> <td class="xl22" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt; border-top: medium none;" height="17">
</td> <td class="xl22" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl23" style="border-top: medium none; border-left: medium none;" x:num="39965.333333333336">
</td> <td class="xl22" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt; border-top: medium none;" height="17">
</td> <td class="xl22" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl23" style="border-top: medium none; border-left: medium none;" x:num="39966.333333333336">
</td> <td class="xl22" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt; border-top: medium none;" height="17">
</td> <td class="xl22" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl23" style="border-top: medium none; border-left: medium none;" x:num="39987.464699074073">
</td> <td class="xl22" style="border-top: medium none; border-left: medium none;">
</td> </tr> </tbody></table>

 

[apissharif]

It's working, what I do is I'm open the table using Microsoft SQL Studio and delete column 'Identity' and save it. Then I'm retried the MS Query and it's working fine.

But as I informed earlier I don't know how to post the SQL to you through this forum, could you guide me how to post it? I tried to look the icon for attachment but couldn't find it.

 

[Andrew Poulsom]

Just copy it from MS Query (highlight it and press Ctrl+c), then paste it into a reply using Ctrl+v.

 

[apissharif]

This is the SQL:

SELECT FP_DELV_RESULT.TAG, FP_DELV_RESULT.SAMPLING_DATE, FP_DELV_RESULT.RESULT
FROM FPDEV.dbo.FP_DELV_RESULT FP_DELV_RESULT
ORDER BY FP_DELV_RESULT.SAMPLING_DATE

I'm just can get it after I deleted the column 'Identity' from the table. Before I delete it, I cannot get the SQL.

I've success created the pivot table, but why I cannot get the value, instead I get the count of Result, when right click and select Field Setting there are no option of Result, instead there are Sum, Count, etc...

 

[apissharif]

Hi Andrew,

Is it another way instead of using Pivot Table? The pivot table result is meets the requirement but there are dropdown list at column and row header. It's like you can filter it, actually I don't need it.

 

[Andrew Poulsom]

You need Date in the row field, Tag in the column field and Sum of Result in the data field. If you want a hard copy you can copy the pivot table and Paste Special|Values somewhere else.

 

[apissharif]

How to insert/paste an image/snapshot through the post?

Earlier I've create a table in excel, then print screen it and paste it inside Paint. Then I select the table and cut it and intend to paste it here but nothing happen. Thanks

 

[Andrew Poulsom]

From the menu at the top of the Board click User CP then Edit Options. Under Miscellaneous Options at the bottom select Enhanced InterFace then Save Changes. Now you should be able to copy a range of cells on a worksheet and paste into a reply.