[Solved] OO 2.4 Calc Compatibility problem with MS Excel

Discuss the spreadsheet application
Post Reply
adnhk
Posts: 22
Joined: Thu Mar 27, 2008 1:06 pm

[Solved] OO 2.4 Calc Compatibility problem with MS Excel

Post by adnhk »

Hi All,

I am new here hence please pardon my ignorance. I was so happy that OO 2.4 is finally released. Without hesitation, I installed it onto my laptop. However, I also immediately faces problem when trying to save a spreadsheet into Excel format. Please try to open attach sample spreadsheet with both OO Calc and Excel (I use Excel 2003). It will open fine on both applications. Then use OO to save the spreadsheet into Excel 97/2000/XP format and try to use Excel to open the newly saved file again. It will open it up with rubbish. Can some experts and kind souls please advise what could be wrong? Is there anyway to save into Excel 97/2000/XP format that the final results can co-exist between OO and Excel 2003? I am supposed to deploy OO to some 200 staffs (1/3 of the company's strength) for the company I work for, but if they cannot co-exist, then how can I deploy OO since staffs need to share their documents and collaborate... Please please someone please advise me on this... :(


Edit: Please see this for solution:

http://user.services.openoffice.org/en/ ... 274#p19274
Attachments
test.zip
(11.31 KiB) Downloaded 846 times
Last edited by adnhk on Thu Apr 03, 2008 9:09 am, edited 1 time in total.
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: OpenOffice 2.4 Calc Compatibility problem with MS Excel

Post by Villeroy »

The document contains VBMSA-Macros (Visual Basic for MicroSoft Applications). If those features are indispensable you should stay with MS Office.
 Edit: All the VBMSA-modules are empty actually. Whatever you expect to happen when you open the document, it may rely on VBMSA-code outside the document (add-ons?). In that case the code could not even work with Excel on another computer. 
Last edited by Villeroy on Thu Mar 27, 2008 2:00 pm, edited 1 time in total.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
r4zoli
Volunteer
Posts: 2882
Joined: Mon Nov 19, 2007 8:23 pm
Location: Budapest, Hungary

Re: OpenOffice 2.4 Calc Compatibility problem with MS Excel

Post by r4zoli »

I tried on winXP with OOo 2.4 and Excel Viewer, I used suggested by you method - both works, no problems.

After that opened file , I saved as odt closed OOo, opened odt and saved as xls and I can open it without problems in both programs.


adnhk wrote:I am supposed to deploy OO to some 200 staffs (1/3 of the company's strength) for the company I work for, but if they cannot co-exist, then how can I deploy OO since staffs need to share their documents and collaborate... Please please someone please advise me on this... :(
You can get more information on transferring from M$O to OOo issues on OOo wiki Migration Guide
AOO 4.0 and LibO 4 on Win 8
Hungarian forum co-admin
adnhk
Posts: 22
Joined: Thu Mar 27, 2008 1:06 pm

Re: OpenOffice 2.4 Calc Compatibility problem with MS Excel

Post by adnhk »

Hi Villeroy, this cannot be. To prove me point, I am now at home, using Office XP (Excel 2002) to test. Again, I use OO to open up test.xls file. Then purposely select one option from the pull down menu and save the file into .xls 97/2000/XP format.

Now I use Excel 2002 to open it up and voila, it opened up fine. Hence this shows that OO 2.4 saves the file into an xls format that at least Excel 2002 DOES supports, also mean that OO 2.4 supports Visual Basic for MS Applications, or else Excel 2002 will not be able to open this file too right? Now, I immediately use Excel 2002 to save the file again. This time round, the file size drops from about 80kb (the one saved by OO 2.4) to just 30kb. I then use my office's laptop running Office 2003 to open up this file. Voila, it opened w/o any issues.

I immediately have the thought that what if I open this OO 2.4's .xls 97/2000/XP file using Office 2007? Just nice I have a laptop with Office 2007 at home. Guess what, exactly the same problem as with my office's laptop that is running Office 2003. It opens up rubbish telling me some error messages. Again, using the test file saved with MS Excel 2002, it opens up nicely too.

So all the above tests proves that the Excel 97/2000/XP format that OO 2.4 saves DOES NOT work on Office 2003 and above. It however, DOES work on Office XP. And why is this so?

On top of the initial test.xls file, I am attaching 2 more files, one that I uses OO 2.4 to save as OO_save.xls 97/2000/XP format, while the other file that I uses Excel 2002 to save as its native xls format, named Excel_2002.xls. Sincerely hope someone with the knowledge can compare these files and advises if anything can be done to make the .xls file that OO 2.4 save, works in Office 2003 and above. Thanks.
Attachments
Excel_2002.zip
(8.25 KiB) Downloaded 558 times
OO_save.zip
(9.25 KiB) Downloaded 584 times
User avatar
r4zoli
Volunteer
Posts: 2882
Joined: Mon Nov 19, 2007 8:23 pm
Location: Budapest, Hungary

Re: OpenOffice 2.4 Calc Compatibility problem with MS Excel

Post by r4zoli »

adnhk wrote: also mean that OO 2.4 supports Visual Basic for MS Applications
It is not true OOo opens M$ documents, tables with macro does not touch code when save back in M$ format save back. VBA codes not executed in OOo it use own StarBasic macros, or can be programmed in Java, Python ....

Now Only OOo Novell Edition (+OxygenOffice ) has a limited capability (under development) to handle VBA codes, and Office 2007 file transfer. It will be integrated into OOo 3.0 if it finished.
AOO 4.0 and LibO 4 on Win 8
Hungarian forum co-admin
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: OpenOffice 2.4 Calc Compatibility problem with MS Excel

Post by Villeroy »

All your files just open fine. I can not know what is missing if it is not there.
They contain 4 Basic modules. The code is commented out, but that should not matter since all the routines are empty.
This one seems to be complete nonsense:

Code: Select all

Sub Module1
Rem Sub DropDown180_Change()
Rem 
Rem End Sub
Rem 
End Sub
The list boxes have a source range Sheet2.A2:A200 and no linked cell, so you can not do anything with them.
As I stated before: If your documents rely on VBA, then I recommend staying with Excel. Whatever you scripted for those documents. It can not work with anything else but Excel.
Whatever you do with all those list boxes should be doable in a compatible way by replacing the listboxes with cell validation and some sheet formulas.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
h1h
Posts: 40
Joined: Wed Nov 28, 2007 8:45 pm

Re: OpenOffice 2.4 Calc Compatibility problem with MS Excel

Post by h1h »

I tried to open the "bad" file with xl 2003 on a citrix server farm. XL moaned about not readable data and tried to repair. Without success. The combo boxes were gone.
Since I never use them myself I cannot say if this was an issue with OOo2.3 allready.
OOo 2.4 (from OOo-site) on Linux
adnhk
Posts: 22
Joined: Thu Mar 27, 2008 1:06 pm

Re: OpenOffice 2.4 Calc Compatibility problem with MS Excel

Post by adnhk »

Sigh... It seems like no one reading my posts have a copy of Office 2003/2007 to help test it out. I did not write those macros. It is just a sample, taken randomly out of hundreds of forms that the company I worked for is using today. I simply edited the form to delete away my company name, plus those fields to input user's name, dept, approval party, etc. Hence only left with the pull down menus where the problem lies.

To put it short, OO 2.4 (like Oxygen and Novell's OO) can open up quite a few of those documents with VBA macros in the company I worked for, that standard OO 2.3.1 cannot. Just that when I save these documents with macros back into Excel format, it seems like it does not save it back "properly" into .xls format.

From OO's Tools--Options-->Load/Save-->VBA Properties, we can then click on HELP to understand further. From the HELP file:

Save original Basic code again
Specifies that the original Microsoft Basic code contained in the document is held in a special internal memory for as long as the document remains loaded in OpenOffice.org. When saving the document in Microsoft format the Microsoft Basic is saved again with it in an UNCHANGDED form.

The key word is UNCHANGDED. I seriously don't think it is unchanged. Else Office 2003/2007 will be able to open it up after using OO 2.4 to save the document into MS .xls format w/o any issues.
vh_llc
Posts: 2
Joined: Sat Mar 29, 2008 6:43 am

Re: OpenOffice 2.4 Calc Compatibility problem with MS Excel

Post by vh_llc »

Don't know if this will help, I am running Office Professional 2003 (SP3) on XP Professional 5.1 (SP2) and have no trouble open your test file.
I also have no trouble converting back and forth. It would seem that the problem is not in Office 2003 but perhaps some where in the configuration????
adnhk
Posts: 22
Joined: Thu Mar 27, 2008 1:06 pm

Re: OpenOffice 2.4 Calc Compatibility problem with MS Excel

Post by adnhk »

Hi vh_lic,

Thanks for your help in testing the files out. Can you advise if you tried the spreadsheet in OO_save.zip? This is the one that my office 2003 and 2007 cannot open and prompted something like it was trying to repair, the same issue that h1h faces when he/she tested using Excel 2003 (it should not matter if he is opening it up when on a Citrix farm). Thanks h1h for your help in testing these files too.

If you are successful in opening this file using Excel 2003, then this could very possibly be a setting/configuration on my office 2003 and 2007, which I've never actually change any settings btw, as I've left them in their default settings ever since they were installed on my office laptop and home laptop respectively.

Edit: My office 2003 is on SP3 too. My colleague's Office 2003 is on SP2 and both of us (running OS XP SP2 btw) cannot open this file...
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: OpenOffice 2.4 Calc Compatibility problem with MS Excel

Post by Villeroy »

I extracted OO_save.xls to a local FAT32 partition and opened it successfully in Excel'97.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
progcode
Posts: 1
Joined: Wed Apr 02, 2008 11:03 pm

Re: OpenOffice 2.4 Calc Compatibility problem with MS Excel

Post by progcode »

I have the same problem as described in this thread.... but here is some more info that may or may not be helpful...

Excel xls document with macros, can open properly, be edited and saved in OO Calc in xls format with no problems. I can then take that xls file that was saved in Calc, and open it up on my PC that is running Office 2000. No problems. But if I sent that xls file to someone running newer versions of Excel, when they open it, it is full of garbage and gibberish.
mrk
Posts: 7
Joined: Mon Mar 31, 2008 11:41 pm

Re: OpenOffice 2.4 Calc Compatibility problem with MS Excel

Post by mrk »

I have checked your files. I have open the OOo and Excel (office 2003) files with no problem. The pull down menu were working fine. I tried to save the excel file into OOo format and then re-save it back to OOo. Everything was still working fine as the original excel file.
adnhk
Posts: 22
Joined: Thu Mar 27, 2008 1:06 pm

Re: OpenOffice 2.4 Calc Compatibility problem with MS Excel

Post by adnhk »

Hi progcode, yes, this is exactly the same problem I am facing with MS Office 2003 SP2 and SP3, as well as Office 2007 machines. OO_Save.zip is the one that is saved using OO 2.4 in .xls. It does open up properly in OO itself, as well as Office XP (2002 is confirmed by me), Office 2000 (confirmed by progcode) and Office 97 (confirmed by Villeroy).

Hi mrk,

Did you try the OO_save.zip file? That is the one that fails on our Office 2003/2007 but works on older generation of MS Office as described above.

I am still awaiting for vh_lic to confirm on this too.

Thank you all for your help in testing.
mrk
Posts: 7
Joined: Mon Mar 31, 2008 11:41 pm

Re: OpenOffice 2.4 Calc Compatibility problem with MS Excel

Post by mrk »

Yes, I did try your OO_save.zip on my MS excel 2003 and it did work without any problem. I checked and I notice I have Office 2003 SP1 (I actually rarely use MS office). Maybe it is something in newer versions (i.e. SP 2 and higher). let me check on office 2007. i think i can find a copy on some machine.

Ok... i checked also on MS Excel 2007. I opened your file OO_save.zip on Office 2007 without problems. I see everything as I see it in Excel 2003 and OOo.

This is quite interesting. I guess some configuration issue is the problem.
adnhk
Posts: 22
Joined: Thu Mar 27, 2008 1:06 pm

Re: OpenOffice 2.4 Calc Compatibility problem with MS Excel

Post by adnhk »

THANK YOU SO MUCH MRK!!! You are a life saver!!! Problem resolved. Thanks to your discovery that your MS Office 2003 opens the test spreadsheet inside OO_Saved.zip file w/o any issues.

With your findings, it simplifies all my tests by a zillion percent!

I immediately go into Windows XP's add/remove programs, scroll to Microsoft Office Professional Edition 2003, Add or Remove features, Choose "Advanced Customizations of applications", install .Net Programability Support and all the Add-ins. VOILA! It worked!

Now for future OO users facing this issue too, the best solution is to ensure you choose the "Complete" option when installing MS Office 2003 or 2007. Never let the setup wizard install the "Standard" only option, which my office laptop comes with this by our default ghost image.
Post Reply