How to export JMP Data Tables as different Tabs of an Excel Sheet ?

[#1]

Hi,

I have a couple of JMP Data Tables open and would like to export these as different tabs of one excel sheet.

How can I do this in JMP script?

Thanks,
Aman

[#2]

I would contact Tech Support.  They can make sure that your drivers are matching up with Excel and JMP properly.

Brian

[#3]

I get the following error:

Send Expects Scriptable Object in access or evaluation of 'Send' , dt << Save database( "DSN=Excel Files;DBQ=C:\jimmy\html\jmp\Bigc.xlsb;", "BigC" )

In the following script, error marked by /*###*/

dt <<  /*###*/Save database(

  "DSN=Excel Files;DBQ=C:\jimmy\html\jmp\Bigc.xlsb;",

  "BigC"

) /*###*/

{}

I am running JMP10.0.2 32-bit and ms office 32-bit.

[#4]

Did you see open data tables?  This error would typically be caused by dt and dt2 not opening or going out of scope.  You may need to fix up the paths to the tables.  Also, you must have a DSN registered that is called "Excel Files".

Brian

[#5]

Sorry, I thought you were referring to the normal File->Save As export mechanism.  You could probably do it through ODBC, but if you have 32-bit Microsoft Office than you will need 32-bit JMP.  Otherwise, the ODBC driver will not work.

Brian

[#6]

I also need to export files into different tabs in excel. I saw the 'no solution' solution is back to 2015, but do JMP add this function now 2022? Thanks

[#7]

There seems to be Scripting Guide > Data Tables > Basic Data Table Scripting > Create Excel Workbooks (jmp.com) which should be able to do this

[#8]

Hello,

The function for this is Create Excel Workbook(a, b, c), where:

a = path/name of Excel file to be created or modified,

b = a list of data tables to export, and

c = list of desired tab names.

Cheers,

Brady

Names Default To Here( 1 );
dt1 = Open( "$SAMPLE_DATA/Big Class.jmp" );
dt2 = Open( "$SAMPLE_DATA/Abrasion.jmp" );
Create Excel Workbook( "$TEMP/MyWorkbook.xlsx", {dt1, dt2}, {"Big", "Abrasive"} );

[#9]

Certainly there are _many_ ways to do this - One way is via R and the very handy RDCOMClient package.

// muitileExcelSheets.jsl
// adapting one example available here:
// https://learnr.wordpress.com/2009/10/06/export-data-frames-to-multi-worksheet-excel-file/
R Term();
R Init();

# send JMP data tables to R
dt = Open( "$SAMPLE_DATA/Big Class.jmp" );
R Send( dt );
Close( dt );

R Submit( "dt" );

dt2 = open("$SAMPLE_DATA/Tiretread.jmp");
R Send( dt2 );
Close( dt2 );

R Submit( "dt2" );

// http://www.omegahat.org/RDCOMClient/

RDCOMClient = R Submit("library(RDCOMClient)");

if(RDCOMClient==-1, throw("This Add-in requires the RDCOMClient R package."));

# kick them to Excel on separate sheets

R Submit("\[
  library(plyr)
  testlist <- dlply(iris, .(Species))
  d_ply(iris, .(Species), function(df) {
   assign(as.character(df$Species[1]), df,
  envir = .GlobalEnv)
  })
  testchar <- as.character(unique(iris$Species))
  testchar1 <- paste(testchar, collapse = ',')
  testchar; testchar1;
  library(RDCOMClient)
  source('http://www.omegahat.org/RDCOMClient/examples/excelUtils3.R')
  xls <- COMCreate('Excel.Application')
  xls[['Visible']] <- TRUE
  wb = xls[['Workbooks']]$Add(1)
  # export one dataframe as multiple sheets (breaking on a column value)
  rdcomexport <- function(x) {
   sh = wb[['Worksheets']]$Add()
   sh[['Name']] <- as.character(x$Species[1])
   exportDataFrame(x, at = sh$Range('A1'))
  }
  d_ply(iris, .(Species), rdcomexport)

  # exporting individual data frames converted from JMP tables above.

  sh = wb[['Worksheets']]$Add()
  sh[['Name']] <- as.character('Big Class')
  exportDataFrame(dt, at = sh$Range('A1'))
  sh = wb[['Worksheets']]$Add()
  sh[['Name']] <- as.character('TireTread')
  exportDataFrame(dt2, at = sh$Range('A1'))
]\");

// R Term();

Best regards,

-Matt