LibreOffice -- Updates and Questions

[Michael]

To download, go to: https://www.libreoffice.org/download/download/

They generally offer two versions:

• The newest will say something like

  If you're a technology enthusiast, early adopter or power user, this version is for you!

  Use this at your own risk.
• The other will say something like

  This version is slightly older and does not have the latest features, but it has been tested for longer.

  This is the one we recommend.

For Linux, even though the latest versions can be downloaded and installed from the LibreOffice web site, it is recommended that the version in the Linux repository be installed, which is usually an older version.

---

Make sure you are opening LibreOffice Calc:
or

rather than LibreOffice:




You may find that the spreadsheet (ods) opens in Excel by default.
It will not work correctly in Excel and if you save it it will corrupt the file.
You can either open it from LibreOffice Calc or from File Explorer by right clicking, selecting Open with, and then LibreOffice.

[Michael]

I work with several different LibreOffice versions on Windows, Linux, and ChromeBook. They all function the same, except for the shortcut keys.

[Michael]

This may apply to no-one but me, but...

In order to enter the data for Bear 1920, which was scanned by Kevin, using his phone, I had to work in an unconventional way. I imported my calculating spreadsheet, Excel, into LibreCalc, and I entered the data into Calc. When I had entered all the data for the year, I then imported the Calc spreadsheet back into Excel. Why do this? Because of the cell phone images, the log images were right side up, upside down, and/or horizontal left or right. The pages weren't flat, so the grid was curved, and the magnification wasn't consistent from image to image. No fault of Kevin, it was a case of the technology, and I just couldn't use the standard LibreCalc spreadsheet with an image in the background with a nice grid overlaid.

What I noticed was going from Excel => Calc => Excel caused some problems. The main one was that the Excel spreadsheet should have had about 4,000 rows and 30 columns. At the end of the process, it had 1,000,00 plus rows and some huge number of columns. Any operation like a Find or Find and Replace was incredibly slow and, depending on just what I did, caused a crash.

In order to work around this issue, I had to copy the data from the Excel spreadsheet into a new Excel spreadsheet, close the original one with the million rows, and then save the new one with the original name.

So, if you have to import a Calc spreadsheet into Excel, be aware.

[Michael]

Version 2.9 of the spreadsheets have been released, thanks to Gordon's hard work.

The updated versions are in the appropriate location for your ship and year. We do encourage you to get the latest version. Also, while you're at it, you should download Version 2.6 of the Shortcuts file. This will save you lots of typing. The instructions for loading a shortcuts file are in the Readme.odt file, which is in the same directory. If you have the Shortcuts file loaded, Alt-h will show you what the V2.6 shortcuts are. Unfortunately, there is no way to show you the version number of your last loaded shortcut file. It could be 2, 2.5 or 2.6. ::)

Angle Adjustment

Thanks to Stuart, who asked for this. We thought it was a great idea.

When the angle is adjusted for a page, the same adjustment will continue to be made to following pages until the adjustment is again changed. This should help with logs that have many pages needing the same rotation applied.

Pressure Data Auto-Fill

Are you tired of having to enter the full pressure when your ship has been sitting in port for weeks and they are reporting every four hours? We were, so probably you were, too!

The allowable gap between auto-filled entries has been increased from two hours to four hours.

When pressure data auto-fill is enabled on the Setup page, just the final two digits of the pressure can be entered and the first two digits and the decimal will automatically be entered for you. For this to work there must be a prior entry containing the full four digits.

CAUTION: If the pressure difference is greater than 0.50 between the two digit entry and the previous four digit entry, auto-fill will fill in the wrong value. So with this bigger gap being allowed, it becomes more critical that the transcribed pressure entries be carefully proof-read the entered value. The reason we allowed a gap of only two hours is that we were unable to find a single case of a two hour pressure change of 0.50" or more, looking at several hundred thousand pressure records.

Are you tired of having to go back to the "bad" pressure cell if something goes wrong? No more!

When an error occurs entering pressure data, control is now returned to the cell in which the error occurred. Previously following an error, the selected cell would be the next cell, which depended on which key was used to enter the data. This should help with finding which cell contains the error.

Are you having issues with three digit pressures? We were.

