VBA Code- Extracting Database data into excel sheet using macro

Code to read the data from database and writing data in excel file using VB Macro and VBA.

'''' Naming the sub as auto_open will trigger the sub automatically on opening the file.

'''' We can create a sub with different name and assign macro to a control in the excel

''''file 

Sub auto_open()
'''' define the connection string to connect to the database, it can be a dsn created,

'''' or connection string to create to the database. Please refer connectionstrings.com
'''' to connect to different database using connection string.
connStr = "dsn=testthedb''
'''' Query to fetch data from the database
strGetEmployeeDetails = "select * from employee order by UserName asc;"
''''Provide reference of sheet to add data to in the current workbook. We can add a

''''sheet in excel, or refer to an external excel file/Sheet using excel.application.

''''In this example, Sheet Employee_Details exist in the current excel file
Set worksht = ThisWorkbook.Sheets("Employee_Details")
''''--------------------------
'''' Create database connection
   Set Conn = CreateObject("ADODB.Connection")
   Set rset = CreateObject("ADODB.Recordset")
   Conn.Open connStr
   rset.Open strGetEmployeeDetails, Conn, adOpenStatic

''''The recordset is stored in rset.
''''The first row of data is the column details of the query results

''''and is added in the results header

For i = 0 To rset.Fields.Count - 1
worksht.Cells(2, i + 2) = rset.Fields(i).Name
Next i

''''Copy the recorset data into excel file starting at row 3 and column 2
worksht.Cells(3, 2).CopyFromRecordset rset
rset.Close
Set rset = Nothing
ThisWorkbook.Save
Conn.Close
Set Conn = Nothing
End Sub

How to create a database checkpoint in QTP

A database checkpoint compares the values returned by SQL query during checkpoint execution. Below are the steps to create a database checkpoint in QTP

• Navigate to Insert>Checkpoint>Database Checkpoint.

• Define whether to define SQL statement manually or using Microsoft Query

• Select the database or query if previously defined as shown below.We select QT_Flight_32 database.Click on OK.

• Select the required columns/tables to be selected in SQL and click on next button.

• Define conditions for selected columns in the above step and define sorting criteria. Click Next>Next

• Click on finish

• Change the properties of database checkpoint and select whether to insert the checkpoint before or after current step and clcik on OK. A database checkpoint is created and inserted in the test.

Connecting to excel as database using Adodb in VBScript/QTP

We can connect to excel file using Adodb object in vbscript.  Following are the steps in working with excel as database.

1.       Create an instance of connection :

Set objConn = CreateObject("Adodb.connection")

2.       Create connection string to connect to excel database as follows

strCon = "Driver={Driver do Microsoft Excel(*.xls)};DriverId=790;Dbq=" & Workbook &";"

Here we have created a database for excel Workbook . The sheets in the workbook acts as table of the database.

3.       Connect to database as shown below:

objConn.open strCon

Now we are done with the connection to excel , we require SQL to run on the data to extract required information . 

4. Create a SQL as required as shown below:

strSQL =  "select name from [Table1$];"

Note here, we have to give sheet name acting as table within square bracket and with suffix “$” as shown above.

5.   Create a recordset as shown below and execute the query using recordset, all  the records based on query will be stored in recordset.

Set rs = CreateObject("ADODB.recordset")

rs.Open strSQL,objConn

6.       We can transfer the data extracted from query back in excel using copyrecordset method

XLSheet.range("A2").copyfromrecordset rs

Where XLSheet is the reference of required sheet in the excel  file and A2 is the location in sheet from which data will be pushed in the sheet.

Working with DataBase in QTP - Understanding Connection and Recordset object

QTP Interacts with database creating an instance of ADODB object using ADO. ADO is a Microsoft technology and stands for ActiveX Data Objects and is a programming interface to interact and access data from a database.

Connection Object

Before a database can be accessed by QTP, An object for database connection has to be established.

Set adocon = createobject(“Adodb.connection”)

Once the object is created, we need to set connection string to connect to the database. We can define a connection string with details of database including database provider, database, and user credentials for access.

Some useful methods and properties of connection object are as follows:

Properties:

adocon.connectionstring – This property sets or returns the details used to create a connection to a data source. Includes details of driver, database,username and password.

Strconnection = "Driver=… "Server=svrnme;uid=username;pwd=password;"

For e.g to connect to a database for excel the strConnection would be like:

 strcon = "Driver={Microsoft Excel Driver (*.xls)};Dbqls="+xfilename +";ReadOnly=0;"

For details of connection strings, see www.connectionstrings.com

adocon.ConnectionTimeout – this defines the time  to wait for a connection to be established.

adocon.provider – This sets or gets connection provider name.

adocon.state – gives status whether connection is on or off.

Methods

adocon.open – opens a database connection based on the connection string provided. 

adocon.Open connectionstring,userID,password,options

adocon.Execute – execute the sql statement provided

adocon.execute “Select * from table”

adocon.close – This closes the adodb connection.

RecordSet Object:

 Once a connection has been established, we can create recordset object to hold a set of record from database. A recorset consists of records and column

Set rs = createobject(“”Adodb.recordset”)

Some useful methods and properties of RecordSet Objects are as follows:

Properties:

BOF property returns True  if the current record position is before the first record in the Recordset,

EOF property returns True if the current record position is after the last record in the Recordset, otherwise it returns False. For a empty recordset,i.e no records in the recordset or empty recordset, value of BOF and EOF is false. So the property can be used in a loop to validate RecordSet does not return any records.

MaxCount Property returns the maximum value of records to be returned in a recordset.
rs.MaxCount = 20 will return 20 rows of data in recordset.

Methods:

rs.cancel – cancels an existing execution.

rs.clone – returns a clone of existing recorset and assigns to an object

set rsclone = rs.clone

rs.Close - closes instance of recordset

rs.open – opens a recordset based on query specified.

rs.open sqlquery, adocon

where sqlquery is query executed and adocon is connection object.

rs.move – moves the pointer in a recordset by specified count as defined in numrec
rs.move numrec, start.

Also  movenext,moveprevious, movefirst, movelast can be used to move to specified location in recordset.

rs.fields.count gives number of items in the fields collection.

rs.field.item(i) returns specified item from the collection.