Inserting the SQL query result in Excel

Question

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

Are you trying to build the results table manually? Just use the Get Data From SQL Server wizard and it will take care of all of this for you. — iamdave
– iamdave, Commented May 23, 2017 at 8:59
Hi @iamdave, yes we have a customized table structure so we Get Data will not work for ex User one is called in A1, and the result is called in E1 and G1. — nishit dey
– nishit dey, Commented May 23, 2017 at 9:04
Is this of any help msdn.microsoft.com/de-de/library/office/ff839240.aspx — Storax
– Storax, Commented May 23, 2017 at 9:10
Have a look at my answer below. Build the table using the built in Excel functionality and if necessary, simply change the SQL script held in the connection using your VBA. Do not try to build your table yourself in VBA. It is a waste of time and effort. — iamdave
– iamdave, Commented May 23, 2017 at 9:13
Excel can load data from multiple data sources since the 1990s. You don't have to write a VBA macro to do this — Panagiotis Kanavos
– Panagiotis Kanavos, Commented May 23, 2017 at 9:14

iamdave · Accepted Answer · 2017-05-23 09:11:37Z

3

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.

answered May 23, 2017 at 9:11

iamdave

12.3k3 gold badges29 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

nishit dey Over a year ago

This is a HP ALM database, so this wont work for me :(

iamdave Over a year ago

@nishitdey Then why is this question tagged SQL-Server? Regardless, if you can connect to that database via ODBC, then yes this will work for you. Select the option From Microsoft Query in the From Other Sources button instead of From SQL Server. Even if that doesn't work, just attach the connection in your question to a Data Table (Click Insert --> Table on the Ribbon) in your Worksheet.

nishit dey · Accepted Answer · 2017-05-23 09:46:11Z

0

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

answered May 23, 2017 at 9:46

nishit dey

4581 gold badge7 silver badges22 bronze badges

Collectives™ on Stack Overflow

Inserting the SQL query result in Excel

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related