Version 13 of JMP introduces a new function: Get Excel Worksheets.
Here is a detailed example illustrating how the function can be used to create a dialog window to allow a user to select which worksheet should be opened
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 |
lst = Get Excel Worksheets("$SAMPLE_IMPORT_DATA\Team Results.xlsx"); New Window("Excel", Show Menu(0), Show Toolbars(0), Border Box(Top(10),Bottom(20),Left(20),Right(20), Lineup Box(NCol(1), Text Box("select a worksheet:", <<Set Width(240)), lb = List Box(lst,<<Set Script(Do Select()),<<Set Max Selected(1)), btn = Button Box("import worksheet",Do Import(),<<Enable(0)) ) ) ); Do Select = Function({},{Default Local}, sel = lb << Get Selected; If (NITems(sel)==0, btn << Enable(0) , btn << Enable(1) ); ); Do Import = Function({},{Default Local}, ws = (lb << Get Selected)[1]; dt = Open("$SAMPLE_IMPORT_DATA\Team Results.xlsx", Worksheets(ws), Worksheet Settings( Has Column Headers( 1 ), Number of Rows in Headers( 1 ), Headers Start on Row( 3 ), Data Starts on Row( 4 ) ) ); dtName = dt << Get Name; Eval(Parse(Eval Insert("\[ dt << On Close( Close( Data Table("^dtName^"), NoSave ) ) ]\"))); ); |