Subscribe Bookmark RSS Feed

Opening a specific XLS Workbook within a JMP Script

whom

Community Trekker

Joined:

Jun 23, 2011

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!

10 REPLIES
Jeff_Perkinson

Community Manager

Joined:

Jun 23, 2011

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



-Jeff
whom

Community Trekker

Joined:

Jun 23, 2011

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

Jeff_Perkinson

Community Manager

Joined:

Jun 23, 2011

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

Are you using JMP 10 or something earlier?

-Jeff

-Jeff
whom

Community Trekker

Joined:

Jun 23, 2011

JMP 8.0.1

Any suggested workarounds?

ms

Super User

Joined:

Jun 23, 2011

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

Check out this older discussion:

whom

Community Trekker

Joined:

Jun 23, 2011

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$]");

drc_lewis

Community Trekker

Joined:

Jul 23, 2013

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!

drc_lewis

Community Trekker

Joined:

Jul 23, 2013

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

David_Burnham

Super User

Joined:

Jul 13, 2011

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

-Dave