Upgrading To Latest Version Loses All Files Formatting

[Pugilist_Puppy]

Hi, i have always held back from upgrading from my original and CURRENT version of 3.4.1

Original reasons being it meant that i'd have to laboriously change certain formulas etc - i forget exactly what the problem was, but it was confirmed that the higher version would not carry them out in the same way. A huge accompanying reason was the truly (IMO) awful formatting and tool bars, quadruple the size of the ones i had and looked as if the icons were drawn by a three-year-old with a huge paint brush lol. Also 3.4.1 worked, and still does to this very day, absolutely superbly and i have hundreds of complex documents that work!

I've just changed over one of my computers to Windows 10 and tested importing my 3.4.1 spreadsheets into it, it opened them with Openoffice version 4.0. To my horror not a single piece of formatting gets carried over, and my custom formatting is a crucial part of them. In the same way i couldn't begin to replace/alter countless pieces of code, it would be a mammoth task to add my formatting to every single document. As i type i'm realising that a lot of my cell formulas employ a different colour background based on the result of the calculations. Everything is displaying in black and white, no red or green backgrounds per result of a calculation etc.

Two questions come from the above

1. Is it possible to import the current document's (calc spreadsheet) formatting into Openoffice 4.0?
2. If not, can i install (from my Windows 7 computer) my trusty Openoffice 3.4.1 into Windows 10?

It's irritating enough i''d have to customise all the toolbars and order of the icons etc. and also add links to my Macros that calculate and order my results.

As ever, huge thanks in advance of assistance.

[RoryOF]

In what format do you Save your files?

[Pugilist_Puppy]

ods

[RusselB]

As to your toolbars, those are stored in your OpenOffice profile, which is stored in a different location for OpenOffice 4.x vs. OpenOffice 3.x
The easiest way to handle this, is to make a copy of your 3.x profile, then replace the 4.x profile with the copy of the 3.x

[Zizi64]

1. Is it possible to import the current document's (calc spreadsheet) formatting into Openoffice 4.0?
2. If not, can i install (from my Windows 7 computer) my trusty Openoffice 3.4.1 into Windows 10?

You can try it by usage the Portable versions of the Apache OpenOffice and/or the LibreOffice.

[RusselB]

The .ods format should be keeping your cell formatting and styling so that it works the same in 3.x and 4.x
Do you have a sample file that you can upload for us to test with?
Please read [Forum]How to attach a document for information regarding making a file anonymous and file size limitations, along with options for getting around the file size limitations, if needed.

[RoryOF]

How are you Saving as .ods? Are you simply changing an .xls extension to .ods?

[Pugilist_Puppy]

How are you Saving as .ods? Are you simply changing an .xls extension to .ods?

You've lost me on the mention of 'xls'.

The documents i refer to are 'Openoffice calc spreadsheets' i don't have excel.

So i open Openoffice, then i select File>New>Spreadsheet and when i save that document it automatically saves as .ods Every spreadsheet i've ever saved or used has been suffixed .ods

Aha! interesting experiments to try on earlier replies above, i shall have a dabble and come back with the outcome. Thank You all.

[Zizi64]

Pugilist_Puppy, please upload sample files and screenshots here.

[RoryOF]

On Forum we sometimes get users who think that a file format is changed by simply substituting the extension letters for another format. This leaves the file in the original format.

My question was to determine if in your case the files were originally .xls format, which OO can write, but attempted to be changed to .ods by such a method. Your answer confirms that this is not the case.

[Pugilist_Puppy]

Aha! ok re the file saving query. I've been away busy for a few days and will try above suggestions in a day or so and report back. Thank you again

I must add i've dipped in and out of these forums over the years and it's remarkable to see the same handful of 'famous' names still assisting countless people, that's really something to be respected, thanks again.

[RoryOF]

Using the native OpenOffice file formats, there is no reason why formatting should not be preserved from OO version to OO version. If you have at about the same time upgraded your operating system, the changes may be due to the different fonts installed by the new operating system version. OO does its best to match with closest fitting fonts, but there can be obvious formatting differences, particularly in tight or detailed formats. Installing the former fonts to the operating system should restore the formatting.

