cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
Jaz
Jaz
Level IV

RE: Error Opening Excel File

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. 

 

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions

RE: Error Opening Excel File

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

View solution in original post

5 REPLIES 5
ryandewitt
Community Manager Community Manager

RE: Error Opening Excel File

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

 

import-excel.png

Ryan DeWitt - JMP User Enablement | Creative • Video • Design • Learning
Jaz
Jaz
Level IV

RE: Error Opening Excel File

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. 

txnelson
Super User

RE: Error Opening Excel File

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

Jim
Jaz
Jaz
Level IV

RE: Error Opening Excel File

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. 

RE: Error Opening Excel File

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