Recording User's Name and Email Address

I want to create a module for use in a tradeshow booth.   The module will be a game.  However, before the user starts to play I want to capture their name and email address and store it in a file such as Excel or any other file.

There is NO internet access available - everything has to be self-contained on a Windows laptop.

I can build a simple VBA/Macro in Excel which they would fill in and that would store the information. And then I could have a button to hyperlink to the course module.

But, my question is this:   Is there a way to build this completely in Storyline so that the user would enter their name and email into to text boxes and that variable would be added to a file such as Excel or Access?

The goal is to have a list of everyone who played the game in the booth.

Thanks


Ben

63 Replies
Brian Batt

Hi Ben,

You'll need to go into Internet Options in Internet Explorer, click the Security tab, and play with the Local Intranet settings.  I don't know which one will get rid of the prompt.  I'd also recommend searching Google for that specific message as there are some registry modifications that can get rid of the message as well.

Ben Delaney Wyse

I have been working through this and finally dusted off my Excel skills and combined it with some javascript to make this work pretty well.  I am creating some modules for a client to use in their tradeshow booth.  The modules are interactive games designed to attract people to play.  But the client wants an easy way to collect some information about each user that plays the game.   Obviously, you can have text input boxes in SL which save to variables.   But, collecting the information into a database wasn't that easy to address when the SL player does not have access to the internet as in this case.   (With internet connectivity, you have options such as Google Docs and other databases).  

I published the module to CD and I am running it on a Windows laptop.  I stored the Excel file in the published folder.  The user fills out the text boxes and then clicks the Submit button and it executes the javascript.     This script takes the information and stores it in an Excel file, saves the Excel file, and then closes the Excel file.  The Excel file is located in the same folder with the Story.html folder. 

Once the show is over, the client can take their Excel file and use it for marketing and followup.

Here is the script:  (you have to have your variables named correctly as well as the excel file)

