Running SQL Script in Excel Macro

Question

I have a question regarding Macros in Excel and how to use them. I recently created a SQL Query that will hit several TFS databases to extract data. I would simply like to run this script from excel such that the output of the script is automatically published in excel with the individual column headers. I have seen this done before in excel by using VB Macros.

As a workaround, I created a small bat file to run sqlcmd, check a sql file containing the script, and finally push the output to a csv file. The only problem with this, is that the CSV's output pushes the results into a single line and does not separate them with headers.

Current code for batfile:

sqlcmd -E -S TFSServer\TFS2010 -i C:\test\testquery.sql -o C:\test\test.csv

Are you saying all the SQL rows are compressed into a single line? Or that each row is in a single column? — LittleBobbyTables - Au Revoir
– LittleBobbyTables - Au Revoir, Commented Jul 25, 2012 at 15:14
@LittleBobbyTables, Sorry about that, I meant to say that each row is a single column. — Frankie C
– Frankie C, Commented Jul 25, 2012 at 15:28
You should be able to just do Data > Text to Columns after the fact. — LittleBobbyTables - Au Revoir
– LittleBobbyTables - Au Revoir, Commented Jul 25, 2012 at 15:29

Kevin · Accepted Answer · 2012-07-25 15:18:52Z

1

Why not use an ssis package or ssrs report for dataextraction? Macros are so... last century.

If you insist on using a macro, here is an example:

Dim con As ADODB.Connection, _
cmd As ADODB.Command, _
rs As ADODB.Recordset, _
strSQL As String, _
i As Integer

strSQL = "EXEC SP_StoredProcedureToGetData"

Set con = New ADODB.Connection
con.Open "Provider=SQLOLEDB;Data Source=SQLSERVER;" & _
    "Initial Catalog=DATABASE;User ID=USERNAME;Password=PASSWORD"

Set cmd = New ADODB.Command
With cmd
    .ActiveConnection = con
    .CommandText = strSQL
    .CommandType = adCmdStoredProc
End With

Set rs = New ADODB.Recordset
With rs
    .CursorLocation = adUseClient
    .CursorType = adOpenKeyset
    .LockType = adLockBatchOptimistic
End With
Set rs = cmd.Execute

Cells(1, 1) = "Column1"
Cells(1, 2) = "Column2"
Cells(1, 3) = "Column3"
Cells(1, 4) = "Column4"
Cells(1, 5) = "Column5"
Cells(2, 1).CopyFromRecordset rs

Set con = Nothing
Set cmd = Nothing
Set rs = Nothing

answered Jul 25, 2012 at 15:18

Kevin

2055 silver badges22 bronze badges

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

1 Comment

Frankie C Over a year ago

I have a question about your proposed answer. It seems that this code requires you to have the script loaded onto the sql server so it can simply be run, is that correct? If so, that is not possible because we do not want to load any additional stored procedures as this is a internal production server. I was looking to be able to run the script from a client machine/account with read only access.

Collectives™ on Stack Overflow

Running SQL Script in Excel Macro

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related