Share your ideas for the JMP Scripting Unsession at Discovery Summit by September 17th. We hope to see you there!
Choose Language Hide Translation Bar
Highlighted
john_madden
Level V

Bypass Excel Import Wizard in script

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

Re: Bypass Excel Import Wizard in script

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

View solution in original post

6 REPLIES 6
Highlighted
ms
Super User ms
Super User

Re: Bypass Excel Import Wizard in script

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 ) ) );
Highlighted
john_madden
Level V

Re: Bypass Excel Import Wizard in script

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.

Highlighted

Re: Bypass Excel Import Wizard in script

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

 

Highlighted
john_madden
Level V

Re: Bypass Excel Import Wizard in script

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
Highlighted

Re: Bypass Excel Import Wizard in script

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

View solution in original post

Highlighted
john_madden
Level V

Re: Bypass Excel Import Wizard in script

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
Article Labels

    There are no labels assigned to this post.