Bypass Excel Import Wizard in script

[#1]

I am trying to open a .xslx file from within an JMP application when the "OK" button of an application window is pressed. My problem is I'm finding it hard to bypass with Excel wizard window and just open directly.

I want the file to open in JMP using the Excel options I specify in an Open() statement. To get my Open() statement options correct, I opened the file manually and copied the Open() statement from the table's Source script, so I think all those options are right. It's a multi-worksheet excel file, and I only want one worksheet in JMP, so here's what my Open() state looks like:

Open(
	"~/Desktop/test.xlsx",
	Worksheets( "Data" ),
	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( 1 ),
		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( 0 ),
		Column Separator String( "-" )
	)
)

Oddly, when I execute this open statement in a free-standing script, everything happens as I want: the file opens directly without the Excel Wizard pane popping up.

However, when I include the identical Open() statement as part of the script of a JMP application that I am building, I can't seem to stop the wizard pane from opening. (The Open() statement is called when I click the "OK" button of my application's user input window.)

Any ideas why this is happening and how to stop it?

[#2]

I've also experienced this and have found one workaround. Save the Open() command as a separate script and use Include() to run the code.

// Create a jsl file with the Open() command
f = Save Text File(
	"~/Desktop/test.jsl",
	Char(
		Name Expr(
			Open(
				"~/Desktop/test.xlsx",
				Worksheets( "Data" ),
				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( 1 ),
					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( 0 ),
					Column Separator String( "-" )
				)
			)
		)
	)
);

// Invoke Open() via user input
nw = New Window( "open test", Button Box( "OK", Include( f ) ) );

[#3]

Thanks, ms. This will be helpful in the future. In the meantime, I've figured out a different workaround for my situation, but this is good to have in the toolbox for the next time.

There's a section in the Scripting Guide (p. 333) that discusses the context-sensitivity of the Open() function and relates it to the Excel Wizard, but I don't find it terribly clear. I can't help thinking that the behavior discussed here is either a shortcoming (?bug) in the implementation of Open(), or else needs more documentation.

For example, one thing that would be great is an explicit option in Open() of whether of not to use the wizard, instead of keying off a preference. Just my two cents.

[#4]

I've tried to reproduce this problem with JMP 13/14/15 using the attached JMP Application.  The button press script is shown below, and it correctly opens the table in all of the versions that I've tried so far.  Does this example work for you?  How does this differ from the Application that you are developing, and what version of JMP are you using?

Button1Press = Function( {this}, 
	// This function is called when the button is pressed
	Open(
		"$SAMPLE_IMPORT_DATA\VA Lung Cancer.xls",
		Worksheets( "VA Lung Cancer" ),
		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( 1 ),
			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( 0 ),
			Column Separator String( "-" )
		)
	)
);

[#5]

Dan, Thanks for responding. I'll try this out, and I'll send you separately the application that is causing my problem. Give me a bit. John

[#6 · Accepted solution]

It turns out that behavior is different on Windows and Mac, likely due to differences in the supporting libraries for reading Excel files.  On Mac, an Open() script called directly will open the file as expected, but an Open() call invoked from a button click will bring up the Excel Wizard.

The workaround that @ms provided forces JMP into "batch" mode, which changes the logic to behave as though the script was run directly rather than via a button.  Another way to do that which does not require an intermediate file is to embed your script in a ScriptBox and invoke <<Run on the box.  Here's an example that should work on both Mac and WIndows from a button-click:

// One way to force JMP into batch mode is to embed the script in a ScriptBox
// and invoke <<Run on the script box.
New Window( "button scriptbox",
	Button Box( "Open",
		<<Set Function(
			Function( {this},
				{scriptbox},
				scriptbox = Script Box(
					"\[
					Open(
						"$SAMPLE_IMPORT_DATA\Texas Precipitation.xlsx",
						Worksheets( "2009" ),
						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( 3 ),
							Data Starts on Column( 1 ),
							Data Ends on Row( 0 ),
							Data Ends on Column( 0 ),
							Replicated Spanned Rows( 1 ),
							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( 0 ),
							Column Separator String( "-" )
						)
					)
					]\"
				);
				scriptbox << Run;
			)
		)
	)
);

[#7]

Dan,
Thanks for this follow-up. I had eventually tried everything else and ended up using @ms's solution of embedding the open statement inside an Include() file. This explains what's going on, and provides a different workaround.
John

[#8]

@danschikore Is the procedure referenced here still the only way to accomplish this? Is there any thought to removing the need to write in this kludge to get the desired scripting behavior?

[#9]

@mjoner - Yes, this workaround is still required through at least JMP 16.0.

[#10]

In case anyone is puzzling over this one, here is an alternative approach: 1. get & store the user preference for Excel Open Method; 2. change it to what you need; 3. open the file; 4. change the setting back to what it was

e.g.

originalPref = Get Preferences( Excel Open Method );
Set Preference(Excel Open Method("Open All Sheets"));

myPath = Pick File(.... etc

dt = Open(myPath...)

eval(originalPref)