6092_en-US

liliconcom

Opening a specific XLS Workbook within a JMP Script<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />

I have an XLS (BigData.xls) that has workbook tabs named "US", "Canada", and "Ireland".

I have a script that needs to only open/read the Canada tab.  Is there a JMP script command to do that?  I'm trying to avoid using the manual dialogs to pull in the Canada tab.

Thanks!

Re: Opening a specific XLS Workbook within a JMP Script

Dave, thanks as always. I will borrow some of this code to get consistent performance across the variants of v10 we have internally.

Re: Opening a specific XLS Workbook within a JMP Script

A crude (but effective) method I have used in the past was to open the entire workbook invisibly, then close the ones that I didn't want.  Check the tables that are present after the Open versus the ones that were open before, and that gives you the names of the workbooks in the spreadsheet.

 

As you have noticed user preferences can cause a problem - here is a pattern of code to detect for a preference, set it to what you need, then restore it:

 

 

// preference to check
searchPref = "Excel Has labels";

// check the preference
prefs = Char( Get Preferences() );
p = Pat Match( prefs, searchPref + "(" + Pat Arb() >> strResult + ")" );
If ( p == 0,
  Write( "!NFailed to find value for " || searchPref )
,
  result = Parse( strResult );
  Show( result );
);


// set it to what you want
Set Preferences(Excel Has Labels(2));


// do your stuff
Open(nsSFEPM:pathMDSS, invisible)


// restore the user preference
Eval(Parse(Eval Insert("\[
  Set Preferences(Excel Has Labels(^result^));
]\")));

 

 

-Dave

Re: Opening a specific XLS Workbook within a JMP Script

I've just tried this in code and got a fellow user to test it too. I discovered that the user's preferences take precedence over the function's argument. In the JSL I specify which sheet to open, but if the user's preference says to prompt then they get prompted! This seems counter-intuitive to the use of the function...

[Edit] My user has 10.0.0 and preference was overriding code. My machine has 10.0.2 and code won over preference. Of course, I didn't see anything in the "what's new in version 10.0.x" documentation about this function at all...

Re: Opening a specific XLS Workbook within a JMP Script

I've just found this today (the worksheets option of the Open command), which was very helpful. However, it didn't appear in the help pages of the application, which would have been more helpful!

Re: Opening a specific XLS Workbook within a JMP Script

While the older discussion helped, the syntax was not correct.  The name of the worksheet must be enclosed in square brackets, and you must add a $ at the end of the worksheet name.   So, if my spreadsheet had the worksheet called demo, the correct syntax is:

 

 

FullyQualifiedFile = "c:\$user\ResearchData\FY2012.xls";
ODB_String = "DSN=Excel Files;DBQ=" || FullyQualifiedFile;
dt = Open Database(ODB_String,"SELECT * FROM [demo$]");

 

Re: Opening a specific XLS Workbook within a JMP Script

If using Open Database() instead of Open(), individual sheets can be specified in the SQL-string.

Check out this older discussion:

Re: Opening a specific XLS Workbook within a JMP Script

JMP 8.0.1

Any suggested workarounds?

Re: Opening a specific XLS Workbook within a JMP Script

Sorry, I should have specified that this option was added in JMP 10.

Are you using JMP 10 or something earlier?

-Jeff

Re: Opening a specific XLS Workbook within a JMP Script

Didn't work.  ALL of the worksheets opened up when I specified Worksheets ( "small" )      or     Worksheets ( {"Small"}  )

Re: Opening a specific XLS Workbook within a JMP Script

You can use the Worksheets() keyword:

 

open("C:\data\cars.xlsx", Worksheets("small"));

Import multiple worksheets by including the worksheet names in brackets:

 

 

open("C:\data\cars.xlsx", Worksheets( {"small", "medium", "large"} ));