Error importing Excel 97-2003 files

[#1]

Dear JMP Community,

  (Windows 10 Enterprise, 64-bit, JMP 14.0.0 and JMP Pro 14.1.0.)

  I have hundreds of Excel files that I need to import into JMP using JSL and have a successful code to dot hat, until I came across this problem when running the code on the hundreds of files I have. The code works for the first 542 files, but stops on the 543rd.

JMP Alert window:

There was an error opening the file in the Excel Wizard. Files older than Excel 97 might fail to open. Using the Open All Sheets preference should work in this case.

  I did some searching on the Community pages, and others have seen a similar problem, but their solutions aren't applicable to this case.

Issues: 1) I can't go in an re-save the file to a new format, have to use the existing one; 2) the format is 92-2003 (.xls), which should be able to be opened with the Wizard (all other files are 97-2003 and work); 3) since this is an automated JSL code, I can't modify it to "open all" tabs as that would completely change the whole coding and loop structure (there are multiple tabs in the workbook, and I don't want to open them all.), 4) opening the file using "selected worksheets" does work, but it doesn't give me a source script to work with, so I am not sure how to generate a work-around in order to get the data that I'm after; 5) no one else has the file open; 6) it's not corrupted as I can open it in JMP (using open all) and I can open it in Excel.

  Any suggestions welcome.

Thanks!,

DS

[#2]

The recommended approach is to open in Excel and save it out as Excel xlsx format.  Since that is not possible, you might try setting the Excel open preference in JSL, using the JSL Open command to open the file, and then resetting the preference before exiting the script.

Brian Corcoran

JMP Development

[#3]

Hi @briancorcoran,

  Thanks for your feedback. Unfortunately, I can't even do the Open() command in JSL. If I do that, even selecting the different options under the dropdown menu of the "Open" button, it still throws the same error.

  Is there any issues with JMP opening xls files that have macros in them? The strange thing is all the excel files I'm importing into JMP are (as far as I know) identical, except this one file that won't be opened in JMP. I tried saving it as a xlsx file, but can't because there's internal macros that are required to transfer data to another database. Yes, these same macros exist in all other 500+ files.

  I can confirm that JMP is able to open xlsm files as well (the newer version of xls with macros). There seems to be something quite strange about this one file. Perhaps there is something corrupt with it afterall, but we can't track down what that problem is.

Thanks!,

DS