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>

 

[Andrew Poulsom]

Welcome to the Board.

It looks like you want a pivot table. If you choose Data|PivotTable and PivotChart Report, check External data source and click Next, you can build your use MS Query to build your SQL by clicking Get Data at Step 2. If you turn on the macro recorder before you start you will get some VBA code.

 

[apissharif]

Hi Andrew,

Thanks for your reply, I'll try it and tell you the result.
Is there any link to explain about pivot table? I know the term 'Pivot Table' but I don't know what it is about.

Thanks.

 

[Andrew Poulsom]

Look here:

http://peltiertech.com/Excel/Pivots/pivottables.htm

 

[apissharif]

I've got problem,

When using Microsoft Query, I've selected which column to use from the table using Query Wizard, but when I'm click Finish button this message appeared
"Incorrect syntax near the keyword 'IDENTITY' ". I'm try to reselect the column but the problem still occured.

I've no idea what to do now.

 

[Andrew Poulsom]

Can you post the SQL please?

 

[apissharif]

Sorry, I don't know how to post the SQL through this forum.

I tried to create Pivot Table from your instruction earlier.
First I goto Data->Pivot Table and Pivot Chart Report...
Then I'm choosed External Data Source and click Next,
After that I'm clicked to Get Data and choose <New Data Source>
Then I'm select SQL Server and write down the server name and choose the database name.
Also I tick at Use the Query Wizard to create/edit queries
After the wizard appeared, I'm just select the column that I want, for this case I'm selected column 'Result' and 'Timestamp', then for next page I sort by 'Timestamp' and lastly choose 'Return Data to Microsoft Excel' and click button 'Finish'. But what happen is the message 'Incorrect syntax near the keyword 'IDENTITY'' appeared.

 

[Andrew Poulsom]

Instead of clicking 'Return Data to Microsoft Excel' click 'View data or edit data in Microsoft Query'. Click the SQL button on the Toolbar and a window will appear. Select the text and press Ctrl+c. Then Ctrl+v into your reply on the Board.

 

[apissharif]

I've done that, but still get the same error. Even if I'm choose the last one also got the same error.
Is it bugs in Microsoft Query?

 

[Andrew Poulsom]

Sorry I don't think I can help any more without seeing the SQL. The erro implies that you are trying to add an identity:

http://www.tek-tips.com/viewthread.cfm?qid=1012903&page=7

What happens if you uncheck 'Use the Query Wizard to create/edit queries' on the Choose Data Source dialog and proceed from there?