How to fire SQL query on Excel sheet?

[shriqtp]

Hi,

I would like to fire SQL queries through QTP...like
(1) Sorting, Count, Avg.....etc and also queries like
(2) Union, UnionAll....etc on Excel sheet columns.

Can anyone please suggest suitable code/script for that.


Thanks
Shrini

[Saket]

see if this post helps you
https://www.learnqtp.com/qtp-database-ch...ving-data/

[shriqtp]

Hi Saket,

Thanks for the link. Yes it will surely helps in getting idea about how to fire SQL query on databases. But my requirement belongs to firing queries like Avg, Count, Sort....and more importantly Union and UnionAll.

So request you to please give a script with examples i.e firing above mentioned queries; so that i and many of LearnQTP family members will get exact idea about it.

Thanks for your gr8 support in advance...

Regards,
Shrini

[shriqtp]

Hi,

Can anybody please resolve my above query....its urgent.

Your help is much appriciated.

Thanks
Shrini

[shriqtp]

Anybody please reply....

[Saket]

Hi Sasmita,

External links are not allowed here, if you know the answers, please post it here.
regarding this thread - Shriqtp want to know about firing query with excel functions like Avg, Count, Sort, which I think is not possible to use directly using ADO.

[QTPLearn]

Hi

Please check this code:

Code:

Set objConnection = CreateObject("ADODB.Connection") Set objRecordSet = CreateObject("ADODB.Recordset") objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source="EMP";Extended Properties=""Excel 8.0;HDR=Yes;"";" strQuery = "Select * From Emp where EmpID IS NOT NULL" objRecordSet.Open strQuery, objConnection, adOpenStatic, adLockOptimistic, adCmdText intColCnt = objRecordSet.Fields.Count

Hope this will help you.