jmp tabulate to excel

[#1]

Is there an add-in that can export tabulate to excel?

I only found add-in that can export data table to excel. 

Thanks in advance!

[#2]

It's still not 100% clear what your end goal is with respect to the JMP output. When you say you need to produce the report as tabs in Excel, I'm interpreting that as you need to show the JMP Tabulate report as content in a seperate and distinct Excel tab. If my interpretation is correct, one way you could do this besides those I've mentioned above is to save the JMP Tabulate report as an Interactive or static HTML object. Then you can embed each object within a tab in Excel...then all someone needs is a web browser to open each object and what the Excel user will 'see' is the Tabulate report output.

[#3]

Right the HTML looks good, but sometimes my colleagues will like to make some more metric out of it, meaning my output usually might not be the last stop.
You mentioned copy/paste to excel, however once I do that it will end up all in one column, not the right format. Though if I pasted it to Notes first, and then pasted it to excel, I do get the correct format, just without the frames.

Thanks,
Joann

[#4]

@joann,
       If all you wanted is the result of JMP tabulation in Excel  repeatedly, would something like this work ? 

dt = Open( "$SAMPLE_DATA/Car Poll.jmp" );
dt1 = (dt << Tabulate(
	Add Table(
		Column Table( Grouping Columns( :sex, :marital status ) ),
		Row Table( Grouping Columns( :country, :size ) )
	)
)) << Make Into Data Table;
dt1 << Set Name("TabulateResults");

Create Excel Workbook( "c:\MyWorkbook.xlsx", {"TabulateResults"} );

[#5]

@uday_guntupalli

Thank you for the reply.

However with the script you provide the output looks like the below, first screenshot; while I'm looking to output the table in the second screenshot.

Thanks,

Joann

[#6]

That would require the generation of a full Excel formatted file, since it includes spanning columns, etc.

[#7]

Thank you Jim! So this is not possible in JMP?

[#8]

Since you did not specify, which summary statistics that you are using or your JMP version, this might not work for you. If your JMP includes the Categorical platform, and you can create your tabulate table using Categorical, there is an option to Save Excel File(). An example script, the JMP output and the Excel file are displayed below.  The shading is not saved, and if labels are long they are rotated, but the structure remains.

This is just an FYI for a possible alternative.  I have not used Categorical much, I know N, Means, Std Dev, % Response are available. However, I have not investigated quantiles and other statistics. 

Names default to here(1);
dt = Open( "$SAMPLE_DATA/Car Poll.jmp" );
obj = dt << Categorical(
	Structured( :sex * :marital status, :country * :size ),
	Share Of Responses( 0 ),
	Share Chart( 0 ),
	Legend( 0 ),
	Total Responses( 0 )
);
obj << Save Excel File(
	"c:/temp/jmp_to_excel_categorical.xlsx",
	Separate Rows for Each Cell Statistic( 0 )
);

JMP Categorical ReportSaved Excel File

[#9]

This is an extension of Peter Bartell's suggestion: save the journal as HTML.  This script adds the JSL that uses Run Program() with wscript  (that runs vbscript) to import the saved HTML file and do some formatting.  It is a modification of example #3 in the script 9_RunProgram.jsl from JSL Companion, Applications of the JMP Scripting Language, 2nd edition.

The script is attached.

Names default to here(1);

dt = Open( "$SAMPLE_DATA/Car Poll.jmp" );
obj = dt << Tabulate(
	Show Control Panel( 0 ),
	Add Table(
		Column Table( Grouping Columns( :sex, :marital status ) ),
		Row Table( Grouping Columns( :country, :size ) )
	)
);

dt1 = obj << Make into Data Table;
nc = ncol(dt1);
Close(dt1,NoSave);


//===== the code below will import the saved html into Excel =====

//assume < 26*26 columns
_alph = Words("ABCDEFGHIJKLMNOPQRSTUVWXYZ","");
_lc = if(nc<27 , _alph[nc],
       _alph[floor((nc-1)/26)] || if(mod(nc,26)==0,"Z", _alph[mod(nc,26)])
      ); 
new window("Tabulate", <<Journal);
obj << Journal();

_htmfid = "C:\TEMP\car_tabulate.htm";
_xlsfid = "C:\TEMP\car_tabulate.xlsx";

current journal() << Save HTML(_htmfid);

cmdList={ 
"On Error Goto 0  'stop the error handler",
"Dim xlApp",
"\[Set xlApp = CreateObject("Excel.Application")]\",
"xlApp.Visible=True",
"\[xlApp.Workbooks.Open ("^_htmfid^")]\",
"xlApp.Rows.WrapText = False",
"xlApp.Wait Now + #0:00:03#",
"\[xlApp.Columns("A:^_lc^").Select]\",
"xlApp.Selection.Columns.Autofit",
"xlApp.Cells(1, 1).Select",
"\[xlApp.ActiveWorkbook.SaveAs ("^_xlsfid^"), 51]\",
"xlApp.Quit"
};

vbsTxt = concat items(cmdList, "\!N"); //add new lines aka CR 
vbsTxt = EvalInsert(vbsTxt); //insert the values for _lc, _htmfid, _xlsfid
Save Text File( "c:\temp\jj1.vbs" , vbsTxt);

rp3 = RunProgram(Executable("wscript"),
	   Options("c:\temp\jj1.vbs"),
	   ReadFunction("text")
);

 Here is a screen shot of the Excel file. It is a standard Excel file with merged cells for row and column headers and values in  cells.