CODEWITHSUNDEEP
sqlexcelvba
Frankie C
Frankie C

Reputation: 349

Running SQL Script in Excel Macro

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

Upvotes: 0

Views: 9590

Answers (1)

Kevin
Kevin

Reputation: 205

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

Upvotes: 1

Related Questions