CODEWITHSUNDEEP
mysqlsql-servervbaexcel
nishit dey
nishit dey

Reputation: 458

Inserting the SQL query result in Excel

I am using the below SQL query which is returing multiple rows and coloumns in result, when i am trying to pass those in excel i am not able to do it

Code:

Set com = QCConnection.Command
com.CommandText = "select RN_TESTER_NAME as 'Tester, sum(case when RN_STATUS='Passed' then cnt else 0 end), sum(case when RN_STATUS='Failed' then cnt else 0 end)from (select count(*) cnt, RN_STATUS,RN_TESTER_NAME from  RUN Where RN_Execution_Date = '5/17/2017'group by RN_STATUS,RN_TESTER_NAME) as RUN group by RN_TESTER_NAME order by RN_TESTER_NAME"
Set RecSet = com.Execute
For Col = 0 To RecSet.ColCount - 1
   Sheet4.Cells(Col + 1, 7) = RecSet.FieldValue(Col)
Debug.Print Sheet4.Cells(Col + 1, 7)
Next

I am getting the result as the below User1
2
3

I want the result in this manner (please note this is the way qc is showing me the result
User1 2 3 (2 and 3 are the values)
User2 1 0
User3 6 3

Also tried to use, but no help

For Col = 0 To RecSet.ColCount - 1
   Sheet4.Cells(Col + 1, 7) = RecSet.FieldValue(Col)
   Sheet4.Cells(Col + 1, 7) = RecSet.FieldValue(Col+1)
Next

EDIT : Please note i am not able to get the User2 and User3 data in that query, but when tested in QC it is showing me the data and there is the data present. Thanks

Upvotes: 2

Views: 7445

Answers (2)

nishit dey
nishit dey

Reputation: 458

Got the solution, I was using wrong loop.

Set com = QCConnection.Command
com.CommandText = "select RN_TESTER_NAME as 'Tester', sum(case when RN_STATUS='Passed' then cnt else 0 end), sum(case when RN_STATUS='Failed' then cnt else 0 end)from (select count(*) cnt, RN_STATUS,RN_TESTER_NAME from  RUN Where RN_Execution_Date = '5/17/2017'group by RN_STATUS,RN_TESTER_NAME) as RUN group by RN_TESTER_NAME order by RN_TESTER_NAME"
Debug.Print com.CommandText
Set RecSet = com.Execute
x = 1
Do Until RecSet.EOR
   Sheet4.Cells(x, 7) = RecSet.FieldValue(Col)
   Sheet4.Cells(x, 8) = RecSet.FieldValue(Col + 1)
   Sheet4.Cells(x, 9) = RecSet.FieldValue(Col + 2)
RecSet.Next
x = x + 1
Loop

Upvotes: 0

iamdave
iamdave

Reputation: 12243

Don't try to build your results table yourself in VBA, just use the built in functionality already present within Excel. Click the Data tab on the Ribbon, then From Other Sources and then From SQL Server:

Follow the steps in the wizard that pops up and you will have your data. If you want to use a specific query, just select a small table in the wizard and then change the connection properties of the Data Table via the Import Data window that pops up at the end of the wizard:

Change the Command Type to SQL and then enter your script into the Command Text:

And you have your table!

If you want to be able to change the columns or data that is returned, you can change the Command Text using VBA and base the various elements of the SQL script on values held within your Worksheet.

Upvotes: 3

Related Questions