[Pugilist_Puppy]

I'm going away soon for a month and i'll be wanting to transfer several documents to my laptop too, so i'll try all the suggestions above. I may quickly try tomorrow copying on to a USB stick my profile and the documents i'll be using when away and see if i can indeed place it in Open Office 4.

Incidentally, in the last few years every time i've been away from home i have transferred documents (calc spreadsheets) from my Windows 7 Desktop PC to my Windows 7 laptop. A lot of the formatting was carried over to the later version, not all mind, but enough to be temporary usable. However, as mentioned, i detest the 'new' toolbars but it sufficed as a working document while away. The two major problems i have in transferring from my Windows 7 to Windows 10 PC is that 1. Everything is black & white, absolutely everything. 2. I will have to laboriously recreate all my toolbar icons as i will be using Open Office permanently on the PC.

Furthermore i've just been struck with horror lol, at the thought that NONE of my macros will transfer and work - unless some of the suggestions above help. Possibly one of the main reasons i never moved on to later versions of Open Office is because i found out, was told, that my code/macros would not work on the new version. That is an awful thought as i have some superb macros that do so many clever things, which is why i absolutely love Open Office Calc. Incidentally i will of course be upgrading my laptop from W7 to W10 in January.

Ok i'll work away on the suggestions and report back, thank you.

[John_Ha]

1. Formatting does not change between versions of AOO - look for something else which has changed. What has changed is the PC and as Rory says it is almost certainly the second PC does not have the font you are using on the first PC. Remember that the font showing in the font drop-down selection box is the font the document is asking for and not necessarily the font being used to create the display because if the font being asked for is not installed on the PC, Windows (or other operating system) will silently substitute a different font which is available, and use that substitute font to display the text.

The TestFonts add-on is invaluable for finding missing fonts which the (Writer - may not work in Calc) document is asking for, but which are not installed on the PC. You can see which fonts are installed on a Windows PC by Start > Control Panel > Fonts or by clicking C:\Windows\Fonts.

2. If you ant AOO to be identical on both PCs copy the user profile C:\Users\xxxxxx\AppData\Roaming\OpenOffice\*.* from the good PC to the bad PC.

3. Your macros will continue to work across different levels of AOO (apart from trivia where the Regular Expression engine was changed and one line of one of my 30 macros was affected) but you need to copy them to the new PC. Copy C:\Users\xxxxxx\AppData\Roaming\OpenOffice\4\user\basic\*.* to the new PC.

4. W10 has the delightful habit of installing the wrong printer driver during updates. I too have been putting off going to W10 as long as possible and Microsoft decided to punish me by bricking my PC with the latest W7 update. I have two bootable disks installed (my new SSD and the original rotating disk) and the latest update looks for the earliest copy of winload.exe it can see - the one on the 3+ year old non-updated W7 disk - and uses it. Guess what - Microsnot says it is not compatible with the update and refuses to boot the PC. Neither install nor repair disk worked so - they go round in a loop - and the only solution (thanks to Mrs Google) was to unplug the second disk.

[RoryOF]

Moving the OO user profile to a new machine is one step; this must be done after the installation of OO on the new machine, to overwrite the default profile OO will generate. That will move macros and dictionaries etc For most accurate formatting on the new computer the fonts used on the older computer will need to be installed. This is an operating system process, nothing to do with OpenOffice.

[Pugilist_Puppy]

OK i finally managed to grab some time to myself and come back to this. I'm on the edge of marking it solved but not just yet.

1. As suggested i drilled down to copy my 'profile' folder from OpenOffice 3 on the W7 PC, simply using 'Ctrl+C' keys.
2. I renamed the current 'profile' folder in 4 as 'TEMPOLDprofile', in case the transfer didn't work.
3. I use 'sharemouse' to move seamlessly between monitors/PC's so i simply then used 'Ctrl+V' to paste it into the W10 OpenOffice 4 version.
4. Great partial success in that every document, with an awful lot of formatting all produced perfectly. I was stunned and THANK YOU all above.

Only outstanding problem before i can mark it as a complete success, and it's obviously a huge crucial one when it comes to the spreadsheets i use calculations and formulae on, is that any time i click on a Macro that i have 'bookmarked' in the toolbars it comes up with a 'script not found' or 'couldn't be run' error.

