How to fire SQL query on Excel sheet?

How to fire SQL query on Excel sheet? - Printable Version

+- Micro Focus QTP (UFT) Forums (https://www.learnqtp.com/forums)
+-- Forum: Micro Focus UFT (earlier known as QTP) (https://www.learnqtp.com/forums/Forum-Micro-Focus-UFT-earlier-known-as-QTP)
+--- Forum: VB Scripting/Descriptive Programming (https://www.learnqtp.com/forums/Forum-VB-Scripting-Descriptive-Programming)
+--- Thread: How to fire SQL query on Excel sheet? (/Thread-How-to-fire-SQL-query-on-Excel-sheet)

How to fire SQL query on Excel sheet? - shriqtp - 06-22-2010

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

RE: How to fire SQL query on Excel sheet? - Saket - 06-23-2010

see if this post helps you
https://www.learnqtp.com/qtp-database-checkpoint-part3-retrieving-data/

RE: How to fire SQL query on Excel sheet? - shriqtp - 06-24-2010

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

RE: How to fire SQL query on Excel sheet? - shriqtp - 07-14-2010

Hi,

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

Your help is much appriciated.

Thanks
Shrini

RE: How to fire SQL query on Excel sheet? - shriqtp - 07-19-2010

Anybody please reply....

RE: How to fire SQL query on Excel sheet? - Saket - 07-19-2010

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.

RE: How to fire SQL query on Excel sheet? - QTPLearn - 08-06-2010

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.