cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Browse apps to extend the software in the new JMP Marketplace
Choose Language Hide Translation Bar
b55019
Level II

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

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

17 REPLIES 17

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

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

Brian

jimloughlin
Level III

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

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.

Jim Loughlin
Loughlin Consulting

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

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

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

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

Isabel26
Level III

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

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

jthi
Super User

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

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

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

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

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