Do i now need to search on my W7 OpenOffice 3 for my macros and then copy and transfer them to W10 & OpenOffice 4 ?

Nearly there can anyone give me that final nudge please.

[RusselB]

Macros can be stored in the Calc program or as part of a specific spreadsheet.
If they are in the spreadsheet, then they will remain on the spreadsheet, no matter which version is used.
If they are in the program, then the copy/paste you did for your profile should've did the same thing as you saw for your formatting.

[DiGro]

Since you copied your macro's from the old profile they should be there.
You might want to check C:\Users\your_name\AppData\Roaming\OpenOffice\4\user\basic to be sure

Check your settings for Macrosecurity in Tools > Options > Security

You might want to add some Trusted sources

There also should be a usable 32-bit Java runtime environment (since you ran Win 7 it probably should be there)

Check Tools > Options > Java

[Pugilist_Puppy]

Attaching a picture that shows the error message that occurs when trying to run the macros..

I imported the older Java (jre1.8.0_111) from W7 & OpenOffice 3 to W10 & OpenOffice 4. I then received the Java message as per picture. I selected the older version, however i still get the top error message when trying to run the macro.

Are scripts and Macros not one and the same thing? Do i need to find my scripts and transfer them over too?

[RoryOF]

Select the Java in /Tools /Options /OpenOffice : Java - it may autodetect it in 30 or so seconds.

If the macro comes from an extension, simply reinstall the extension. Program installation and configuration can sometimes be specific to a particular computer; it may be so in this case.

[Pugilist_Puppy]

I had no problem selecting the imported older java. There are no extensions involved. Just re-reading the error message, it's surely a simple case of the 'script' can't be found, so my question become show do i transfer the script from W7 to the W10 OpenOffice. While waiting for an answer i'll go dig around in the W7 OpenOffice folders and see if there is a script folder and try copy and paste anything i can find that seems to fit.

It would appear that some 'macros' or scripts have not transferred over? When i go to 'organize macros' while some of them are there, the ones i currently use are not.

W7 and W10 both have the same macros under this following 'tree'
OpenOffice Basic Macros
V
MY SHEET NAME
V
'STANDARD'
V
SCRIPT NAMES (Macros?)

W7 has the current macros i need to use under this 'tree'
OpenOffice Basic Macros
V
MY MACROS
V
STANDARD
V
MODULE 1
V
SCRIPT NAMES (Macros?)|

Unfortunately W10 only reads as below, missing the script/macro names
OpenOffice Basic Macros
V
MY MACROS
V
STANDARD
V
MODULE 1

[Villeroy]

Macros do not depend on Java. Only the dialog Tools>Macros>Run... depends on Java. Tools>Macros>Organize>Basic lets you run any macro without any Java being installed on the system. With LibreOffice you can even use Tools>Macros>Run... without Java.

[Pugilist_Puppy]

Hopefully this will clarify my mumblings?

[RoryOF]

Why not try again: move Module1 from the Win7 computer to the Win10 computer; you may need to check that you have sufficient permissions to overwrite Module1 on Win10 - it may be necessary to close the OpenOffice quickstarter.

[John_Ha]

All your W7 macros are in the file called C:\Users\xxxxxx\AppData\Roaming\OpenOffice\4\user\basic\Standard\Module1.xba. It is a hidden file.

Is it there? The "empty" file without macros is 2kB so your file should be bigger - mine is 135 kB.

[Pugilist_Puppy]

