cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
Amir_y
Level I

check if excel worksheet exsists

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

......

1 ACCEPTED SOLUTION

Accepted Solutions

Re: check if excel worksheet exsists

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

 

View solution in original post

3 REPLIES 3

Re: check if excel worksheet exsists

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

 

Amir_y
Level I

Re: check if excel worksheet exsists

that works good.

thanks

Re: check if excel worksheet exsists

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