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

[#1]

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. 

[#2]

@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 ? 

[#3]

Hi,

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

[#4]

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 = 

[#5]

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.

[#6 · Accepted solution]

@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( "-" )
	)

[#7]

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. 

[#8]

@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. 

[#9]

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

[#10]

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