JMP14 How can i import multi excel files with defined sheet?

[#1]

Dear All

there is a function of JMP14 which can import multi excel files together and stack them automatically.

But it also choose the first sheet in excel file, can i define the sheet in the excel which include more than one sheet?

thanks!

[#2]

@juna1Super thanks

[#3]

Hi @juna1 .  This script works great.  I notice that if a file in the directory does not contain the worksheet name, the script stops and doesn't complete the remaining files that do have it.  Is there some code we can insert to skip files in the selected directory that do not contain the worksheet name?

[#4]

You can place a Try() function around the Open() function and it will keep the Excel workbooks that do not have the correct sheet name from erroring out.

dir = Pick Directory( "Select a directory" );
//get all file names under the folder
files = Files In Directory( dir, Recursive );

nfiles = N Items( files );
For( i = 1, i <= N Items( files ), i++,
	fullfile = dir || files[i];
	dt = "";
	Try(
		dt = Open(
			fullfile,
			Worksheets( "Intensity" ),
			Use for all sheets( 0 ),
			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( "-" )
			)
		)
	);
	If( dt != "",
		dt << New Column( "AbbCon1", character, width( 8 ), <<set each value( files[i] ) );
		If( i == 1,
			(dt1 = dt ; dt1 << Set name( "Tem" ))
		);
		If( i > 1,
			dt1 << Concatenate( dt, "Append to first table" );
			dt << closewindow;
		);
	);
);