Convert Excel 97-2003 To Excel 2019
Francesca Cruiz
Educate them. Print out some real basic step by step with pictures and instruction maybe even a video. Now you can email and even print out the instructions and have it posted on the monitors of those that you know will have the most trouble. Keep it basic and to the point. Then have a non-techie read over it.
However, if the culture of your company is that you just need to do it then by all means do it. Ill bet you could write a powershell script to connect into the COM objects of all excell files and auto change the format to the latest. That is if MS does not already have a tool to do just that.
I have received around 1700 97-2003 excel files with a ton of data including some text information. I am trying to read them all into Stata before I clean and run some statistical analysis. But, Stata is unable to open them.The files have a .xls format with large amounts of text in them which I want to keep, and convert them into .csv files (hence the csv tag).
I additionally tried to write a bulk line of code on stata to save all files as csv or atleast an xls that can be imported into stata but I have had zero luck. Everytime I try to save them, I get a corrupted csv or an xls with the exact same error that pops up as " try xlCreateBook()" on stata and the "format does not match extension" error when I manually try to open the file.
I am working on a console application which will convert xlsx file into xls file. I don't want to rename it from xlsx to xls because it will get opened in excel 2007 but it will be shown as corrupted file in excel 2003. Looking for a way which will load the document and then it will be saved as xls format.
Use read range and then write range(you can change the extension here .xls) (use activities under workbook) its working but when you open the file it says corrupted so no data in excel.
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.
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.
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.
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.
I then click "Convert to PDF", and I am promtped for a file name and location. After completing that, I click "Save". Then the following window pops up, but where the process used to continue, nothing further happens.
p.s. i don't think the adobe website, and forums in particular, are easy to navigate, so don't spend a lot of time searching that forum list. do your best and we'll move the post if it helps you get responses.
- When you choose to save as PDF, you get the dialogue box/window asking which sheet/sheets you want to include in the PDF. Yours only has one but if you had more, you could choose which ones to include.
- You also get to decide how the sheet is displayed on the page(s) such as to puts the whole sheet on a single page, as you have selected.
- Once you save to the chosen location, the processing window appears until the save is complete.
- In my setup, the PDF then opens in Acrobat. That's because in the save to location window, I have View Result selected. If you don't have that selected, just go to your selected folder in file Explorere and you should see your PDF that you should then be able to open.
I had the same issue today and searched for a solueiton and saw this thread. I finally figured out what my issue was. It was to do with how the excel file was saved. It was saved as a Excel 97-2003 worksheet. I saved as Excel Workbook (*.xlsx) file and was able to save as PDF after that. I went and checked some other files that were saved under an older version and I could not save as PDF with those as well, unitl I changed the excel file type to the .xlsx file. My guess is 97-2003 is to old school to keep up. lol.
Supported, creating / manipulating MS Excel charts are supported, the charts would be rendered in the native Excel format, so if you open the Excel file into MS Excel, you may edit the charts directly in it. See the topics in the sections: Creating charts
Supported, you may call Workbook.caculateFormula() method, it will calculate the results of the formulas, then you may simply use cell.setValue(cell.getValue()) method as it will replace the formulas with calculated results/values. See the document:
Hi,
The issue of the files not opening got resolved. But the macros are still not copied in the XLSM files when converted from 2003 file. Could you please look into this.
Also, one more question, is it possible to run macros?
Thanks,
Kulbhushan Singhal.
Hi,
I can see the problem with 2003 XLS file to 2007 XLSM file conversion. The problem is logged in our issue tracking system with an ID CELLSJAVA-18917. Once it is resolved, we will let you know.
Hi,
Regarding running macros, the link you provided is for .net and not java. And I could not find any method there that can help me in running macros code.
Regarding protecting workbooks, everything works fine for Excel 2003, but for Excel 2007 after entering the password to open the file, I get the following error:
We
are looking at the feature of keeping macros when converting from one file
format to another. We will give you an eta if we can support it soon. For running
macros, I am afraid, it is not supported at the moment and cannot be supported soon.
For the issue of chart2image, it is because currently theJDK's Image APIs do not support transparent background for JPG format. I thinkyou can try other image formats such as png which can show the image correctly.
For the issue of saving macros, we will support saving themacros when reading from excel2003 files and saving to excel2007 files in the nextweek. For reading from excel2007 files and saving it to excel2003 files, it is morecomplicated and we cannot support it soon or shorter time.
Within excel I have tried setting the column in question from General to Text format with no improvement. Because excel source chokes on this particular column I have found that adding a data conversion transform and doing a redirect on failure after the excel source transform to be useless.
Are you sure it's choking on the destination? In the advanced editor, you might want to check both the external column and the output column on the excel spreadsheet and the external column on the destination. These default to 50, IIRC, and may need to be altered if the column is larger.
If I enable the mapping for this column in the Excel source and subsequently on the OLE DB destination, then it fails at the source. If I disable the mapping on each end then all the other columns flow.
So, in the excel source, in the advanced editor, it probably shows a mismatch between the actual column size (which isn't shown anywhere), the external column size and the output column size. Truncation will occur, and the task will stop, when it tries to move the external column to the output column. If you right click on the task and bring up the advanced editor, you can change the column lengths for those 2 entries and that may take care of the problem.
Next, I went to excel source advanced editor and attempted to edit the external metadata for that column from Unicode string [DT_WSTR] 255 to 800. But, external metadata just reverts back to 255 when I close the dialogue box. I upped the metadata for Excel source column's output to 800 and while it 'sticks' the package still fails at source.
I have been in scenarios where the Excel source transform passes the data through and then a column fails downs stream, and for this scenario I use data conversion transforms. But I am not getting past the ingestation stage even.
[Excel Source [14]] Error: There was an error with Excel Source.Outputs[Excel Source Output].Columns[Asset Description] on Excel Source.Outputs[Excel Source Output]. The column status returned was: "Text was truncated or one or more characters had no match in the target code page.".
[Excel Source [14]] Error: The "Excel Source.Outputs[Excel Source Output].Columns[Asset Description]" failed because truncation occurred, and the truncation row disposition on "Excel Source.Outputs[Excel Source Output].Columns[Asset Description]" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
c80f0f1006