topic Re: Open Excel within a button box - Strange behavior in Discussions

Open Excel within a button box - Strange behavior

nikles — Fri, 09 Jun 2023 19:46:43 GMT

Hi. I'm noticing that if I try to open an excel file using a script, the Excel Import Wizard appears if the Open command originates from a buttonbox, but it does not appear if the Open command is in the top-level of the script. For the record, I do not want the wizard to appear in this case. Is anyone familiar with this behavior, and if so, can I fix it?

For example, I have a script where the user picks an excel file, then it opens. In this case the excel file opens as a JMP table without issue. However, if instead I place the command to open the excel file within a buttonbox, the Excel Wizard appears. Even if using the exact same options in both cases. This is demonstrated in the script below, using the attached xlsx file:

Names Default to Here(1);

//Get location of file:
path = Pick File("Select xlsx Table...",,
	{"Excel|xlsx"}, 1,
	0
);
If(path == "", Stop());	

//Expression to open file:
open_exp = Expr(
	Open(path,
		Worksheets("Sheet1"),
		Use for all sheets(1),
		Concatenate Worksheets(0),
		Create Concatenation Column(0),
		Worksheet Settings(
			1,
			Has Column Headers(1),				
			Number of Rows in Headers(1),
			Headers Start on Row(1),
			Data Starts on Row(2),
			Data Starts on Column(1),
			Data Ends on Row(0),
			Data Ends on Column(0),
			Replicated Spanned Rows(0),
			Replicated Spanned Headers(0),		
			Suppress Hidden Rows(1),
			Suppress Hidden Columns(1),
			Suppress Empty Columns(1),			
			Treat as Hierarchy(0),
			Multiple Series Stack(0),
			Import Cell Colors(0),
			Limit Column Detect(1),				
			Column Separator String("-")
		)
	);
);

//Attempt to open file...works as intended (no Import Wizard)
open_exp;

//Attempt to open file via a button...always results in the Import Wizard.
win = New Window("Temp",
	ButtonBox("Open xlsx",
		open_exp
	)
);

As you can see, both methods are using the exact same command with the exact same options on the exact same file, yet I'm getting 2 different behaviors.

Of further note, when the xlsx file is opened via the buttonbox, the open command settings seem to be ignored. That is, if I alter the "Number of Rows in Headers", "Headers Start on Row", "Data Starts on Row", etc options, the first method used will respect those new settings. However the second "buttonbox" method with the Import Wizard always seems to revert to the default settings instead.

For the record, I don't want to modify my preferences so that JMP never uses the Excel Wizard. I just don't want to use it in this case, and I can't require other users of this app to make the same mods to their preferences.

My specifics:

JMP Pro 15.2.1

Mac OS Catalina 10.15.7

Excel: Version 16.49

Hope someone can help. Thanks!

Re: Open Excel within a button box - Strange behavior

txnelson — Wed, 12 May 2021 22:08:15 GMT

I do not find the same behavior on JMP 15.2 running Windows 10. I suggest that you try adding a
Your Window Name << close window
just before your
open_exp
and see if the behavior changes

Re: Open Excel within a button box - Strange behavior

nikles — Sun, 16 May 2021 19:56:01 GMT

Hi Jim. Thanks, but no unfortunately that did not solve the problem. I'll reach out to support and see if they have some ideas.

Re: Open Excel within a button box - Strange behavior

Craige_Hales — Tue, 13 Sep 2022 01:21:54 GMT

see for the batchinteractive function. The button driven version of the script knows you must be interactive and helpfully puts you in an interactive wizard.

Also, has a better answer.

Finally, if you have the opposite issue and want the wizard, see

@Wendy_Murphrey both of these seem hard to discover in the documentation.

edit: link to odd variation with SetFont

Re: Open Excel within a button box - Strange behavior

Wendy_Murphrey — Mon, 17 May 2021 13:03:55 GMT

Hi, @Craige_Hales.

Batch Interactive() is not documented as it is considered experimental. However, the suggestion has been made to the JMP Development team to promote the function to full support. I will add this discussion to that request. The typical use case is as part of a script that is executed from an interactive action, such as clicking a button.

Documentation of the "Excel Wizard" option is included in the following locations:

JSL Syntax Reference entry for the Open() function
Scripting Guide Import Data section

I will be glad to pass along a request to include it in the Scripting Index example for opening Excel data.

Re: Open Excel within a button box - Strange behavior

miguello — Tue, 30 Aug 2022 23:21:04 GMT

Wendy,

Is there any update on this?

I have a script that sets some fonts on my display boxes, but it turns out some of the computers in the organization have very minimal fonts installed.

So my users get lots of pop-ups saying "Unknown Font". Rather than revert everything to the default fonts, I tried to wrap it in Try() blocks, but it didn't help.

Craige advised to use Batch Interactive(1), but it also seems to not be working, as simple example below shows:

dt = Open( "$SAMPLE_DATA/Big Class.jmp" );

biv = dt << Bivariate( Y( :height ), X( :weight ) );
rbiv = biv << Report;

For Each({box, index},  (rbiv << XPath( "//AxisBox/MouseBox/DropBox/TextEditBox" )), 
	Batch Interactive(1);
	Try(box << Set Font( "Agency FF", 13 ));
	Batch Interactive(0);
);

Any idea why Batch Interactive(1) doesn't work in this case and how to deal with the missing fonts?