RE: Error Opening Excel File

[#1]

Hi, 

I'm trying to import my excel file as a data table but apparently the version of excel used is too old. It says: "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."

So, I tried changing the preferences to "Open All Sheets" and the error no longer appears but no files are imported. 

Any help would be appreciated. 

[#2]

It may be corrupt, things to try.

1) In the 1st window of the import Wizard, toggle on Concatenate worksheets, see image below.  

2) Re-export the File in Excel as an .xls vs. xlsx

3) Export as a .csv

[#3]

Hi, 

Thanks for your reply. I would try that but I can't get to the import wizard in the first place as it won't let me open the file. 

Do you know of a way to add a column from a data table into to an excel file without having to turn the excel file into a data table? 

Thanks. 

[#4]

You can always Cut and Paste from the JMP data table to the Excel spreadsheet

[#5]

Hi, 

I could but I would prefer a more automated way that doesn't require human intervention (would rather find a way of doing it solely in JSL).

Thanks though. 

[#6 · Accepted solution]

if an Excel file is older than Excel 97, JMP will try to use some much older code to open an Excel 5.0 file.  That is only available through Open All Sheets.  However, files earlier than 5.0 often fail to open at all.  The simplest solution is to open the file in Excel and use File->Save As to specify either Excel Workbook (XLSX) or Excel 97-2003 (*.xls).  Then save out the file using a new name or overwrite the existing file.  JMP can now open the file.

If you have a large quantity of these older files, then it might be worth writing a small about of VBA script within Excel to batch process them.

If you are sure the file is a newer file, that has perhaps been generated by a product other than Excel, it might not have been properly generated.

I hope this helps,

Brian Corcoran

JMP Development