topic Re: check if excel worksheet exsists in Discussions

check if excel worksheet exsists

Amir_y — Sun, 11 Jun 2017 11:28:04 GMT

I would like to write JMP script which try to open specific worksheet from excel.

The problem is if one of my excel files not including the requested worksheet, then the JMP script crush .

I need advice how can I prevent it? (I mean, check before open the excel, if it including the requested worksheet..)

this si my code:

add_to_DB = function({file_path, sheet, header_line, data_line},

new_dt = Open(
file_path,
Worksheets( sheet ),
Worksheet Settings(
Headers Start on Row( header_line ),
Data Starts on Row( data_line ))
);

......

Re: check if excel worksheet exsists

Mark_Bailey — Sun, 11 Jun 2017 12:40:50 GMT

I doubt that there was any crash.

I don't know of any way for a script to check for the presence of a particular sheet in an Excel workbook or for specific content in any other external file type. So your solution won't work.

Error conditions like yours happen in real life all the time. There are ways to deal with them in the script. Have you learned JSL yet? You should read Help > Books > Scripting Guide. Chapter 8 in the current version covers Programming Methods such as error handling.

Are you sure that your code works when the desired sheet is present?

One way is to pay attention to what the Open() function returns. What is returned to the log? Did you look at it? You could store the result in a variable and then test whether it was successful.

Alternatively, you could use the built-in error handling mechanism to trap the error condition. In your case, this mechanism would work like this example:

Try(
	new_dt = Open(
		file_path,
		Worksheets( sheet ),
		Worksheet Settings( Headers Start on Row( header_line ), Data Starts on Row( data_line ) )
	),
	// code in case Open() fails
);;

// code in case Open() succeeds

Re: check if excel worksheet exsists

Amir_y — Sun, 11 Jun 2017 15:40:58 GMT

that works good.

thanks

Re: check if excel worksheet exsists

briancorcoran — Mon, 12 Jun 2017 12:37:37 GMT

If you ever want to find a list of sheetnames that DO exist within the Excel workbook, you can use something like:

sheetList = Get Excel Worksheets( "$SAMPLE_IMPORT_DATA\Team Results.xlsx" );
Show( sheetList );

Brian Corcoran

JMP Development