VBA: connect you Excel to Sql Server

 Sometimes happen that Customer or ourself wants to do something with sql without using sql. it’s a strange idea but happens!
For Example if we want to launch a Reporting service Manual Scheduled, or a stored procedure but we don’t want to access on virtual machine, open sql server and launch it.
We can do it with excel and vba!

First of all we have to open VBA. To do that we have to go to the development tab and select visual basic. After that we have to check if we have installed  “Microsoft ActiveX Data Object 6.1 Library”. to check it we have to select tools on menu tab of visual studio and after click references:
Immagine 028.png
If Exist Check it and click ok, if not exist  suggest you to go on this Support Link of Microsoft where you can select and install a Windows update that install the correct patch.
Now we can connect excel to sql, to do that we create a new VBA Sub.
  1. In the left tab select Form, right click and create new form
  2. IN the form copy this fuction
Function fSQLStringConnection()
Dim sConn As String
Dim Server As String
Dim db As String
Dim user As String
Dim pwd As String
Server = “Sql server Name
db = “Database Name
user = “Username
pwd = “Password
sConn = “Provider=sqloledb;Server=” & Server & “;Database=” & db & “;User Id=” & user & “;Password=” & pwd & “”
fSQLStringConnection = sConn
End Function
After that we have to change the paramenter Server, db, user, psw with your information. This Function open a connectio direclty to a db on your server.
After that if you want to start a stored procedure you have to create this sub:
Sub WriteToSQLServer()
Dim cn As ADODB.Connection
Dim sConn As String
Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset
Dim ssql As String
Set cn = CreateObject(“ADODB.Connection”)
cn.CommandTimeout = 300
sConn = fSQLStringConnection
cn.Open sConn
Set cmd = CreateObject(“ADODB.Command”)
cmd.ActiveConnection = cn
cmd.CommandText = “Stored Procedure Name
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Refresh
cmd.Parameters(1).Value = NomeUtente
Set rs = CreateObject(“ADODB.Recordset”)
Set rs = cmd.Execute()
Set rs = Nothing

cn.Close
Set cn = Nothing
End Sub
Change Stored Procedure name and it works correctly. Otherwise if you want read a table you have to use:
Function CheckStatoFromSQL()
Dim cn As ADODB.Connection
Dim sConn As String
Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset
Dim ssql As String
Set cn = CreateObject(“ADODB.Connection”)
cn.CommandTimeout = 300
sConn = fSQLStringConnection
cn.Open sConn

ssql = “SELECT Stato FROM dbo.TableName

Set rs = CreateObject(“ADODB.Recordset”)
rs.Open ssql, cn
CheckStatoFromSQL = rs.Fields(“Column Name“)
Sheets(“Parametri”).Range(“A10”).CopyFromRecordset rs
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Function
Change table name, column name and reference to sheet paramenter and you can read a value and write it in a cell of your excel.
Annunci

Rispondi

Inserisci i tuoi dati qui sotto o clicca su un'icona per effettuare l'accesso:

Logo WordPress.com

Stai commentando usando il tuo account WordPress.com. Chiudi sessione / Modifica )

Foto Twitter

Stai commentando usando il tuo account Twitter. Chiudi sessione / Modifica )

Foto di Facebook

Stai commentando usando il tuo account Facebook. Chiudi sessione / Modifica )

Google+ photo

Stai commentando usando il tuo account Google+. Chiudi sessione / Modifica )

Connessione a %s...