topic RE: Saving Multiple Excel Sheets as Seperate Data Tables in JSL in Discussions

RE: Saving Multiple Excel Sheets as Seperate Data Tables in JSL

Jaz — Sat, 10 Jun 2023 23:13:49 GMT

Hi,

I'm trying to use the Get Excel Worksheets function to save each of the worksheets in my excel file as a seperate data table.

I used the following code and I keep getting a name unresolved error, but I'm sure that the file path is correct.

listOfWorksheets = Get Excel Worksheets("C:\Users\jaz\Desktop\Template.xlsx");

Another thing is that the Get Excel Worksheets doesn't appear in blue in my script. Does that mean that the version of JMP I'm using doesn't support this function?

Any help would be appreciated.

RE: Saving Multiple Excel Sheets as Seperate Data Tables in JSL

uday_guntupalli — Thu, 21 Jun 2018 13:01:41 GMT

@Jaz,
Couple of things.

1. In order to save each work table as a separate worksheet, you will want to use the Create Excel Workbook

2. As for the error you are getting, your line of code follows the syntax, the other things to consider are :

a. What are the names of the tabs ? Try renaming them to see if they are causing an issue

b. Is the file locked or restricted ? Try creating a copy of the file and rerunning your code

c. Lastly, are you using a windows OS or a MAC ?

RE: Saving Multiple Excel Sheets as Seperate Data Tables in JSL

Jaz — Thu, 21 Jun 2018 13:04:26 GMT

Hi,

Would appreciate it if you could read my edited post above and I'm using Windows OS.

RE: Saving Multiple Excel Sheets as Seperate Data Tables in JSL

Jaz — Thu, 21 Jun 2018 13:05:36 GMT

Also, how would I go about creating an excel workbook? Thanks.

RE: Saving Multiple Excel Sheets as Seperate Data Tables in JSL

uday_guntupalli — Thu, 21 Jun 2018 13:12:29 GMT

Which version of JMP are you using ? If it is before JMP 12, I doubt Get Excel Worksheets() existed. If the function is not being identified, while other functions are, it most likely is not a part of the version you are using. YOu may confirm by trying to search the function in "scripting index" as well.

As for your other question, if you are using the right version, once you achieve the list of Excel sheets, you can loop around the list and use the open() function.

FilePath = <Your Custom File Path>; 

SheetsList = Get Excel Worksheets(FilePath); 

for(i = 1, i <= N Items(SheetsList), i++,
            dt = Open(FilePath || SheetsList[i]); 
   );

SheetsList =

RE: Saving Multiple Excel Sheets as Seperate Data Tables in JSL

Jaz — Thu, 21 Jun 2018 13:16:51 GMT

Yes, it looks like the version I'm using is out of date. Is there any other way you could suggest of turning each individual sheet into a data table and saving it. Note: I want to do it in JSL not using wizards.

RE: Saving Multiple Excel Sheets as Seperate Data Tables in JSL

uday_guntupalli — Thu, 21 Jun 2018 13:20:21 GMT

@Jaz,
You can use the import wizards to give you the corresponding JSL. However if you don't want to do that, you can make a list of the worksheets manually, and try to use the open command and loop through and open each worksheet.

Here is an example of the different parameters Open() might need. You may need to provide all of those, but this is just an example

Open( FilePath[i],
	Worksheets( "Sheet1" ),
	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( "-" )
	)

RE: Saving Multiple Excel Sheets as Seperate Data Tables in JSL

Jaz — Thu, 21 Jun 2018 13:24:17 GMT

I was also wondering about if I updated my version of JMP and then attempted to run the script on a different device with the outdated version installed. Would the script still function as intended?

Thanks for the help.

RE: Saving Multiple Excel Sheets as Seperate Data Tables in JSL

uday_guntupalli — Thu, 21 Jun 2018 13:27:30 GMT

@Jaz,
I don't follow your question. If you had an updated JMP version on a machine , then the code you developed in the prior version would mostly work unless any functionality has been removed/changed. However, without seeing the script or the functions you are going to use, that will be hard to answer with a definitive yes/no. My guess would be that it will run fine if you using basic JSL.

RE: Saving Multiple Excel Sheets as Seperate Data Tables in JSL

Jaz — Fri, 22 Jun 2018 10:35:25 GMT

Hi,

To clarify, I mean that if I had used the function Get Excel Worksheets (which is only available in JMP 12 / newer) in my script, and someone else attempted to run that script on their computer which only has JMP 11, would the script run as intended (would the Get Excel Worksheets function work)?

RE: Saving Multiple Excel Sheets as Seperate Data Tables in JSL

briancorcoran — Fri, 22 Jun 2018 12:10:38 GMT

To answer your previous question, a script using a feature new to version 12 will fail in version 11, with a JSL error saying it doesn't understand the command.

In JMP 11, you would need to save tables as individual Excel files. There is no ability to collect a bunch of tables together into a single workbook.

Brian Corcoran

JMP Development