How I may help
LinkedIn Profile Email me!
Call me using Skype client on your machine

Reload this page LoadRunner Script Generation Spreadsheet

This spreadsheet writes VuGen script code for use within Mercury LoadRunner for performance testing. This is a companion to my pages on Vu Scripting, performance monitoring, and tuning.

 

Topics this page:

  • The Annoyance
  • My Solution
  • Get It
  • Spreadsheet Columns
  • Work It
  • Sample Scripts Gen'd
  • Make It Your Own
  • Engine Parameters
  • Your comments???
  •  

    RSS XML feed for load testers RSS preview for load testers Site Map List all pages on this site 
    About this site About this site 
    Go to first topic Go to Bottom of this page


    Set screen The Annoyance

      When working with a large number of screens for an application to be automated, it is often time consuming to record each screen and then tweak the scriptanother page on this site with transaction identifiers, confirmation text, etc.

      The other problem stems from my desire to keep a unique and sequential identifier of each transaction/screen that reflects the hierarchy of my test plan.

      It is too time consuming to go through the whole script to renumber items by hand each time I add or remove an item or to make the same changes to make changes that applies to all items.

      "Wall of Light Blue, 1999" (Oil on linen) by Sean Scully
    Go to Top of this page.
    Previous topic this page
    Next topic this page

    Set screen My Solution

      So I came up with an Excel spreadsheet containing VBA macros.

      Get it download Download spreadsheet Download sample Excel spreadsheet with macros file lrxls2vu.xls

      Using a spreadsheet to list URLs allows me to build the details of an application that is easy to see and work with. Now I can start earlier, and even before code is constructed. if I focused only on scripts within VuGen — perhaps

      This spreadsheet has now become a key part of my application performance assurance (test) plans.


    Go to Top of this page.
    Previous topic this page
    Next topic this page

    Set screen Roadmap

      However, this spreadsheet does not have all possible VuGen script coding/tweaksanother page on this site and these limitations:

      • it does not include EXTRARES items recognized during manual recording. This can pose a serious hidden danger which results in under-reporting of response time.
      • it does not generate script to submit forms.

      In a future version I plan to:

      1. Add a column in the worksheet to specify a sub-routine that generates more lines (such as EXTRARES).
      2. digitally sign the file with PVK format Digital Certificate Files Importer "pvkimprt.exe" to avoid annoying messages. The public part of the digital certificate is stored in a SPC file. The private part is stored in the PVK file.

        I haven't bothered with this because Microsoft makes it so scary for users to accept certificates.

      3. something else readers like you request!


    Go to Top of this page.
    Previous topic this page
    Next topic this page

    Set screen Spreadsheet Columns

      Below is a description of each column in the spreadsheet:

      Caution! Do not insert or delete columns — the VBA script works by column sequence number.

    1. App. (Application) — uniquely identifies a portion or an entire application, such as a private administration area or a public storefront, each of which may be operating from a different set of servers. These appear in transaction names before a colon (:).

    2. Scr. (Screen) — uniquely identifies an entire screen, such as the landing page, login page, sub-menus, listings, shopping cart, checkout, etc. These appear in transaction names before a dash (-).

    3. Group — an identifier for a collection of related actions on the same screen, such as a series of similar links or submittal of different values. These appear in transaction names before a dot (.).

    4. Item — uniquely identifies a link, image, field, or other item to be acted upon from an app, screen, or group.

    5. Run Anything in this column will cause the whole row to be ignored. The VBA script only generates code for rows which have this column blank. Examples include link to "Logout" or submit actions in the spreadsheet for documentation. This is also useful to temporarily disable an item.

    6. Script Action identifies the action file specified within This column is also the leftmost column because the spreasheet should be sorted by this column so that items for each action block are generated together. Gaps will result in multiple blocks begin generated for the same script action.

    7. Resulting Screen Name — the name (function) of the screen obtained if the action is successful. Typically this name contains abbreviations such as tab, link, img, popup, etc. to designate the behavior expected.

    8. Manual Step — describes what users do to get to the screen (such as click tab, etc.). This is added to script output as comments.

    9. Pct. (Percent) Use — specifies the percentage of all iterations that the item is actually invoked by the randomizer code controlling script execution. A blank value or "100%" (100 percent, internal value 1.0) specifies that the script always runs. "10%" specifies that the script has a 1 in 10 chance of being picked to run.

    10. Use Case — to designate logical use cases. Several may be designated (separated by commas).

    11. Impor. (Importance) is included as comments in LR scripts.

    12. Parm — designates a unique identifier that will not be changed. This may be used in the future as that basis for creating unique parameter names.

    13. Invoke Text used in LR web_text function calls to make application requests based on text supplied in this column.

    14. Invoke URL used in LR web_url function calls to make application requests using the http supplied in this column.

    15. Confirm Text — used for loadrunner web_reg_find function to ensure that the page returned with the (unique) text anticipated.

    16. Ret. (HTTP Return Code) — the return code from HTTP headers, normally 200 or 301.

    17. Snapshot — the file name of the ".inf" file used by LoadRunner. The script automatically adds the ".inf" suffix to values in this column. If this is blank, the "Snapshot=" line is not generated.


    Go to Top of this page.
    Previous topic this page
    Next topic this page

    Set screen Work It: Run the spreasheet

    1. Open the spreadsheet using Microsoft Excel. The sheet was written in Excel 2003.
    2. If prompted, Enable Macros to run.

      Since the whole point of this file is to run macros, click "Enable Macros".

      There are several macros in the module. The "heart" of the macros are the (for lack of a better name) "PrintIt" and "Final" subs.

      The macro "All" invokes all the individual "Create..." macros that each create a c action script file.

      Sub "Create...Noise" creates an action fileon this page that invokes the URLs according to the percent of iterations specified in the "Noise" worksheet.

      Sub "Create...G1" creates an action fileon this page that invokes only one of the URLs in the "G1" worksheet on each iteration.

    3. Specify your own information.on this page

    4. If you have sorted the spreadsheet a different way, resort by the first three columns (Screen, Section, Item). This is the sort order in the sample spreadsheet.

    5. Press Alt-F5 or select menu Tools, Macro, Macros...
    6. Select subroutine Create_LoadRunner_VuScript() and click Run
    7. Change the file name or path in the "Output Text File Path" pop-up dialog. The default path is the path of the Excel sheet itself.
    8. click OK to generate the file.

    9. Use Windows Explorer to navigate to the file.
    10. Use Notepad (or other text editor) to open the file.

    11. Highlight the codeon this page to copy. If the action already exists, do not copy the action name, brackets, or "return 0;" line.
    12. In VuGen, create the script action if the action has not been created.
    13. click the location where the code should be pasted.
    14. Press Ctrl-V to paste the code.
    15. Execute the VuGen script.


    Go to Top of this page.
    Previous topic this page
    Next topic this page

    Set screen Sample Scripts Generated

      lr_start_transaction and corresponding lr_stop_transaction functions are always executed so that results from LoadRunner reports can be consistently copied and pasted to a spreadsheet, which has a fixed number of rows.

    Set screen Individual "Noise" Script

      Here is an example of the output generated by the VBA module:

      Noise()
      {
      // File H:\Wilsonmarcom\Noise.c generated 1/25/2005 2:29:02 PM
      // by Excel file H:\Wilsonmarcom\lrxls2vu.xls containing 5 rows 
      // Assuming definition of ptDateTime.
      // See http://www.wilsonmar.com/LRXls2VU.htm for updates from this version.
      
      int i, iRecRand, iMixRand;
      float floatThinkRand;
      long longReturnCode, longBytesReturned, longLastByteMSecs;
      char last[30];
      
         lr_continue_on_error(1); // to process all despite an error. 
      // Save message level from user's Runtime Settings:
      iMsg_level=lr_get_debug_message();
      sprintf( cMsg_level, "%d", iMsg_level);
      lr_save_string( cMsg_level, "pMsg_level" );
         floatThinkRand = 0.5;
      
         srand(time(NULL)+rand()%100);
         iMixRand = rand()%100;
         iRecRand = 0;
      
      
      // In Action  for Use Case HealthCheck ManualStep= Priority=
      // From cell $A$2 Run= Parm= MixPct=31
      
      if( 31>=iMixRand ){ // Execute 31 Percent of iterations.
      
         web_reg_find("Text=Hello World!",
             "ID=HealthCheck N1", "FAIL=NotFound",LAST);
      
         lr_start_transaction("HealthCheck N1");
      
         web_url("HealthCheck N1",
             "URL=http://www.godaddy.com/healthcheck.html",
             "Resource=0",
             "RecContentType=text/html",
             "Referer=",
             "Mode=HTML",
             LAST);
      
         lr_end_transaction("HealthCheck N1", LR_AUTO);
         lr_think_time(0.5);
      
      } //if iMixRand()
      
      // In Action  for Use Case Keepalive ManualStep= Priority=
      // From cell $A$3 Run= Parm= MixPct=11
      
      if( 11>=iMixRand ){ // Execute 11 Percent of iterations.
      
         web_reg_find("Text=",
             "ID=Keepalive N2", "FAIL=NotFound",LAST);
      
         lr_start_transaction("Keepalive N2");
      
         web_url("Keepalive N2",
             "URL=http://www.godaddy.com/gdshop/mya_keepalive.asp",
             "Resource=0",
             "RecContentType=text/html",
             "Referer=",
             "Mode=HTML",
             LAST);
      
         lr_end_transaction("Keepalive N2", LR_AUTO);
         lr_think_time(0.5);
      
      } //if iMixRand()
      
      // In Action  for Use Case Notfound ManualStep= Priority=
      // From cell $A$4 Run= Parm= MixPct=10
      
      // Note: http assumed
      if( 10>=iMixRand ){ // Execute 10 Percent of iterations.
      
         web_reg_find("Text=This site is currently unavailable.",
             "ID=Notfound N3", "FAIL=NotFound",LAST);
      
         lr_start_transaction("Notfound N3");
      
         web_url("Notfound N3",
             "URL=http://www.godaddy.com/sites.html",
             "Resource=0",
             "RecContentType=text/html",
             "Referer=",
             "Mode=HTML",
             LAST);
      
         lr_end_transaction("Notfound N3", LR_AUTO);
         lr_think_time(0.5);
      
      } //if iMixRand()
      
      // In Action  for Use Case  ManualStep= Priority=
      // From cell $A$5 Run= Parm= MixPct=0
      
         // Invoke Text and URL not specified.
      
      
         lr_continue_on_error(0); // to stop again on any error.
      return 0;
      } // End of generated script.
      
      

    Go to Top of this page.
    Previous topic this page
    Next topic this page

    Set screen Sample Output - One of Many ("G1")

      Here is an example of the output generated by the default VBA code:

      LandAround()
      {
      // File H:\Wilsonmarcom\LandAround.c generated 1/25/2005 2:22:42 PM
      // by Excel file H:\Wilsonmarcom\lrxls2vu.xls containing 4 rows 
      // Assuming definition of ptDateTime.
      // See http://www.wilsonmar.com/LRXls2VU.htm for updates from this version.
      
      int i, iRecRand, iMixRand;
      float floatThinkRand;
      long longReturnCode, longBytesReturned, longLastByteMSecs;
      char last[30];
      
         lr_continue_on_error(1); // to process all despite an error. 
      // Save message level from user's Runtime Settings:
      iMsg_level=lr_get_debug_message();
      sprintf( cMsg_level, "%d", iMsg_level);
      lr_save_string( cMsg_level, "pMsg_level" );
         floatThinkRand = 0.5;
      
         srand(time(NULL)+rand()%100);
         iMixRand = rand()%100;
         iRecRand = rand()%3 +1; // to begin from 1.
      // Entire section:
      if( 20>=iMixRand ){
      
      
      
      // In Action  for Use Case  ManualStep= Priority=1
      // From cell $A$2 Run= Parm= MixPct=0
      
      if( 1== iRecRand ){
      
         web_reg_find("Text=Sorry, the page you requested does not exist.",
             "ID=1- mercury home", "FAIL=Found",LAST);
         web_reg_find("Text=Copyright  2005 Yahoo! Inc. All rights reserved.",
             "ID=1- mercury home", "FAIL=NotFound",LAST);
      
         web_reg_save_param("pHTTPRet","LB=HTTP/1.1 ",
            "RB= ","Search=Header","ORD=All","NOTFOUND=Warning",LAST);
      
         lr_start_transaction("1- mercury home");
      
         web_url("1- mercury home",
             "URL=http://www.mercury.com",
             "Resource=0",
             "RecContentType=text/html",
             "Referer=",
             "Mode=HTML",
             LAST);
      
         lr_end_transaction("1- mercury home", LR_AUTO);
         lr_think_time(0.5);
      
         if( atoi(lr_eval_string("{pHTTPRet_count}")) != 0 ){
            sprintf(last, "{pHTTPRet_%s}", 
               lr_eval_string("{pHTTPRet_count}"));
            lr_save_string( lr_eval_string(last) ,"pHTTPRet_last");
            if( atoi(lr_eval_string("{pHTTPRet_last}")) != 200){
                lr_error_message("HTTP %s (last of %s) not the 200 expected for 1- mercury home",
                    lr_eval_string("{pHTTPRet_last}"),
                    lr_eval_string("{pHTTPRet_count}"));
            } //if
         } //if
      
         longReturnCode=web_get_int_property( HTTP_INFO_RETURN_CODE );
         longBytesReturned=web_get_int_property( HTTP_INFO_DOWNLOAD_SIZE );
         longLastByteMSecs=web_get_int_property( HTTP_INFO_DOWNLOAD_TIME );
         // Allow messages (Advanced trace) regardless:
             lr_set_debug_message( LR_MSG_CLASS_JIT_LOG_ON_ERROR, LR_SWITCH_OFF);
             lr_set_debug_message( 10,LR_MSG_ON );
      
         lr_output_message("%s\t1- mercury home\t TTime=\t%4.2f\t RC=\t%d\t Bytes=\t%d\t MSecs=\t %d",
            lr_eval_string("{ptDateTime}"),
            floatThinkRand,
            longReturnCode,
            longBytesReturned,
            longLastByteMSecs );
         // Reset message level to Runtime Settings:
             if( iMsg_level & LR_MSG_CLASS_JIT_LOG_ON_ERROR)
                 lr_set_debug_message( LR_MSG_CLASS_JIT_LOG_ON_ERROR, LR_SWITCH_ON);
             lr_set_debug_message( iMsg_level, LR_MSG_ON);
      
      } //if iRecRand()
      
      // In Action  for Use Case  ManualStep= Priority=2
      // From cell $A$3 Run= Parm= MixPct=0
      
      if( 2== iRecRand ){
      
         web_reg_find("Text=Sorry, the page you requested does not exist.",
             "ID=Y2- yahoo group", "FAIL=Found",LAST);
         // No Confirm text specified for web_reg_find
      
         web_reg_save_param("pHTTPRet","LB=HTTP/1.1 ",
            "RB= ","Search=Header","ORD=All","NOTFOUND=Warning",LAST);
      
         lr_start_transaction("Y2- yahoo group");
      
         web_url("Y2- yahoo group",
             "URL=http://groups.yahoo.com/group/LoadRunner/",
             "Resource=0",
             "RecContentType=text/html",
             "Referer=",
             "Mode=HTML",
             LAST);
      
         lr_end_transaction("Y2- yahoo group", LR_AUTO);
         lr_think_time(0.5);
      
         if( atoi(lr_eval_string("{pHTTPRet_count}")) != 0 ){
            sprintf(last, "{pHTTPRet_%s}", 
               lr_eval_string("{pHTTPRet_count}"));
            lr_save_string( lr_eval_string(last) ,"pHTTPRet_last");
            if( atoi(lr_eval_string("{pHTTPRet_last}")) != 200){
                lr_error_message("HTTP %s (last of %s) not the 200 expected for Y2- yahoo group",
                    lr_eval_string("{pHTTPRet_last}"),
                    lr_eval_string("{pHTTPRet_count}"));
            } //if
         } //if
      
         longReturnCode=web_get_int_property( HTTP_INFO_RETURN_CODE );
         longBytesReturned=web_get_int_property( HTTP_INFO_DOWNLOAD_SIZE );
         longLastByteMSecs=web_get_int_property( HTTP_INFO_DOWNLOAD_TIME );
         // Allow messages (Advanced trace) regardless:
             lr_set_debug_message( LR_MSG_CLASS_JIT_LOG_ON_ERROR, LR_SWITCH_OFF);
             lr_set_debug_message( 10,LR_MSG_ON );
      
         lr_output_message("%s\tY2- yahoo group\t TTime=\t%4.2f\t RC=\t%d\t Bytes=\t%d\t MSecs=\t %d",
            lr_eval_string("{ptDateTime}"),
            floatThinkRand,
            longReturnCode,
            longBytesReturned,
            longLastByteMSecs );
         // Reset message level to Runtime Settings:
             if( iMsg_level & LR_MSG_CLASS_JIT_LOG_ON_ERROR)
                 lr_set_debug_message( LR_MSG_CLASS_JIT_LOG_ON_ERROR, LR_SWITCH_ON);
             lr_set_debug_message( iMsg_level, LR_MSG_ON);
      
      } //if iRecRand()
      
      // In Action  for Use Case  ManualStep= Priority=3
      // From cell $A$4 Run= Parm= MixPct=0
      
      // Note: http assumed
      if( 3== iRecRand ){
      
         web_reg_find("Text=Sorry, the page you requested does not exist.",
             "ID=3- qaforums", "FAIL=Found",LAST);
         // No Confirm text specified for web_reg_find
      
         web_reg_save_param("pHTTPRet","LB=HTTP/1.1 ",
            "RB= ","Search=Header","ORD=All","NOTFOUND=Warning",LAST);
      
         lr_start_transaction("3- qaforums");
      
         web_url("3- qaforums",
             "URL=http://qaforums.com",
             "Resource=0",
             "RecContentType=text/html",
             "Referer=",
             "Mode=HTML",
             LAST);
      
         lr_end_transaction("3- qaforums", LR_AUTO);
         lr_think_time(0.5);
      
         if( atoi(lr_eval_string("{pHTTPRet_count}")) != 0 ){
            sprintf(last, "{pHTTPRet_%s}", 
               lr_eval_string("{pHTTPRet_count}"));
            lr_save_string( lr_eval_string(last) ,"pHTTPRet_last");
            if( atoi(lr_eval_string("{pHTTPRet_last}")) != 200){
                lr_error_message("HTTP %s (last of %s) not the 200 expected for 3- qaforums",
                    lr_eval_string("{pHTTPRet_last}"),
                    lr_eval_string("{pHTTPRet_count}"));
            } //if
         } //if
      
         longReturnCode=web_get_int_property( HTTP_INFO_RETURN_CODE );
         longBytesReturned=web_get_int_property( HTTP_INFO_DOWNLOAD_SIZE );
         longLastByteMSecs=web_get_int_property( HTTP_INFO_DOWNLOAD_TIME );
         // Allow messages (Advanced trace) regardless:
             lr_set_debug_message( LR_MSG_CLASS_JIT_LOG_ON_ERROR, LR_SWITCH_OFF);
             lr_set_debug_message( 10,LR_MSG_ON );
      
         lr_output_message("%s\t3- qaforums\t TTime=\t%4.2f\t RC=\t%d\t Bytes=\t%d\t MSecs=\t %d",
            lr_eval_string("{ptDateTime}"),
            floatThinkRand,
            longReturnCode,
            longBytesReturned,
            longLastByteMSecs );
         // Reset message level to Runtime Settings:
             if( iMsg_level & LR_MSG_CLASS_JIT_LOG_ON_ERROR)
                 lr_set_debug_message( LR_MSG_CLASS_JIT_LOG_ON_ERROR, LR_SWITCH_ON);
             lr_set_debug_message( iMsg_level, LR_MSG_ON);
      
      } //if iRecRand()
      
      } // if( entire section 
      
         lr_continue_on_error(0); // to stop again on any error.
      return 0;
      } // End of generated script.
      
      

    Go to Top of this page.
    Previous topic this page
    Next topic this page

    Set screen Make It Your Own

    1. To edit the VBA code, press Alt-F11 or select menu Tools, Macro, Visual Basic Editor.

    2. To insert a row, highlight the entire row below where you want to insert,
      then select menu "Insert", "Row".

    3. If you would like to rearrange or insert columns, in Sub Create_LoadRunner_VuScript():
      1. At the top of the code, add a Dim for each new variable
      2. In section "2.1 Assign variables from spreadsheet:" renumber the cells in the same left-to-right order as in the spreadsheet.

    4. To make the code ignore a column, such as "MixPct", change the line from

       MixPct = Val(rw.Cells(1, 8)) 'convert to single
      to

       MixPct = 0 ' Val(rw.Cells(1, 8)) 'convert to single
      by adding two double-quotes or a zero (0) followed by a quote.
      Idea This approach allows you to easily activate it again later.

    5. Sort the spreadsheet by the first set of columns (App, Screen, Section, Item) by selecting menu Data, Sort. Click the "Header row" radio button. With numbers, select "Ascending".

    6. To generate all files, select macro "All", then click the blue Run arrow button or press F5 to execute the subroutine.

    7. To generate a single file, select macro "Create...", then click the blue Run arrow button or press F5 to execute the subroutine.

      Caution! If your cursor is at sub "PrintIt" or "Final", a new file will not be generated.


    Go to Top of this page.
    Previous topic this page
    Next topic this page

    Set screen Engine Parameters

      "Create..." subroutines specify parameters that control the behavior of the "PrintIt" engine for a particular script file.

    1. Specify the file name to be output in line

      strFileName = ThisWorkbook.Path & "\noise.c"

    2. Specify the text that will be verified for every entry. Example:

      ConfirmAllText = "Sorry"

    3. To specify creation of lr_start_transaction and lr_end_transaction code:

      boolIsTrans = True

    4. To override the think time specified in the data worksheet with a fixed number of seconds for every transaction:

      StdThinkTime = 0.5

    5. To specify creation of code to check all HTTP return codes:

      boolCheckAllHTTPRC = True

    6. To generate code to print information about every request made:

      boolPrintOut = True

    7. The "G1only" subroutine uses this parameter to specify the maximum random number to be created that each transaction references to see if it should be invoked. This should be the record count or records in the G1 data worksheet:

      RecTot = 19

      The "Noise" subroutine has RecTot = 0

    8. To specify the percentage of time that the entire block of URLs are invoked, replace the 0 (zero) with a whole percentage number:

      singleSectionPct = 10

      This is used by the sample "G1only" sub.

    9. To override the percentage of time that a URL is invoked, replace the 0 (zero) with a whole percentage number:

      MixPct = 0

      To make use of the percentage Use specified in the worksheet:

      MixPct = Val(rw.Cells(1, 10)) * 100 'convert to single - 0 to output all lines.


    Go to Top of this page.
    Previous topic this page
    Next topic this page

    Portions ©Copyright 1996-2010 Wilson Mar. All rights reserved. | Privacy Policy |

    Related Topics:
    another page on this site LoadRunner Scripting 
    another page on this site Performance Testing 
    another page on this site NT Perfmon / UNIX rstatd Counters 
    another page on this site Performance Tuning 
    another page on this site WinRunner 
    another page on this site Rational Robot 
    another page on this site Free Training! 
    another page on this site Tech Support 


    How I may help

    Send a message with your email client program


    Your rating of this page:
    Low High




    Your first name:

    Your family name:

    Your location (city, country):

    Your Email address: 



      Top of Page Go to top of page

    Thank you!