Hi,
I'm having two issues with the Create Excel Workbook call in a JMP script and I'm hoping someone here can give me some insight.
I'm running JMP Pro 15.2.1 on Windows 10. I have a script that is maintaining a pair of invisible tables (::dt_wj and ::dt_ma below) which are updated by the user via a non-modal UI window. One of the buttons on this window is supposed to generate a new excel workbook in the user's TEMP folder via the code below:
::excelpath = Trim(
Run Program( Executable( "C:\Windows\System32\where.exe" ), Options( {"/R", "C:\", "excel.exe"} ), Read Function( "text" ) )
);
temppath = Convert File Path( "$TEMP", Absolute, Windows );
timestamp = Round( Tick Seconds() );
filename = "MAWJ" || Char( timestamp );
filepath = Convert File Path(temppath||filename,Absolute,Windows);
Try(
Create Excel Workbook(
filepath,
{::dt_wj,::dt_ma},
{"WJ","MA"}
);
,//else
Write("Failed to create workbook!\!N");
//Return(Empty());
);
If( !Is File( filepath||".xslx" ),
Write("File does not exist!\!N");
//Return(Empty());
);
Write( "Opening with Excel..." );
Try(
Run Program( Executable( ::excelpath ), Options( Eval List( {"/e", filepath} ) ) )
,//else
Write( "Failed!!!\!N" );
//Return(filepath);
);
Write( "Done.\!N" );
//Return(filepath);
Odd behavior 1: When I run the code as written above (it's wrapped in a function in my code, hence the commented out the Return statements), it passes the first Try block (where it calls Create Excel Workbook) without executing the catch clause, however, the next line of code (testing Is File) returns 0. When I check the temp folder I see a temp file was created, but the filename has a '-' appended to it before the extension: for example 'MAWJ259022-.xlsx'. However, weirder still, if I adjust the Is File test to include the inexplicable hyphen:
If( !Is File( filepath||"-.xslx" ),
Write(Eval Insert("File ^filepath||\!"-.xslx\!"^ does not exist!\!N"));
Return( Empty() );
);
It still fails, even though I can see with my own eyes a 5KB excel workbook with that precise file-path as verified via the log output.
Odd behavior 2: Two of the columns in one of my JMP tables contain large ID numbers (12 and 16 digits respectively) which are being held in Character columns in JMP (since they are not really numbers but identifiers). When I (manually for now) open the excel file in the temp folder, these columns are read into Excel as Numeric or General, even though the column in the JMP table I'm saving from is definitely Character/Nominal. This is problematic since Excel can't handle the 16 digit number and truncates the last 2 digits, and converts the 12 digit number to scientific notation, making both identifiers useless. It seems to me that if JMP is generating the excel file itself, it should be able to set the data-type for the excel column to match the closest JMP-analog data type in the process.
I've tried sidestepping this by generating temporary CSV files for each data table and then pulling them back into JMP via Load Text File, adjusting the columns to read various combinations of Eval Insert("=TEXT(^regexresult[1]^,\!"############\!")") or Eval Insert("='^regexresult[1]^") or even some odd stuff I found about excel's CSV handling like trying tripled double quotes in the column data. None of this seems to get me around excel trying to auto-categorize my data and ruin it, so I'm really hoping there's a way to make JMP do this the right way instead.
There is a third behavior I'd like to sidestep, which is that JMP goes non-responsive until the excel instance launched with Run Program is exited, but I suspect that's a larger issue to solve and it's resolution is secondary to my main goal here.
Any insight or comments/suggestions on things to try would be greatly appreciated, thanks!
Robert Maltby
