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:
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.
- In the left tab select Form, right click and create new form
- 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 StringServer = “Sql server Name”
db = “Database Name”
user = “Username”
pwd = “Password“sConn = “Provider=sqloledb;Server=” & Server & “;Database=” & db & “;User Id=” & user & “;Password=” & pwd & “”fSQLStringConnection = sConnEnd 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 StringSet cn = CreateObject(“ADODB.Connection”)
cn.CommandTimeout = 300sConn = fSQLStringConnectioncn.Open sConnSet cmd = CreateObject(“ADODB.Command”)
cmd.ActiveConnection = cn
cmd.CommandText = “Stored Procedure Name”
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Refresh
cmd.Parameters(1).Value = NomeUtenteSet rs = CreateObject(“ADODB.Recordset”)
Set rs = cmd.Execute()
Set rs = Nothingcn.Close
Set cn = NothingEnd 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 StringSet cn = CreateObject(“ADODB.Connection”)
cn.CommandTimeout = 300sConn = fSQLStringConnectioncn.Open sConnssql = “SELECT Stato FROM dbo.TableName“
Set rs = CreateObject(“ADODB.Recordset”)
rs.Open ssql, cnCheckStatoFromSQL = rs.Fields(“Column Name“)
Sheets(“Parametri”).Range(“A10”).CopyFromRecordset rs
rs.CloseSet rs = Nothing
cn.Close
Set cn = NothingEnd 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.
