JMP Script: Automatically Opening 2 Specific Worksheets in User Selected Excel File as Invisible Data Tables

clausa

Hello Folks,

I am having trouble getting JSL to open files as I want. I have JMP_10.

I want the user to select the Excel file and JSL selects the right worksheets, opens them as invisible data tables, and names them?

I have a single Excel file with multiple worksheets. I want a script to create 2 hidden or private data tables from 2 worksheets in the Excel file. I need the location and file name of the Excel document to be flexible and not hard coded. The worksheet names however can be hard coded. Here are the issues/solutions I have found:

• Adding argument to an Open command where the user is prompted for the file. I can only get arguments (invisible, worksheets) to work if the I code in the file name. I want the user to be prompted to select the file with the data.
  • This code will open a specific worksheet, but only hard coding the file name and direcotry  (or relative to script). I need the user to select the file, but automtically open the sheets I specify (and which will be constant inside of the Excel file).
    • data = Open("data 2014.01.10.xlsm", invisible, Worksheets("For JMP"));
    • FitVar = Open("data 2014.01.10.xlsm", invisible, Worksheets("Variables"));
  • I want something more like below so the user is prompted to select the file once, but JSL knows which sheets to open and how to name them
    • data = Open(, invisible, Worksheets({"For JMP"));
    • FitVar = Open(LAST_FILE_OPENED, invisible, Worksheets({"Variables")); or some other way to open the 2 sheets as different named data tables in JMP
    • I
      
  • I know that this will require turning off the Excel preference to have the user select the sheets to open ("Set Preferences( Excel selection( 1 ) )").

• Right now I have the following, which is cumbersome and slow
  • code to force the user to select Excel Worksheets
    • ExcPref = Char( Arg( Parse( (Char( Get Preferences( Excel Selection ) )) ), 1 ) );

    • Set Preferences( Excel selection( 1 ) );

  • 2 open prompts, both of which require the user to select the file and then the correct worksheet
    • data = Open()
    • FitVar = Open()
  • revert the Excel prefernce to the user's original
    • Set Preferences( Excel selection( ExcPref ) );

Does anyone know how to get this to work?

Thank you for the help!

pmroz

You could use pick file to select the file without opening it. Once you have the filename, use open to open it for each of the hardcoded worksheet names.

As far as making the datasets invisible, once they've been opened, send them each the following message:

data << show window(0);

fitvar << show window(0);