AS-IS Scenario

Mr. Rick Rude (HR Executive) has to extract the employee data every day from MS SQL Server Database. He has to extract the data at the start of day and before leaving the day. He has to send 2 reports to the HR Manager one at the beginning of the day and the other at the end of the day. Report will contain how many employees are employed in a particular department at the start of the day and other report will show the number of employees in a particular department at the end of the day.

TO-BE Scenario

Normally he would have to open MS SQL Server Management Studio and write query to extract the data and then he will have to copy the employee data from MS SQL Server to excel. It is little bit time consuming and hence he takes the help of Macro to extract the data and paste in excel sheet. Within a matter of seconds the task gets completed.

Let’s take a look at the procedure to extract data from SQL Server to Excel.

Before writing code to extract data follow the below steps.
Steps to Create employee database, creating table and inserting records into the table.

Step 1. Open SQL Server Management Studio
Step 2. Create employees database by executing the below command.
CREATE DATABASE employees;
Step 3. Create employees Table by executing the below command.

CREATE TABLE employees (
employee_id INT IDENTITY(1,1) PRIMARY KEY,
first_name VARCHAR (25) DEFAULT NULL,
last_name VARCHAR (25) NOT NULL,
email VARCHAR (120) NOT NULL,
phone_number VARCHAR (15) DEFAULT NULL,
hire_date DATE NOT NULL,
job_id INT NOT NULL,
salary DECIMAL (10, 2) NOT NULL,
manager_id INT DEFAULT NULL,
department_id INT DEFAULT NULL,
);

Step 4. Insert records in the employees table.
SET IDENTITY_INSERT employees ON;
INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (1,’Steve’,’Smith’,’steve.smith@gmail.com’,’912.123.4567′,’1987-06-17′,4,24000.00,NULL,9);
INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (2,’Heena’,’Kuchhar’,’heena.kuchhar@gmail.com’,’912.123.4568′,’1989-09-21′,5,17000.00,100,9);
INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (3,’Lexi’,’Cash’,’lexi.cash@gmail.com’,’912.123.4569′,’1993-01-13′,5,17000.00,100,9);
INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (4,’Alex’,’Wales’,’alex.wales@gmail.com’,’590.423.4567′,’1990-01-03′,9,9000.00,102,6);
INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (5,’Bruce’,’Arnold’,’bruce.arnold@gmail.com’,’590.423.4568′,’1991-05-21′,9,6000.00,103,6);
INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (6,’Steve’,’Austin’,’steve.austin@gmail.com’,’590.423.4569′,’1997-06-25′,9,4800.00,103,6);
INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (7,’Kallu’,’balla’,’kallu.balla@gmail.com’,’590.423.4560′,’1998-02-05′,9,4800.00,103,6);
INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (8,’Diana’,’Pretty’,’diana.pretty@gmail.com’,’590.423.5567′,’1999-02-07′,9,4200.00,103,6);
INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (9,’Nancy’,’Wheeler’,’nancy.wheeler@gmail.com’,’912.124.4569′,’1994-08-17′,7,12000.00,101,10);
INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (10,’Daniel’,’Smith’,’daniel.smith@gmail.com’,’912.124.4169′,’1994-08-16′,6,9000.00,108,10);
INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (11,’John’,’Chin’,’john.chin@gmail.com’,’912.124.4269′,’1997-09-28′,6,8200.00,108,10);
INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (12,’Ismael’,’Jana’,’ismael.jana@gmail.com’,’912.124.4369′,’1997-09-30′,6,7700.00,108,10);
INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (13,’Patrick’,’Rose’,’patrick.rose@gmail.com’,’912.124.4469′,’1998-03-07′,6,7800.00,108,10);
INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (14,’Mario’,’Pope’,’mario.pope@gmail.com’,’912.124.4567′,’1999-12-07′,6,6900.00,108,10);
INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (15,’Danny’,’Ralph’,’danny.ralph@gmail.com’,’912.127.4561′,’1994-12-07′,14,11000.00,100,3);
INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (16,’Alexa’,’Khoo’,’alexa.khoo@gmail.com’,’912.127.4562′,’1995-05-18′,13,3100.00,114,3);
INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (17,’Sheila’,’Leila’,’sheila.leila@gmail.com’,’912.127.4563′,’1997-12-24′,13,2900.00,114,3);
INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (18,’Tiger’,’Wood’,’tiger.wood@gmail.com’,’912.127.4564′,’1997-07-24′,13,2800.00,114,3);
INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (19,’Guy’,’Peter’,’guy.peter@gmail.com’,’912.127.4565′,’1998-11-15′,13,2600.00,114,3);
INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (20,’Karen’,’Lee’,’karen.lee@gmail.com’,’912.127.4566′,’1999-08-10′,13,2500.00,114,3);
INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (21,’Matthew’,’Wade’,’matthew.wade@gmail.com’,’650.123.1234′,’1996-07-18′,19,8000.00,100,5);
INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (22,’Adam’,’Cole’,’adam.cole@gmail.com’,’650.123.2234′,’1997-04-10′,19,8200.00,100,5);
INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (23,’Pam’,’aniston’,’pam.aniston@gmail.com’,’650.123.3234′,’1995-05-01′,19,7900.00,100,5);
INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (24,’Shakira’,’Voll’,’shakira.voll@gmail.com’,’650.123.4234′,’1997-10-10′,19,6500.00,100,5);
SET IDENTITY_INSERT employees OFF;
Step 5. Open Employee_Data.xlsm excel file.
Step 6. Press Alt+F11
Step 7. Go to Insert Menu
Step 8. Select Module
Step 9. Module will be inserted
Step 10. Select Module 1
Step 11. Go to Properties Window
Step 12. Change the name to ImportDataSQL (name)

