51352_en-US

liliconcom

Excel workbook with name list

Hi,

I like to create an excel workbook with name of sheets, and files that are defined as a variable name.

 

Here is the script I have:

 

Nitems = 2;
listexcelsheet = {"||file1||","||file2||","||file3||"};
listexcelfile = {"||name1||","||name2||","||name3||"};
if (Nitems == 2,
 
Create Excel Workbook(
"$Documents/JMP/"||product_name||"_"||process||".xlsx" ,
listexcelfile,listexcelsheet
)
);

 

I tried to use Eval() and Parse() and no success. I appreciate your help.

 

Thanks

 

 

Re: Excel workbook with name list

Hi Brian,

Thanks for the note. You are right. I was getting three sheets first and was due to overwrite the previous file. I deleted the excel file and then I run the script and that had two sheets which I did not know. Anyways, Thanks for your help. 

Re: Excel workbook with name listThe table that you attached had two sheets in it. I'm unable to reproduce a case where you get 3 sheets.

Brian
Re: Excel workbook with name list

Hi Brian,

Thanks. I wanted one workbook and two worksheets inside. The script I tried last creates three worksheets. I wanted two. 

 

Thanks for further help.

 

Adam

---

@briancorcoran wrote:

Hi AT,

 

I thought that's what you wanted?  If you want separate workbooks for each worksheet, you would need to just create a loop and call the function individually for each JMP table.

 

Brian

---

 

Re: Excel workbook with name list

Hi AT,

 

I thought that's what you wanted?  If you want separate workbooks for each worksheet, you would need to just create a loop and call the function individually for each JMP table.

 

Brian

Re: Excel workbook with name list

Thanks Brian for the feedback. I tried your script and creates three worksheets.

 

I have attached the output. I have version JMP 13 pro.

 

Thanks

 

Re: Excel workbook with name list

Hi AT,

 

I'm afraid you have run into a Mac only bug that is fixed in the upcoming JMP 14.  The issue is the way the file names are extracted on the Mac.  I imagine you are on JMP 13.  The only workaround that I am aware of right now for this issue is to give the list of names of the tables, rather than the full filename.  In this case, you could reuse your "name" variables but Big Class would have to have a space in it.  An example that works is:

 

 

product_name = "ProductA";
process = "Process1";
 
//Tables need to be open to go into Excel Workbook
file1 = open("//Library/Application Support/JMP/13/Samples/Data/Abrasion.jmp";);
file2 = open("//Library/Application Support/JMP/13/Samples/Data//Big Class.jmp";);
 
//Worksheet names
name1 = "Abrasion";
name2 = "Big Class";
 
//listexcelfile = eval list({file1, file2});
listx = eval list({name1, name2});
listexcelsheet = eval list({name1, name2});
 
Create Excel Workbook(
"$Documents/JMP/"||product_name||"_"||process||".xlsx" ,
listx, listexcelsheet
);

 

 

I'm sorry for the inconvenience this has caused.

 

Brian Corcoran

JMP Development

Re: Excel workbook with name list

Hi Cameron,

I use Mac machine which might be different than Window machine.

 

 

Re: Excel workbook with name list

Thanks Cameron. I am sorry I am still getting an error. I have attached script I used and the lop file says - "None of the specified tables could be found. Save failed".

 

product_name = "ProductA";
process = "Process1";
 
//Tables need to be open to go into Excel Workbook
file1 = open("$SAMPLE_DATA/Abrasion.jmp";);
file2 = open("$SAMPLE_DATA/Big Class.jmp";);
 
//Worksheet names
name1 = "Abrasion";
name2 = "BigClass";
 
listexcelfile = eval list({file1, file2});
listexcelsheet = eval list({name1, name2});
 
Create Excel Workbook(
"$Documents/JMP/"||product_name||"_"||process||".xlsx" ,
listexcelfile, listexcelsheet
);

 

If in Create Excel workbook, I write listexcelsheet first and then listexcelfile, the output is geneated but only has "Abraison.jmp" only. I have attached that too.

 

Thanks

Re: Excel workbook with name list

You got the listexcelsheet and listexcelfile backwards in the Create Excel Workbook() function.  Reverse those and it should work.

When I said you had those backwards in my first response, I was referring to the names.  You seemed to name the list of files "listexcelsheet" and the list of sheets "listexcelfile."  Run my script exactly as given and it should work.

I attached what I got.

Re: Excel workbook with name list

Thanks Cameron.  I tried your script and only creates one worksheet "Abrasion".

Here is what I used:

 

product_name = "ProductA";
process = "Process1";
 
//Tables need to be open to go into Excel Workbook
file1 = open("$SAMPLE_DATA/Abrasion.jmp";);
file2 = open("$SAMPLE_DATA/Big Class.jmp";);
 
//Worksheet names
name1 = "Abrasion";
name2 = "BigClass";
 
listexcelfile = eval list({file1, file2});
listexcelsheet = eval list({name1, name2});
 
Create Excel Workbook(
"$Documents/JMP/"||product_name||"_"||process||".xlsx" ,
listexcelsheet, listexcelfile
);

 

Re: Excel workbook with name list

Hard to say without seeing your exact code.  Here's what I did with 2 tables from the sample data library and both worksheets show up in the workbook as expected:

product_name = "ProductA";
process = "Process1";

//Tables need to be open to go into Excel Workbook
file1 = open("$SAMPLE_DATA/Abrasion.jmp";);
file2 = open("$SAMPLE_DATA/Big Class.jmp";);

//Worksheet names
name1 = "Abrasion";
name2 = "BigClass";

listexcelfile = eval list({file1, file2});
listexcelsheet = eval list({name1, name2});

Create Excel Workbook(
 "$Documents/JMP/"||product_name||"_"||process||".xlsx" ,
 listexcelfile, listexcelsheet,
);

Re: Excel workbook with name list

Thanks Cameron for quick solution. It works but I only get one of sheet created not three and there is no error. What could be wrong? Thanks

 

Re: Excel workbook with name list

I think what you're missing is the Eval List() function.  The way you are trying to get the values of those files is not going to work.  Instead, try this:

listexcelfile= eval list({file1, file2, file3});

Same applies to your definition of listexcelsheet. 

I also think you had your listexcelfile and listexcelsheet variables backwards.