Friday 21 June 2013

Excel Macro to read a LoadRunner results and write it to excel sheet | Load runner .MDB file to excel reports generation

Recently my client asked me to get the load runner test reports in excel sheet or Gsheet for their convenience. To develop this i have written an excel macro that reads load runner result file.mdb and generate reports in the excel sheet

Here are the steps:
  1. Open a new or existing Excel workbook, press ALT + F11 to open the Visual Basic Editor.
  2. In the Visual Basic Editor, select Insert > Module to create a new module.
  3. In the new module, define a function that will read the LoadRunner result file. 
Function LoadLRResultFile(FileName As String) As Object
    Dim db As Object
    Set db = CreateObject("DAO.DBEngine.36")
    Set LoadLRResultFile = db.OpenDatabase(FileName)
End Function

This function uses the DAO library to open the LoadRunner result file and returns a reference to the database object. Define a subroutine that will generate the report.

Sub GenerateReport()
    Dim db As Object
    Set db = LoadLRResultFile("C:\path\to\LoadRunner result file.mdb")
    ' TODO: Generate report
    db.Close
End Sub

This subroutine calls the LoadLRResultFile function to open the LoadRunner result file, generates the report, and closes the database.

To generate the report, you can use SQL queries to retrieve data from the LoadRunner result tables and write the results to an Excel worksheet. 

Sub GenerateReport()
    Dim db As Object
    Set db = LoadLRResultFile("C:\path\dev\LoadRunner result file.mdb")
    
    Dim rs As Object
    Set rs = db.OpenRecordset("SELECT * FROM Summary")
    
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets.Add
    
    Dim i As Long
    For i = 0 To rs.Fields.Count - 1
        ws.Cells(1, i + 1).Value = rs.Fields(i).Name
    Next
    
    ws.Range("A2").CopyFromRecordset rs
    
    rs.Close
    db.Close
End Sub

This code generates a report by retrieving data from the Summary table and writing it to a new worksheet. The first row of the worksheet contains the column names, and the remaining rows contain the data. Finally, save the macro and run it to generate the report.