PREPARE JMETER REPORTS USING EXCEL MACRO’S

First of all you need to enable macro developer option in excel.For this follow the below steps
1. Click on Office button which is loacted at the top left corner
2. Go to Excel Options
3. Go to the tab Popular
4. Check “Show Developer Tab in Ribbon” (3rd Check box)





5. Then click ok.
6. You can see the new developer tab in the excel sheet.Click Developer tab a new Visual Basic, a new Microsoft Visual Basic for applications window will be displayed





7. ClickInsert Module, and enter the following code into the Module and save


Lets say for example
Convert milliseconds to seconds
Sub ConvertTOSec()
Dim cnt As Integer
cnt = ActiveSheet.UsedRange.Rows.Count
For Each c In ActiveSheet.Range(“C2:J” & cnt)
c.Value = c.Value / 1000
Next c
End Sub

Formatting specified cells

Sub TC_formattingCells ()
Columns(“C:J”).Select
Selection.NumberFormat = “0.00″
Range(“I:I,J:J”).Select
Range(“J1″).Activate
Selection.Delete Shift:=xlToLeft
Range(“M8″).SelectActiveWorkbook.Save
End Sub

8. Click on Macro option
9. Select macro and Run


Here is the sample Jmeter report



Output of sample macro report

Converted milliseconds to seconds.



2.) Combine multiple workbooks to one master workbook using macro

1. Put all the workbooks that you want to combine into the same directory. See screenshot:



2. Launch an Excel file that you want to combine other workbooks into.

3. Click Developer > Visual Basic, a new Microsoft Visual Basic for applications window will be displayed, clickInsert > Module, and input the following code into the Module:


Sub GetSheets()

Path = “D:\AggreageResults_13012014 \”

Filename = Dir(Path & “*.csv”)

Dim i As Integer

Do While Filename <> “”

Workbooks.Open Filename:=Path & 

Filename, ReadOnly:=True
i = i + 1
For Each Sheet In ActiveWorkbook.Sheets
Sheet.Copy After:=ThisWorkbook.Sheets(i)
ThisWorkbook.Sheets.Add
Next Sheet
Workbooks(Filename).Close
Filename = Dir()
Loop
End Sub

 In the above code, you can change the path to the one that you are using.
Then click button to run the code, and all of the worksheets within the workbooks have been merged into the master workbook.

No comments:

Post a Comment