BookmarkSubscribe
Choose Language Hide Translation Bar

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

0 Kudos
jimloughlin
Community Trekker

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.

0 Kudos

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

0 Kudos

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

0 Kudos
Highlighted
mattflynn
Community Trekker

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