Important Note – We have to activate the Microsoft ActiveX Data Objects 6.1 Library. I am using MS Office 2013 so it will differ according to the MS Office Version.
Step 13. Go to Visual Basic (Alt+F11) Tools References Select Microsoft ActiveX Data Objects 6.1 Library Click on OK

Step 14. Type the below code in the Code Window.
Note- If there is user id and password enter in the below code (‘StrConn = StrConn & “uid=user,pwd=” ///enter the user id and password if you have set up any///)
Option Explicit
Dim Conn As ADODB.Connection
Dim Rst As ADODB.Recordset
Sub RunReport()
Dim Server_Name As String
Dim Database_Name As String
Dim SQL As String
‘You can edit the servername
Server_Name = “SENDIL-PCSQLEXPRESS”
Database_Name = “employees”
SQL = “select * from employees”
Call Connect_SQLServer(Server_Name, Database_Name, SQL)
End Sub
Sub Connect_SQLServer(ByVal Server_Name As String, ByVal Database_Name As String, ByVal SQL_Statement As String)
Dim StrConn As String
Dim WsReport As Worksheet
Dim Col As Integer
StrConn = “Provider=SQLOLEDB;”
StrConn = StrConn & “Server=” & Server_Name & “;”
StrConn = StrConn & “Database=” & Database_Name & “;”
StrConn = StrConn & “Trusted_Connection=yes;”
‘StrConn = StrConn & “uid=user,pwd=” ///enter the user id and password if you have set up any///
Set Conn = New ADODB.Connection
With Conn
.Open ConnectionString:=StrConn
.CursorLocation = adUseClient
End With
Set Rst = New ADODB.Recordset
With Rst
.ActiveConnection = Conn
.Open Source:=SQL_Statement
End With
Set WsReport = ThisWorkbook.Worksheets.Add
With WsReport
For Col = 0 To Rst.Fields.Count – 1
.Cells(1, Col + 1).Value = Rst.Fields(Col).Name
Next Col
.Range(“A2”).CopyFromRecordset Data:=Rst
End With
Set WsReport = Nothing
Call CloseObject
End Sub
Private Sub CloseObject()
If Rst.State <> 0 Then Rst.Close
If Conn.State <> 0 Then Conn.Close
Set Rst = Nothing
Set Conn = Nothing
MsgBox “Employee data imported to excel”
End Sub

Step 15. Go to Employee_Data.xlsm
Step 16. Go to Developer Tab
Step 17. Go to Insert in Controls Section
Step 18. Select Button (Form Control)

Step 19. Place the Button inside the excel sheet.
Step 20. Right click on the Button and select Edit Text
Step 21. Change the Text to Extract Data from SQL Server
Step 22. Right Click on the Button
Step 23. Select Assign Macro and Select RunReport
Step 24. Click on RunReport Button to run the Macro.