If the AutoPressureFill feature was enabled and three digits entered, the first digit was converted to a decimal and then the pressure expanded to four digits, with the decimal in place of the first digit. I.e 958 became 29.58.

Now if three digits are entered, the entry remains untouched so that you can determine the necessary correction during proofreading.

Return to bad directory cell

Michael go tired of this when he was testing our 17 different Version 2_9 spreadsheets. He was always having to go back to the Setup Page if one of his directories had a typo and he was on the Log Page testing a Get Image or Write Data.

If a User or Image directory shown on the Setup sheet is invalid and you try to get an image or write your data, you used to get an error message. Now you get the message AND you are taken to the appropriate cell on the Setup sheet so you can fix the name of the directory.

Writing a Data File more than once for the same log page

This wasn't bothering you, but Michael's decoder wasn't impressed with trying to decode several XML files for the same log page. So, save a page as often as you want. Michael's decoder doesn't have to stress about it anymore. ;D

When data is saved more than once with the Write Data button for the same page, the data should be written to the same file. If a new log page image is loaded into the spreadsheet and then the previous page reloaded, Write Data should write to a different file from the file produced earlier.

Instead data has been getting written to a new file every time the Write Data button is pressed.

This was caused by a long-standing bug in LibreOffice that was corrected in a recent version. However, when the bug was fixed it caused the OWA spreadsheets to exhibit this wrong behaviour.

Write Data now works the same regardless of which version of LibreOffice you are using.

Put YYYY/MM/DD near the top of the Event Page for Logs that have the Weather and Events on separate images.

When you're working on those logs that have separate images for the Weather and Events, are you like me and you can't remember the date that was on the Weather page when you're on the Event page? That's no longer an issue!

For logs that have weather and events on separate pages, the date is shown at the top of the Event Page for reference. Otherwise this cell is blank.

Data Output Files

This means nothing to you, but it's a big help to us.

The output XML files now contain the operating system and LibreOffice version used when transcribing the data. This information can be valuable if things don't work as expected.

[Michael]

Version 2_91 has been released.

Spreadsheet crashes

Would you feel happier knowing that Ctrl-Z won't crash your v2_9 spreadsheet?

Version 2_9, on which Gordon worked long and hard, uncovered a bug in LibreCalc that occurs with the undo command Ctrl-Z. Hanibal discovered that doing a Ctrl-Z in version 2_9 caused the spreadsheet to crash. We traced the problem to having a Selection Changed Sheet Event enabled, which was necessary to run part of the code that Gordon had developed. Their bug, not ours, but a bug nevertheless.

Version 2_91 has disabled that part of the code, and the Selection Changed Sheet Event has been turned off. When AutoPressureFill was turned on, that part of the code returned the user to a bad pressure cell after an UpAndOver command, Alt-Up Arrow, or a Mouse Click was issued. You will still be returned to the bad cell after a Tab or Enter is issued.

Spreadsheet slow

Is your v2_9 spreadsheet running slow when AutoPressureFill is enabled?

One user noticed that the spreadsheet was slower with this option enabled, so version 2_91 is faster. Having the Slection Changed Sheet Event enabled slowed the system down for some users, and this is turned off in v2_91. Also, if AutoPressureFill is not being used, i.e. set to No on the Setup Sheet, the Content Changed Sheet Event is turned off as well, making the spreadsheet even faster.

CopyDown will now move to the top of the next column

Are you tired of having to do an UpAndOver when you've just reached the bottom of a column with CopyDown?

This was Stuart's request.

If CopyDown, Alt-1 or Alt-], filled in the last hour (12 or 24) of a weather block it just remained in place. Now it will call an UpAndOver and move to the top of the next column. You still need to do the UpAndOVer, Alt-UpArrow, if the last Hour after a CopyDown is not Hour 12 or 24, or if you had to enter the data in the last hour yourself.

There is another Shortcut key for UpAndOver

Are you an Alt-1 CopyDown person and Alt-UpArrow is just too far away to activate UpAndOver?

Version 2_7 of the Shortcuts file has Alt-2 as well as Alt-Up Arrow activate an UpAndOver.

To load the Shortcuts_v2_7.cfg file once you've downloaded it, in your Calc spreadsheet:

1. Go to Tools/Customize
2. When the Customize box pops up, go to Keyboard, click on Load, and select the Shortcuts_v2_7.cfg file and Click Open and OK.

[Michael]

When we allowed AutoPressureFill to work with a gap of four hours between readings, we were aware that on occasion the pressure could change by 0.50 inches or more. If the difference is exactly 0.50 inches there is a 50/50 chance that the computed pressure will be wrong. If the difference is more than 0.50 inches, the computed pressure will be wrong.

Here is a case from Storis on 12 January, 1952 moored at Juneau. Transcribers using AutoPressureFill to save typing are cautioned to keep an eye out for these cases. They aren't common, and are more likely to occur in winter than summer. The fix is easy, enter the correct pressure. AutoPressureFill goes to the nearest pressure with the given decimal value. In this case 29.68 is closer to 30.14 than 29.14.

[Michael]

Version 3_0 has been released.

Black strip annoying?

Would you like to improve the Feng Shui of your log page image?

Stuart didn't approve of the wide black strip on the left side of the log page images, and wondered if we could get rid of it. As well as being unsightly, the wide strip pushed some of the right hand columns out of the window.

It took a while, but we did it. On the Setup sheet there is a Crop Yes/No cell. Set it to Yes, and images will be cropped according to the amount set in the four cells, one each for Left, Right, Top and Bottom. The cropping will not affect the image file itself, only the part which is loaded into the spreadsheet. That means you can change the value until you find the optimal amount. You need to Click on the Get Image button to reload the image to see your changes. The units are funny, but for my PC and scale setting, a value of 10 will crop about 6 cm of black.

Since you really want to know, 10 units on the Setup sheet equals 1000 Calc units where 20 Calc units represents 1/96^th inch. Many of the log pages were scanned at 72 pixels per inch. Feel free to do the arithmetic, and don't forget to take into account the value of the window zoom, the image scale and the resolution of your screen.

As a bonus, you can crop a negative amount. Why do that? It allows you to shift the image further to the right by adding white space to the left of the image. There has been a case where that would have been handy.

Every image will be cropped the same way until either the Crop option is set to No, or a value in one of the crop cells is changed.

UpAndOver goes to the wrong place

Do you get annoyed when UpAndOver goes to PM_Start instead of AM_Start?

This took a long time, but Hanibal found a case when UpAndOVer would go to PM_Start instead of AM_Start on a log page with all the weather in one block. PM_Start is needed for those log which have the weather in two blocks. Hanibal's case required a certain special combination of blank lines, but we have caught the bug and banished it from our macro codes.

Badly timed error messages

Don't you hate it when you get an error message about an incorrect or missing value in a cell and you have to remove the message before you're taken to the cell in question?

Now you are taken to the cell with the error, and then the error message is displayed. That way you don't have to remember what the message said, you can see it while you make the correction.

If those wide black strips are getting you down, you can get Version 3_0 in the usual Spreadsheet directory for your ship and year.

---

Now that we have implemented all Stuart's latest suggestions, we await his next idea, or yours, with bated breath and eager anticipation. ;D

[Michael]

Version 3_1 has been released.

Note: to use the new shortcut keys, you need to download and install Shortcuts_V3_1.cfg. This file is located with version 3_1 of your spreadsheet.

Would you like to better position the weather and Event areas?

We added a new shortcut key, Alt-J. Alt-J will place the image so the first weather block will be positioned so it's at the top corner of the window. On my spreadsheet, I set the Scale to a value such that I get all the main weather block on the page, plus the row below. By having that extra row, the position of the image doesn't change if inadvertently I go one row too far down.

The shortcuts for going to the PM weather block, for those logs that have separate weather blocks for AM and PM, and for going to the main Event area will automatically set the page in the window. The top of the PM weather block will be at the top corner of the window. For the Event area, the left hand edge of the Event area will be at the left hand edge of the screen. The top of the Event area will down the page enough so that the Event Wizard will be above the text, so you don't have to move it around as you work down the page.

"But," I hear you say, "I want to see the Green and Yellow cells at the top of the page when I'm transcribing the weather data." Have no fear, just use Alt-LeftArrow or Alt-Home and cell A1 will be at the top corner of your screen.

