Subscribe Bookmark RSS Feed

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

clausa

Community Trekker

Joined:

Jan 16, 2014

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!


    1 ACCEPTED SOLUTION

    Accepted Solutions
    Solution

    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);

    3 REPLIES
    clausa

    Community Trekker

    Joined:

    Jan 16, 2014

    Bump. Anyone have any ideas? I don't need a fully baked solution, but partials will help!

    Solution

    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);

    clausa

    Community Trekker

    Joined:

    Jan 16, 2014

    Thanks PMroz! I did not know about the pick file command, but that works perfectly. Actually allows me to combine everything together. I do need to turn off the Excel open preference first to force the worksheet, but easy to do. See code below. Thanks again!

    FileName = Pick File(

          New Window( "Open File Instructions",

                <<modal,

                V List Box(

                      align( center ),

                      Text Box( "Please select the Excel File that has your data." ),

                      Text Box("Make sure that it was saved since you last added data to it."),

                      Button Box( "OK" )

                )

          )

    );

    ExcPref = Char( Arg( Parse( (Char( Get Preferences( Excel Selection ) )) ), 1 ) );

    Set Preferences( Excel selection( 0 ) );

    data = Open( FileName, invisible, worksheets( "For JMP" ) );

    ConFitVar = Open( FileName, invisible, worksheets( "Continuous Fit Variables" ) );

    Set Preferences( Excel selection( ExcPref ) );