Subscribe Bookmark RSS Feed

How to save a group of files as excel spreadsheets?

Hi Guys,

How can I save a folder full of .jmp files as excel spreadsheets with a script?

I am trying something like:

5 REPLIES
mpb

Super User

Joined:

Jun 23, 2011

First thing is to make sure you can save a single table as an Excel file. Example:
Ok that worked.

It only works if the JMP file has < = 256 columns because of the Excel limitation.

Is there any way to split the extra columns into a different sheet?

I can do that in Excel VBA but I would like to do it in JMP.
Ok that worked.It only works if the JMP file has less than or equal to 256 columns because of the Excel limitation.Is there any way to split the extra columns into a different sheet?
I can do that in Excel VBA but I would like to do it in JMP.
mpb

Super User

Joined:

Jun 23, 2011

I haven't done the details, but yes you should be able to do what you describe. It breaks down into 2 parts, (1) Creating subsets of no more than 256 columns and (2) Saving these subsets as individual sheets of the same Excel workbook.
1. Script the Table>Subset after counting the number of columns in the original table and assessing how many subsets you need to create
2. Use the capabilities built into the JSL ODBC access to databases (in this case the Excel file is a "database" whose tables are the sheets). Though you'll have to play a bit to get it, the basics are outlined in the Scripting docs on p384-5. In addition, if you open the JMP log and see what code is generated by opening and saving an Excel file using "Open Database Table" you'll get some more hints of how to handle it.

It will take some fussing to get it right but it can be done.
Ok thanks, I will try that.