Latest steps. Remember the W7 is the fully working spreadsheet OpenOffice 3 with all macros fine. W10 is the OpenOffice 4 (now with my V3 User and all formatting now imported to perfection. BUT Macros can't be found.

So, in W7 my Module1.xba shows file size 93 KB
In W10 my Module1.xba shows file size 2 KB.
I renamed the W10 to TEMPOLD and imported the 93 KB from W7 to W10. However, it still produces the error message shown earlier and they are not showing up in the 'Existing' Macros' in Module one as per earlier post.

I'm falling asleep now it's gone 4:30AM but will see what the 'turn off quickstarter suggestion' was when attempting to import again. Will likely report back tomorrow with what transpired. Thanks again everyone.

UPDATE
Ok i may have sorted this, but i'm officially bog-eyed and have to go to grab a few hours sleep with a busy day and evening ahead tomorrow(today). I'll need to run tests changing cell values and recalculating to see if it is working fine. During my above efforts i noticed that the file in W7 was showing as Module1.xba. However in W10 it was listed simply as Module1 with no suffix in view. So when it didn't work i added the suffix to it's name. That made no difference hence i removed it and......it possibly is now indeed running the macro successfully. I'm certainly not getting the can't be found error messages and at first, tired glance, it seems to be adjusting the calculations as per Macro.

UPDATE 2
As i was about to press 'post' for this reply, i thought i'd open up the document again and look under the 'organize macros' and Lo and Behold the Macros i need to use are now displaying in the list!

If i can un-muddle my brain tomorrow and note down the steps i took in importing my OpenOffice 3 User details and whatever else i subsequently pasted it will open up so much as i move on to new computers and laptops that will have Windows 10 on. Obviously in the near future i'll move to Open Office 4 fully but for now it was important to be able to continue using my spreadsheets and Macros as they are in OpenOffice 3.

[Villeroy]

Basic macros are difficult to organize. With Python macros you can simply copy the modules to the right place. The hierarchy of Basic libraries, modules and routines is organized by means of XML files.

Solution 1: Replace the whole directory "basic/Standard" in that user profile with the same directory from the other computer. This would not work with libraries other than "Standard".
Solution 2: Open basic/Standard/script.xlb with a text editor and register your modules.
Solution 3: Organize Basic macros properly in future. Use library "Standard" as a scratch pad and put everything productive in your own libraries. Then you can easily move/copy/import whole libraries between documents, between documents and user profiles and between different user profiles.

[Villeroy]

@Rory
See basic/script.xlc where libraries are registered.
See basic/SomeLibrary/script.xlb where the modules of a library are registered.

[Pugilist_Puppy]

Basic macros are difficult to organize. With Python macros you can simply copy the modules to the right place. The hierarchy of Basic libraries, modules and routines is organized by means of XML files.

Solution 1: Replace the whole directory "basic/Standard" in that user profile with the same directory from the other computer. This would not work with libraries other than "Standard".
Solution 2: Open basic/Standard/script.xlb with a text editor and register your modules.
Solution 3: Organize Basic macros properly in future. Use library "Standard" as a scratch pad and put everything productive in your own libraries. Then you can easily move/copy/import whole libraries between documents, between documents and user profiles and between different user profiles.

When i first started using basic spreadsheets and OpenOffice i was completely new to computers and all associated terminology. I found the references to scripts and libraries etc very bewildering. Even now i've never had the time to read up on and learn from the ground up. I've always constructed things on a need to use basis. I know for sure that my placement of code/scripts/modules are all over the place.

As it stands at the moment, as recently mentioned i have managed to get spreadsheets choc-a-bloc with Macros working and the correct formatting. Also another important document that didn't use any Macros or calculations whatsoever transferred perfectly as per imported profile.

However, i've just placed another document that again has no calculations or Macros and consists solely of text and number entries into the cells and has some Hyperlinks. A strange snag arises, my other spreadsheets that contain hyperlinks all have my desired clear background without any shading. However this particular document has an unwanted grey shading in place as if it has not taken note of my imported profile. In this instance it wasn't hard to go to options and select white for the hyperlink backgrounds. But if i have to do that with a ton of other spreadsheets it would be a problem, especially if there are other things that haven't carried over. I've drilled down into OpenOffice and been comparing settings, but so far i haven't seen any differences with the successful imports. I'll investigate further.

I might spend some of my spare time on vacation to really delve into OpenOffice Documentation and instructions as if i was coming upon it for the first time.

On the fact that i have things stored in the wrong places, i don't know if it's best to leave well alone for my important spreadsheets, or (using a copy) try and move things to the correct libraries and whatnot to where they should be.

Again i must say i'm hugely delighted with getting my main urgent spreadsheets sorted. Thank You