/ Initialize worksheet name
var wsName = document.location.pathname;
wsName = wsName.replace(/\//gi, "\\\\");                              // Replaces all forward slashes (/) with double back slashes (\\)
wsName = wsName.substring(2);                                         // Removes the two leading slashes
wsName = wsName.replace(/%20/gi, " ");                                // Replaces %20 with spaces
wsName = wsName.substring(0, wsName.lastIndexOf("\\") + 1);       // Removes the html file name from the file path
wsName = wsName + "FILENAME.xlsx"                                  // Add the excel file name to the file path - change name of file as needed
   
try
{
    // Create the Excel Application object
    var excelApp = new ActiveXObject("Excel.Application");
    excelApp.Visible = false;
       
    // Create the Excel Worksheet object
    var excelWorkbook = excelApp.Workbooks.Open(wsName);
    var excelWorksheet = excelWorkbook.Sheets("Sheet1");
       
    // Find position in the worksheet
    var i = 2;
    while (excelWorksheet.Cells(i, 1).Value != undefined)
    {
        ++i;
    }

    // Place information on the worksheet
    excelWorksheet.Cells(i, 1).Value = i - 1;
    excelWorksheet.Cells(i, 2).Value = player.GetVar("TextEntry");      // Name
    excelWorksheet.Cells(i, 3).Value = player.GetVar("TextEntry2");     // Company
    excelWorksheet.Cells(i, 4).Value = player.GetVar("TextEntry1");     // Email

    // Saves the worksheet and closes it
    excelWorkbook.Save();
    excelWorkbook.Close();

    // Close Excel
    excelApp.Quit();
}
catch (e)
{
    // Error message
    alert(e.Description);
}
finally
{
    // Close Excel
    if (!(excelApp==undefined) || (excelApp==null))
    {
        excelApp.Quit();
    }

    // Clean up
    excelWorksheet = null;
    excelWorkbook = null;
    excelApp = null;
 }

Also, at the end is some error checking. 

Hopefully, someone else will find his beneficial.  It's been puzzling me for over a week.

Ben

Ben Delaney Wyse

Thanks Brian,

I am still working out some issues.  There is a Windows ActiveX security issue which is related to the sandbox SL is using which shows up when you run this script.   It works fine but you have to click through the warning screen.  

I am testing a VB.net version which does not have these same issues and another version which takes your original javascript code using the text box and adds an automated pass through to Excel.   Once I have a good solution that doesn't flip Windows security warnings, I will repost... I hope.

Ben Delaney Wyse

This issue has become a sort of obsession I guess.  I don't know if anyone else will ever need to do something like this but, if so, hopefully they can benefit from my efforts.  Thanks to Steve Flowers for helping me solve my issue with collecting the quiz results data and to Brian Batt for getting me motivated to work this out in JavaScript.

This script creates an ActiveX object to interact with Excel.  ActiveX and Excel are the challenge to doing this.  The JavaScript part is pretty straight-forward.  It's become evident that it's not easy to work around some issues in Windows.  But, it is possible.  I abandoned my VB.net approach and went back to simple JavaScript and Excel macro approach.  The only problem with this is you have modify the registry in order to eliminate the ActiveX warning message.    Most people would consider this a risky approach unless you are familiar with how to do this. It's probably considered a hacker approach at best.

Nevertheless, I needed a way to run a Storyline module on a laptop computer in a trade show booth and collect the names,email addresses and score of everyone who played the module game for later marketing / sales follow-up.   (There is no internet access available in this situation).  So, my choices are either, attempt to install and run a local host web server and work through that method, or create a script which will use SL's JavaScript method and save the data into an Excel file.   The later is most preferable because I will not be present when this is used in the field.  I will have to walk through the client on how to set this on their laptops.

The hurdles are substantial because Excel has known issues with interacting with JavaScript and because files on the local computer are not classified into any zone in Internet Explorer;  consequently, are always run with Medium/High security.  [This is a critical challenge.]  Internet Explorer, itself, does not permit you to change this behavior, but it does have a registry setting for it.  If you don't address this issue, you will get an ActiveX warning window every time the user submits their information when playing the SL module.

To suppress the warning message you must open the registry using a registry editor like RedEdt32.exe, found at C:\Windows\System32\regedt32.exe  and modify the key: 

[HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Internet Settings\Zones\0]

This is tricky stuff and you have to be really careful when you mess around in the registry.  You want to make sure that the only key you modify or create is called "1201".  If it exists, change its value to "0" from "1".  If it does not exist, you have to create it.

Upon changing the key from 1 to 0, the security warning will no longer appear for the ActiveX control on the local files.  If 1201 doesn't exist you have to add it here.  Make sure the value is set to 0.

---------------------------------------------------------------------

So,  I settled on the following JavaScript which is inserted in the trigger of a button in the module.   I have it set up so that the user enters their name, company,  and email information into text boxes on the opening screen of the module.   When they are finished playing the game and come to the Results screen, there is a "Record" button.  This Record button is where I have my JavaScript trigger.  It will run the JavaScript to open the Excel file, add the user's info and score and then save and close the spreadsheet -  all in the background.   The game is restarted with another trigger, and the process just continues to add user data in the next row below the previous data entered.



As for the script:

The script checks for the existence of the Excel file, and for creation of the Excel file if the file does not exist.  A note of caution here:  The Excel file must be specified as a string in the SaveAs Method at Line 44.  If you attempt to use some sort of variable, this step will fail.  This seems to be a known error in the interaction between Excel and JavaScript, and this also seems to be the only way around the issue.

Consequently, you will need to define where the Excel file is going to be before it is placed on another computer, and modify the path accordingly.

I found the easiest way to do this is always put the Story output folder at the C:\\ root.  You still have to change the name of the folder in the script path, but it's easier to always have it right when you are consistent where the folder is placed.  You can create a shortcut for the Launch.exe application and put that on the computer desktop.   If you try to place your story output folder on the desktop, you have to make sure you have the path set correctly.  Since, this path includes the computer user's name it can vary from machine to machine.  But, C:\\ is the same on all machines.

Here is the script for the trigger:



// Initialize worksheet name
var wsPath = document.location.pathname;
wsPath = wsPath.replace(/\//gi, "\\\\");                              // Replaces all forward slashes (/) with double back slashes (\\)
wsPath = wsPath.substring(2);                                         // Removes the two leading slashes
wsPath = wsPath.replace(/%20/gi, " ");                                // Replaces %20 with spaces
wsPath = wsPath.substring(0, wsPath.lastIndexOf("\\") + 1);           // Removes the html file name from the file path
var wsName = wsPath + "USerData.xlsx"                              // Add the excel file name to the file path

try
{
    // Create  filesystem object to determine if the file exists
    var fileSystemObject = new ActiveXObject("Scripting.FileSystemObject")
    var fileExists = fileSystemObject.FileExists(wsName);

    // Create the Excel Application object
    var excelApp = new ActiveXObject("Excel.Application");
    excelApp.Visible = false;
  
    // Open or create the Excel Worksheet object
    if (fileExists)
    {
        // Opens the workbook
        var excelWorkbook = excelApp.Workbooks.Open(wsName);
    }
    else
    {
       // Creates the workbook  -  modify column names to suit your needs
       var excelWorkbook = excelApp.Workbooks.Add;
       excelWorkbook.Worksheets("Sheet1").Cells(1,1)="Index";
       excelWorkbook.Worksheets("Sheet1").Cells(1,2)="Name";
       excelWorkbook.Worksheets("Sheet1").Cells(1,3)="EMail";
       excelWorkbook.Worksheets("Sheet1").Cells(1,4)="Company";
       excelWorkbook.Worksheets("Sheet1").Cells(1,5)="Score";
     
       // Saves the new workbook - [Place the save path to your file here - Save Path must be a string constant - change the name of the storyline folder here to match the project folder published output folder name]
       excelWorkbook.SaveAs("C:\\Storyline Folder output\\USerData.xlsx"); 
     }

    // Get a reference to the worksheet to be use
    var excelWorksheet = excelWorkbook.Sheets("Sheet1");
  
    // Find position in the worksheet
    var i = 2;
    while (excelWorksheet.Cells(i, 1).Value != undefined)
    {
        ++i;
    }

    // Place information on the worksheet -  variables must match the ones you are trying to collect from your Storyline Project
    excelWorksheet.Cells(i, 1).Value = i - 1;
    if (player.GetVar("TextEntry")!=undefined) excelWorksheet.Cells(i, 2).Value = player.GetVar("TextEntry");       // Name
    if (player.GetVar("TextEntry1")!=undefined) excelWorksheet.Cells(i, 3).Value = player.GetVar("TextEntry1");     // Company
    if (player.GetVar("TextEntry2")!=undefined) excelWorksheet.Cells(i, 4).Value = player.GetVar("TextEntry2");     // Email
    if (player.GetVar("myScore")!=undefined) excelWorksheet.Cells(i, 4).Value = player.GetVar("myScore");           // Score
  
        // Saves the worksheet and closes it
    excelWorkbook.Save();
    excelWorkbook.Close();

    // Close Excel
    excelApp.Quit();
}
catch (e)
{
    // Error message
    alert(e.Description);
}
finally
{
    // Close Excel
    if (!(excelApp==undefined) || (excelApp==null))
    {
        excelApp.Quit();
    }

    // Clean up
    excelWorksheet = null;
    excelWorkbook = null;
    excelApp = null;
}

If you have some slick improvements, I would love to get them.  

Thanks

Ben

Robin Leach

Hey Ben,

I am amazed at the script that you have setup so far.  I problably would have gone a different route due to lack of programming skills.  In Brian's video above he demonstrated an simple script that creates a text file.  You could modify the script slightly to put commas in between each item and have all data for each person on the same line.  The text file could then be renamed with a .csv extension and imported into Excel.  Just a thought.

casey hillstrom

I have a similar need to record information about the user before they begin their elearning course. The info my superiors would like to record is the user's name, email address, and organization name. I know how to add these fields very easily in SL but the back end stuff (flash, java, Etc) is a little more difficult. Assuming the user's will all have an internet connection does anyone have any advice on how to preserve this kind of information in an excel or MS word file? 

lebo mafoko

Hi guys

I need help. I want to create some sort of a scoring page similar to the text output but sending my results to a html page. I want to create a high score board kind of page, where the person with the highest score is at the top of the page. Maybe showing the person's name and score.

How can I go about doing this or is there anyone who can show me..

lebo mafoko

Ben D said:

This issue has become a sort of obsession I guess.  I don't know if anyone else will ever need to do something like this but, if so, hopefully they can benefit from my efforts.  Thanks to Steve Flowers for helping me solve my issue with collecting the quiz results data and to Brian Batt for getting me motivated to work this out in JavaScript.

This script creates an ActiveX object to interact with Excel.  ActiveX and Excel are the challenge to doing this.  The JavaScript part is pretty straight-forward.  It's become evident that it's not easy to work around some issues in Windows.  But, it is possible.  I abandoned my VB.net approach and went back to simple JavaScript and Excel macro approach.  The only problem with this is you have modify the registry in order to eliminate the ActiveX warning message.    Most people would consider this a risky approach unless you are familiar with how to do this. It's probably considered a hacker approach at best.

Nevertheless, I needed a way to run a Storyline module on a laptop computer in a trade show booth and collect the names,email addresses and score of everyone who played the module game for later marketing / sales follow-up.   (There is no internet access available in this situation).  So, my choices are either, attempt to install and run a local host web server and work through that method, or create a script which will use SL's JavaScript method and save the data into an Excel file.   The later is most preferable because I will not be present when this is used in the field.  I will have to walk through the client on how to set this on their laptops.

The hurdles are substantial because Excel has known issues with interacting with JavaScript and because files on the local computer are not classified into any zone in Internet Explorer;  consequently, are always run with Medium/High security.  [This is a critical challenge.]  Internet Explorer, itself, does not permit you to change this behavior, but it does have a registry setting for it.  If you don't address this issue, you will get an ActiveX warning window every time the user submits their information when playing the SL module.

To suppress the warning message you must open the registry using a registry editor like RedEdt32.exe, found at C:\Windows\System32\regedt32.exe  and modify the key: 

[HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Internet Settings\Zones\0]

This is tricky stuff and you have to be really careful when you mess around in the registry.  You want to make sure that the only key you modify or create is called "1201".  If it exists, change its value to "0" from "1".  If it does not exist, you have to create it.

Upon changing the key from 1 to 0, the security warning will no longer appear for the ActiveX control on the local files.  If 1201 doesn't exist you have to add it here.  Make sure the value is set to 0.

---------------------------------------------------------------------

So,  I settled on the following JavaScript which is inserted in the trigger of a button in the module.   I have it set up so that the user enters their name, company,  and email information into text boxes on the opening screen of the module.   When they are finished playing the game and come to the Results screen, there is a "Record" button.  This Record button is where I have my JavaScript trigger.  It will run the JavaScript to open the Excel file, add the user's info and score and then save and close the spreadsheet -  all in the background.   The game is restarted with another trigger, and the process just continues to add user data in the next row below the previous data entered.



As for the script:

The script checks for the existence of the Excel file, and for creation of the Excel file if the file does not exist.  A note of caution here:  The Excel file must be specified as a string in the SaveAs Method at Line 44.  If you attempt to use some sort of variable, this step will fail.  This seems to be a known error in the interaction between Excel and JavaScript, and this also seems to be the only way around the issue.

Consequently, you will need to define where the Excel file is going to be before it is placed on another computer, and modify the path accordingly.

I found the easiest way to do this is always put the Story output folder at the C:\\ root.  You still have to change the name of the folder in the script path, but it's easier to always have it right when you are consistent where the folder is placed.  You can create a shortcut for the Launch.exe application and put that on the computer desktop.   If you try to place your story output folder on the desktop, you have to make sure you have the path set correctly.  Since, this path includes the computer user's name it can vary from machine to machine.  But, C:\\ is the same on all machines.

Here is the script for the trigger:



// Initialize worksheet name
var wsPath = document.location.pathname;
wsPath = wsPath.replace(/\//gi, "\\\\");                              // Replaces all forward slashes (/) with double back slashes (\\)
wsPath = wsPath.substring(2);                                         // Removes the two leading slashes
wsPath = wsPath.replace(/%20/gi, " ");                                // Replaces %20 with spaces
wsPath = wsPath.substring(0, wsPath.lastIndexOf("\\") + 1);           // Removes the html file name from the file path
var wsName = wsPath + "USerData.xlsx"                              // Add the excel file name to the file path

try
{
    // Create  filesystem object to determine if the file exists
    var fileSystemObject = new ActiveXObject("Scripting.FileSystemObject")
    var fileExists = fileSystemObject.FileExists(wsName);

    // Create the Excel Application object
    var excelApp = new ActiveXObject("Excel.Application");
    excelApp.Visible = false;
  
    // Open or create the Excel Worksheet object
    if (fileExists)
    {
        // Opens the workbook
        var excelWorkbook = excelApp.Workbooks.Open(wsName);
    }
    else
    {
       // Creates the workbook  -  modify column names to suit your needs
       var excelWorkbook = excelApp.Workbooks.Add;
       excelWorkbook.Worksheets("Sheet1").Cells(1,1)="Index";
       excelWorkbook.Worksheets("Sheet1").Cells(1,2)="Name";
       excelWorkbook.Worksheets("Sheet1").Cells(1,3)="EMail";
       excelWorkbook.Worksheets("Sheet1").Cells(1,4)="Company";
       excelWorkbook.Worksheets("Sheet1").Cells(1,5)="Score";
     
       // Saves the new workbook - [Place the save path to your file here - Save Path must be a string constant - change the name of the storyline folder here to match the project folder published output folder name]
       excelWorkbook.SaveAs("C:\\Storyline Folder output\\USerData.xlsx"); 
     }

    // Get a reference to the worksheet to be use
    var excelWorksheet = excelWorkbook.Sheets("Sheet1");
  
    // Find position in the worksheet
    var i = 2;
    while (excelWorksheet.Cells(i, 1).Value != undefined)
    {
        ++i;
    }

    // Place information on the worksheet -  variables must match the ones you are trying to collect from your Storyline Project
    excelWorksheet.Cells(i, 1).Value = i - 1;
    if (player.GetVar("TextEntry")!=undefined) excelWorksheet.Cells(i, 2).Value = player.GetVar("TextEntry");       // Name
    if (player.GetVar("TextEntry1")!=undefined) excelWorksheet.Cells(i, 3).Value = player.GetVar("TextEntry1");     // Company
    if (player.GetVar("TextEntry2")!=undefined) excelWorksheet.Cells(i, 4).Value = player.GetVar("TextEntry2");     // Email
    if (player.GetVar("myScore")!=undefined) excelWorksheet.Cells(i, 4).Value = player.GetVar("myScore");           // Score
  
        // Saves the worksheet and closes it
    excelWorkbook.Save();
    excelWorkbook.Close();

    // Close Excel
    excelApp.Quit();
}
catch (e)
{
    // Error message
    alert(e.Description);
}
finally
{
    // Close Excel
    if (!(excelApp==undefined) || (excelApp==null))
    {
        excelApp.Quit();
    }

    // Clean up
    excelWorksheet = null;
    excelWorkbook = null;
    excelApp = null;
}

If you have some slick improvements, I would love to get them.  

Thanks

Ben


what are the steps of trying this out. I see a lot of code and im not sure what to do with it.

Chris Spurlock

Question, I can only get this to work in IE if I change my security settings to "allow unsafe active X scripting"..And most uses will have this selected to disabled by default.

How can I make the write to file javascript a "safe script" so the uses will never have to adjust their security settings?

Thanks

Chris

Chris Spurlock

Brian Batt said:

Hi Ben,

I put together a quick example for you here:

https://player.vimeo.com/video/204929978

JavaScript is here:  https://gist.github.com/2628900

(Note:  I am not a JavaScript expert and can't help you with any issues that you may run into.)

Dont forget to put this var player = GetPlayer();in the code otherwise you will get a empty text file.
Yoni H.

Really cool solution to exactly the problem I have.

I have copied and changed the code to my requirments but I keep getting an error "undefined" after the code executes  - then it asks me if I want to save the workbook. If I click yes then headings are being saved but not my variables.

Could anyone please help??

Thanks

Jeff  Ski

I am also having trouble with this script. I do not see the file appearing in my root. I do not get an ActiveX warning while running the program.

The only thing I have done differently from the video (as far as I can tell) is publish to a folder in my C:\ Drive (instead of the D:\ drive, which may be a disc drive). 

Does anyone know why the Test.txt does not come up in my C:\ drive?

Ben Delaney Wyse

Hi folks,

There have been some questions lately about implementing this on Windows 8.  I haven't worked through the issues with Win 8 and don't have any specific answers.  

Brian's video is the better way to approach this if you simply want to record info to a txt file.  My approach was for a specific and somewhat unique problem because the client needed to go into an Excel file and it had to be completely self contained.   ActiveX and all the security issues raised by these JavaScript calls from within a Web Browser make this a pretty vexing issue if you aren't prepared to deal with some deep technical issues.

So, other than what I have posted here [which is pretty dated at this point], I am not much help.    

Thanks

Mario Buber

Brian Batt said:

Hi Ben,

I put together a quick example for you here:

https://player.vimeo.com/video/204929978

JavaScript is here:  https://gist.github.com/2628900

(Note:  I am not a JavaScript expert and can't help you with any issues that you may run into.)


Hi Brain, where can I find the available JavaScript variable provided by the storyline player.

I'm looking for a solution to grab the current slide and figure out the actual position of the playhead. Below the line I would like to create my own simple custom seekbar.It should jump ahead 10 seconds on the timeline when clicking a button and executes some JavaScript or it should jump back 10 sec.

But first of all, I would like to figre out how to grab the duration of the actual slide and show it in the slide?

Can you help me out on that?

Thx Mario

Ashley Terwilliger

Hi Mario,

Brian has taken another position within Articulate, so he doesn't get into the forums as often.  I'm not sure what element you're looking to get from the player, but you may want to review the information here on Javascript best practices, and I hope that someone in teh community will be able to assist you in figuring out the javascript elements.