cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Browse apps to extend the software in the new JMP Marketplace
Choose Language Hide Translation Bar
jlmbs
Level III

Is this possible to write a script that opens always the last sheet of an xlsx file?

I need a JSL script that opens always the last sheet of a chosen xlsx file. Is there a function to do this in JSL?
Alternatively, is it possible to accomplish this by compiling all the sheets of the Excel file into a list and open the last item of the list? Or open the excel file in a way that the user can input the sheet to open?

1 ACCEPTED SOLUTION

Accepted Solutions

Re: Is this possible to write a script that opens always the last sheet of an xlsx file?

Hi,

 

I've added a reference to the chosen file, which can then be plugged into Open().  Let us know if this works (the Open settings may need to be adjusted for your file):

sheetList = Get Excel Worksheets(file=Pick File("Select xlsx file","$DOCUMENTS",{"Excel File|xlsx", "All Files|*"},1,0,""));
Show( sheetList );
lastSheet = sheetList[N Items( sheetList )];
Open(
	file,
	Worksheets( lastSheet ),
	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( "-" )
	)
);

View solution in original post

5 REPLIES 5
txnelson
Super User

Re: Is this possible to write a script that opens always the last sheet of an xlsx file?

Here is the example from the Scripting Index that illustrates how to get a list of the spreadsheets that are in a given wookbook.  From there one can select the last spreadsheet.

txnelson_0-1659923071830.png

Names Default To Here( 1 );
sheetList = Get Excel Worksheets( "$SAMPLE_IMPORT_DATA\Team Results.xlsx" );
Show( sheetList );

From there it is a simple matter to get the last entry in the list

lastSheet = sheetList[N Items( sheetList )];

 

Jim
jlmbs
Level III

Re: Is this possible to write a script that opens always the last sheet of an xlsx file?

This do not seem to be able to work. Here I wrote a code to pick up the xlsx file of interest, of which I would like to get the last worsksheet and open it into a data  table. Could you advise on how to move forward from the code below?

sheetList = Get Excel Worksheets(Pick File(	"Select xlsx file","$DOCUMENTS",{"Excel File|xlsx", "All Files|*"},1,0,""));
Show( sheetList );
lastSheet = sheetList[N Items( sheetList )];
jthi
Super User

Re: Is this possible to write a script that opens always the last sheet of an xlsx file?

After you have the name of last sheet and path to the file, then you can use Open() to open the file with only the last worksheet.

From Scripting Index:

jthi_0-1667315883017.png

 

-Jarmo
txnelson
Super User

Re: Is this possible to write a script that opens always the last sheet of an xlsx file?

Your code seems to work for me.  What issue are you having with it?  What JMP version and what operating system?

Jim

Re: Is this possible to write a script that opens always the last sheet of an xlsx file?

Hi,

 

I've added a reference to the chosen file, which can then be plugged into Open().  Let us know if this works (the Open settings may need to be adjusted for your file):

sheetList = Get Excel Worksheets(file=Pick File("Select xlsx file","$DOCUMENTS",{"Excel File|xlsx", "All Files|*"},1,0,""));
Show( sheetList );
lastSheet = sheetList[N Items( sheetList )];
Open(
	file,
	Worksheets( lastSheet ),
	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( "-" )
	)
);