cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Try the Materials Informatics Toolkit, which is designed to easily handle SMILES data. This and other helpful add-ins are available in the JMP® Marketplace
Choose Language Hide Translation Bar
Jaz
Jaz
Level IV

RE: Error Opening Excel File

Created: Jun 18, 2018 08:40 AM  |  Last Modified: Jun 10, 2023 4:13 PM (10850 views)

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
briancorcoran
Staff briancorcoran
Staff
Solution

RE: Error Opening Excel File

Jun 18, 2018 01:17 PM (10809 views)  |  Posted in reply to message from Jaz 06-18-2018

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 ryandewitt
Community Manager Community Manager

RE: Error Opening Excel File

Jun 18, 2018 08:55 AM (10846 views)  |  Posted in reply to message from Jaz 06-18-2018

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
0 Kudos
Jaz
Jaz
Level IV

RE: Error Opening Excel File

Jun 18, 2018 10:07 AM (10834 views)  |  Posted in reply to message from ryandewitt 06-18-2018

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. 

0 Kudos
txnelson
Super User txnelson
Super User

RE: Error Opening Excel File

Jun 18, 2018 10:17 AM (10831 views)  |  Posted in reply to message from Jaz 06-18-2018

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

Jim
0 Kudos
Jaz
Jaz
Level IV

RE: Error Opening Excel File

Jun 18, 2018 10:25 AM (10828 views)  |  Posted in reply to message from txnelson 06-18-2018

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. 

0 Kudos
briancorcoran
Staff briancorcoran
Staff
Solution

RE: Error Opening Excel File

Jun 18, 2018 01:17 PM (10810 views)  |  Posted in reply to message from Jaz 06-18-2018

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