How to use EXCEL to display SQL Server 2005 Query Result?

Question

I have 50 databases in SQL Server 2005 and each database has the same table that I use to pull data from. I wrote SQL with cursor in it so that it loops through all the database specified. I got the result in the 50 different result panes. My requirement is to import and display the results in 50 different sheets in the excel spreadsheet. My question: Is there a way in the excel that I can use so that I can display the results in 50 different sheets automatically?

Thank you

score 0 · Answer 1 · edited May 23 '17 at 10:32

0

Yes. You can achieve this by using VBA. I'd record a macro where you import one of these tables into one (newly added) worksheet. You can then change this newly recorded macro to create all sheets and queries automatically.

Edit: To get started, you can read through the resources provided in the threads: How to get started with Visual Basic for Applications? or Where do I get started with VBA and macros programming in Word 2007? (which actually also links to a video).

edited May 23 '17 at 10:32

Community

1
1

answered May 23 '14 at 14:57

MP24

3,110
21
23

Hi MP24, Thank you. Could you please provide me some examples or relevant tutorials so that I can follow it and accomplish my aim. – poshan May 23 '14 at 15:06

score 0 · Answer 2 · answered May 23 '14 at 15:11

You can create temporary table

create table #t (id int, col1 varchar, col2 varchar)

In your cursor you can insert data from 50 different databases into it

insert #t(id, col1, col2)
select id, col1, col2
from SomeDatabase.dbo.tt

And then select all data from it to one pane.

select * from #t

Don't let data manipulate you. Manipulate it yourself :)

score 0 · Answer 3 · answered May 23 '14 at 17:32

I use the following script a lot to spew out MSSQL Data to an Excel sheet. Maybe this is what you are looking for.

Sub ConnectSqlServer()

Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sConnString As String

' Create the connection string.
sConnString = "Trusted_Connection=yes;Database=database;Server=sql;Driver={SQL Server}"

'conn.Open connString
' Create the Connection and Recordset objects.
Set conn = New ADODB.Connection
Set rs = New ADODB.Recordset

' Open the connection and execute.
'LOOP OVER SHEETS WITH DIFFERENT TABLES
conn.Open sConnString
For i = 1 To 50

Set rs = conn.Execute("SELECT * FROM TABLE")

' Check we have data.
If Not rs.EOF Then
    ' Transfer result.
    Sheets(i).Range("A2").CopyFromRecordset rs
' Close the recordset
    rs.Close
Else
    MsgBox "Error: No records returned.", vbCritical
End If
Next i
'END LOOP
' Clean up
If CBool(conn.State And adStateOpen) Then conn.Close
Set conn = Nothing
Set rs = Nothing

End Sub

How to use EXCEL to display SQL Server 2005 Query Result?

3 Answers3