Forum Discussion
When importing Excel Workbook having Superscript text in Header it does not show the column involved
yeah understood CJW1960 , not surprising but worth asking.
Is there no way to alter the way the XLS files are produced to stop the superscript characters being used? Or to export the data in a different format like CSV?
Phil
Sorry CJW1960 - didn't see you had attached it already. Yes, like PhilipTreacy I get the error. There are twopossible ways to fix it.
- Remove the superscript char
- Convert the workbook to XLSX. I confirmed saving as XLSX fixes the issue.
This is the second confirmed issue I've seen with XLS formatting and Microsoft will not fix. I reported this to them earlier this year and the product team looked at it and basically went "Yup. You are right, it fails, but we aren't going to spend any time coding around an Excel format that was EOL'd in 2007." And I fully understand.
The only suggestion they offered was to install the Access 2010 Engine Redistributable. Links and more info here. That code actually has some stuff for XLS files. However, it did not fix my issue, so no promises here.
Sounds good CJW1960 - I'm doing the same thing with a file I get 2x per month from an outside provider.
Can you mark one/more of these posts as the solution so everyone will know the thread is solved and if they have the same/similar issue will know that saving the file as XLSX is the workaround.
Sorry CJW1960 - I cannot get it to fail either. The data you posted is not a 3 superscript format. it is the ASCII character 252 which is very different. However, I replaced it with a normal 3 and then applied the superscript format to it and nothing would fail.
Your posting data to the forum is stripping something out.
If you are really using an XLS workbook, save it as XLSX. Then see if it works. If it still fails, you will have to share the workbook with us to really test.
- To protect data privacy, you should save it as a new file in the exact same format. (Excel 2003, Excel 2007-2020, XLSM, whatever format it is. Don't change that)
- Delete all unnecessary sheets
- Delete all data around the table so nothing is there but the table.
- You should be able to delete all but the first row of the table.
- You should be able to key in fake data to that first row.
Save it and see if it still fails. If it does, share the workbook via OneDrive, Dropbox, etc.
DO NOT copy the offending sheet to a new workbook and share that with us. That will strip out dozens of hidden things like styles, names, etc that might be the root of the issue. Do what I said above.
Sorry CJW1960 - didn't see you had attached it already. Yes, like PhilipTreacy I get the error. There are twopossible ways to fix it.
- Remove the superscript char
- Convert the workbook to XLSX. I confirmed saving as XLSX fixes the issue.
This is the second confirmed issue I've seen with XLS formatting and Microsoft will not fix. I reported this to them earlier this year and the product team looked at it and basically went "Yup. You are right, it fails, but we aren't going to spend any time coding around an Excel format that was EOL'd in 2007." And I fully understand.
The only suggestion they offered was to install the Access 2010 Engine Redistributable. Links and more info here. That code actually has some stuff for XLS files. However, it did not fix my issue, so no promises here.
Thanks edhans, tried the potential Access 2010 solution but as you said it did not fix the issue.
Think i will create a workaround opening all files and saving before closing using a macro.
Cheers, KeesSounds good CJW1960 - I'm doing the same thing with a file I get 2x per month from an outside provider.
Can you mark one/more of these posts as the solution so everyone will know the thread is solved and if they have the same/similar issue will know that saving the file as XLSX is the workaround.