Error opening excel file when using path variable.

CentroidError56 — Wed, 05 Mar 2025 14:03:50 GMT

Trying to open an excel file using the path variable "$Input_File" as below. When I run the script I receive an evaluation error. The script works fine when I execute the script with the file path itself rather than using the path variable. Can anybody suggest why this happens and how to resolve? Using JMP Pro 17.

Set Path Variable("Input_File", Pick File());

dt = Open(
	"$Input_File",
	Worksheets({"Experiment Details"}),
	Use for all sheets( 0 ),
	Concatenate Worksheets( 1 ),
	Create Concatenation Column( 1 ),
	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( 2 ),
		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( "-" )
	)
	);
dt << Set Name("Exp_Details_1.jmp");

Re: Error opening excel file when using path variable.

txnelson — Wed, 05 Mar 2025 14:17:24 GMT

You need to use

file = get path variable("Input_File")

to get the value set from the Pick File.

Then use:

dt = Open(
	file,
	Worksheets({"Experiment Details"}),
	Use for all sheets( 0 ),
	Concatenate Worksheets( 1 ),
	Create Concatenation Column( 1 ),
	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( 2 ),
		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( "-" )
	)
	);
dt << Set Name("Exp_Details_1.jmp");

topic Error opening excel file when using path variable. in Discussions

Error opening excel file when using path variable.

Re: Error opening excel file when using path variable.