Do you need a better way to zoom in so you can read that illegible scribble?

There are several ways to zoom in and out:

1. Hold down the Control key and roll the mouse wheel one way or the other;
2. Move the position of the slider at the bottom corner of your spreadsheet. No slider for zoom there? Go to the menu at the top of the screen, View, and click on Status Bar; or,
3. Use Alt-Z to zoom in a set amount, Alt-Shift-Z to zoom out or Ctrl-Shift-Z to set the zoom back to 100%.

"But what is that set amount, Michael?" I hear you say. There is a new line on the Setup sheet for a Zoom Step. The default is 10%, but you can set any amount between 5 and 50%. I set my step so that when I do an Alt-Z, the zoom is such that the Event area expands to fit the window horizontally. I can then do Alt-Shift-Z when I'm on the weather page, and my 24 hour weather block fills the window vertically.

Do you wish that you could automatically jump to the top of the next column of weather data?

If you use the CopyDown macro, Alt-[ or Alt-1, when you're at the bottom of the column, the data above is copied into the cell for Hour 24 and you are automatically taken to the to of the next column.

Little improvements

1. Display Help, Alt-H, lists the new shortcuts, and some of the items have been moved around for an improved reading experience. Instead of getting a message box that didn't display properly on some systems, you are taken now to a Help sheet in the spreadsheet;
2. When you set the first data cell in a weather block with Alt-S, you will get a more readable message, if you have the Advisory Prompts on the Setup sheet set to Yes;
3. The LatLong Wizard has Not Specified set as the default Subtype;
4. The Event Wizard's default position is now very close to the top of the screen. It saves having to move it around as much as before as you work through the Event area.
5. You get a more detailed error message if the Index or Page numbers for a log page are out of bounds;
6. Panay II has headings at the top of the PM weather block.

Two bugs recently deceased.

The two bugs who recently succumbed to their injuries are:

1. If you typed a value in a cell but did not exit the cell with an Enter, Tab or Mouse Click and you did Alt-R to see the WR Data sheet, the value was not saved. Now it is; and,
2. If you were using the Crop feature, the cropped image was stretched to fit the original size of the image. Now the image size is recomputed, and there is no stretching. This was a problem because the rows and columns needed to be adjusted for the cropped image, but no longer!

You can get the new version of you spreadsheet in the normal place. Don't forget to download Version 3_1 of the shortcuts, it's in the same place. To load the Shortcuts, on Calc's main menu, click on Tools/Customize. Select the Keyboard tab, and click the Load button on the right hand side of the box. You then have to select the Shortcuts_v3_1.cfg file.

When all is said and done, do Alt-H and read the new help file. They're mostly shortcut keys, but even a committed mouse user will find something useful. For example: Ctrl-Shift-J. This is a Calc hotkey, not one of ours. It toggles the Calc window so all the various toolbars disappear so you get the maximum amount of the logpage on the screen. Ctrl-Shift-J reverts back to your previous view.

[Michael]

Version 3_2 has been released.

We listen to your feedback and work hard to maximize your enjoyment with our data entry spreadsheets.

Hanibal wanted the position of the Event Input Wizard to be configurable as it is for the Lat/Long Wizard. He also suggested some improvements to wording on the Setup sheet, and an alternate softkey to open the Event Wizard. These were great ideas, so we implemented them immediately.

While we were at it, we decided to make the position of the Sea Surface Temperature Wizard configurable as well. We haven't needed to use it for years, but Burton Island is now reporting Sea Surface data in its own table, so the Sea Surface Temperature Wizard is again in vogue. We also added a new shortcut key Alt-M to open that wizard.

Note: to use the new shortcut key Alt-M, you need to download and install Shortcuts_V3_2.cfg. This file is located with version 3_2 of your spreadsheet.

Note: There has always been an alternate sofkey, Alt-E, to open the Event Wizard, and the secondary key is Alt-;. If you right-click on the spreadsheet, only the Alt-; key shows as the shortcut key which opens the Event Wizard. This is due to the way Calc works, and not us. Nothing we can do about it. Alt-; was used as an alternate because early versions of LibreCalc did not recognize the right hand Alt key, and it was easier to use ; with the left hand Alt key than it was using E. Easier, that is, unless you have a German keyboard. I keep Alt-; as a secondary key because Alt-E is used by my system for other things. ;D In order to see any other secondary shortcut keys, check the Help sheet with Alt-H.

Would you like to better position the Event and Sea Surface Wizards?

There are new lines on the Setup Sheet so you can configure the position of these two wizards when they're opened.

Did the listing of the configuration options on the Setup Sheet offend your sense of order?

In order to add four new lines for configuring the positions of the Event and Sea Surface Temperature Wizards, we re-arranged the order of the list of configuration variables. We also removed the line for adding dittos. That option was superseded long ago by the CopyDown macro, and is no longer relevant. For a long time we just added new options to the bottom of the list. We did this so you could copy your preferences directly from an old spreadsheet to a new one, but these recent updates meant a change was needed. So, make sure the configurable variables are the ones you want. Don't try to copy your old set straight into Version 3_2.

For those working on Burton Island, the changes to the Sea Surface Temperature Wizard will be very beneficial. For those of you working on other ships, your only benefit will be having a configurable Event Wizard position. If that doesn't turn your crank, there's no need to rush to version 3_2.

[Michael]

Updates to v3_2

Stuart noticed a change in behaviour with CopyDown (Alt-1 or ALt-[). It used to act like pressing an Enter key if you used it in the first hour after typing in the value. I.e. it would save the typed data and move down to the next row. In version 3_2 CopyDown would move to the next non-blank column. We put it back so it would act like an Enter Key in the first hour. While we were at it, we fixed its behaviour in the very last data cell in a weather block. It used to do an UpAndOver and go outside the weather block, now it just goes down one row.

Hanibal caught a bug when a change in a cell name wasn't adjusted in one of the macros. A Save (Write Data) when no location was given caused a macro error. That has been fixed. The bug appeared after the initial release of v3_2 and before this final version.

We also improved the wording in a couple of warning or error messages. You get a specific warning in the case when the Archives server is down and an image can't be downloaded. You also get a a better worded message if you use UpAndOver when you're not in a weather block.

A couple of transcribers wanted UpAndOver to skip over the PL and Revs column as it did for blank columns, such as Heel and Leeway. In the rare occasion when you have to enter PL data, you'll have to move back to that column manually.

Finally, there were cases when the Sea Surface Wizard wouldn't save the data properly. This was a Calc issue, not ours, and it depended on which version of Calc and which operating system you were using. We fixed it by converting the saved data in a DOS and not a Unix format. This affected one transcriber working on the Burton Island.

These changes were minor, and they did not warrant a new version number. The updated spreadsheets are in their usual directories.

[pommystuart]

Thanks for the hard work guys.

[Hanibal94]

Just upgraded LibreOffice from 6.3.6.2 to 6.4.6.2.
At first glance, everything is good - no breaking changes.

[Michael]

I've been using it for a while, and it seems fine.

[wendolk]

I'm embarrassed to say, I cannot figure out LibreOffice (after all, I get paid to design databases and spreadsheets ) I cannot find a button, prompt, etc., that enables me to set the path for the output data or the image files - ugh!

[Randi]

Those buttons are in the individual spreadsheets not LibreOffice

https://docs.google.com/presentation/d/ ... 4f984_0_13

[wendolk]

aha - there is a macro in the cell - my husband was enjoying my predicament - along the lines of "and you a highly paid consultant"

For the record, I am NOT highly paid -

[Michael]

Well, once you get a few transcriptions done, you'll find the monthly paychecks from OW will be most gratifying. Your husband will have to eat his words! But that's because with the OW rate of pay, you won't be able to afford anything else!

[wendolk]

I don't know why this is so difficult for me - the macro isn't working for me - I can't seem to enter a recognizable path - does it read carats instead of slashes?

I want a raise! Exasperation pay....

[Michael]

This what my directories look like:

C:\Users\Michael\Desktop\Old_Weather\Ice\Haida\XML\1922
C:\Users\Michael\Desktop\Old_Weather\Ice\Haida\Images\Orig_IMG

I hope this helps.

[Randi]

What OS are you using?

Linux and Mac Guide