[Libreoffice-bugs] [Bug 124461] Creating workbook to workbook links no longer simple.

[bugzilla-daemon at bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=124461

--- Comment #3 from Bob <bob at telpro.co.za> ---
Hi,
After a week of trying my best to avoid the users of the application to be
forced to use Excel, I have had to throw in the towel. I have found workarounds
for most of the issues in Libra and I will describe them below, hopefully to
assist you in identifying the root causes. The remaining obstacle is a time
issue, The files take too long to open and too long to save in Libra, to enable
me to call it a working solution, furthermore although the solution works in
Libra, sometimes it throws a wobbly and crashes, this very frustrating for the
users.

First let me describe the application to give you a strategic view then will
drill down to where the issues are, and what I have managed to discover in my
try and try different again, with much head banging. 

There are 4 reference files, with multiple sheets, that contain data that is
for the most part fairly static, with changes occurring perhaps every four
months. Users require access to these files in order to get reports and create
customer documents, these being dynamic.  These users are dispersed in many
locations. All files therefore are stored on a cloud service. 

I at first created the referenced files as ODS and the dynamic files as OTS. 

In order to cut time, I at first recreated the data that was needed  in the
reference files in the dynamic files by linking to these files using the
///file.... etc linking. This proved problematic with ODS files as sometimes
the data was returned as #REF or #Value or if using the index/match function I
would get data from wrong sheets in the reference file.  I then discovered by
accident a work around by creating the reference files as XLSX, here the data
was never returned as #REF, #VALUE or incorrect ranges. However the OTS took
forever to load, 15 to 20 minutes. 

Thinking that perhaps OTS had an issue I recreated template files with Excel as
XLTX. Libra treats the file as an OTS but cannot save it as an XLTX. However
here I discovered different issues, all URL based, but some complicated by
different OS's 

By this time I had scrapped the idea of loading the reference data as local
data in the template files and all calls were made to the closed Reference
files when needed.

When opening the template file in 6.22 Libra on Windows 7 and 8 it would report
that the file did not exist at the URL echoed. The echoed URL however was
correct. Had me scratching my head for a while. I then discovered the work
around was to change the default working directory path under options to the
Template directory. I also changed the path for the template directory but this
did neither improve or reduce problems.

Now saving the XLTX files as OTS files I could get the users to use either
format as an entry document and links to PDF's etc could navigate correctly
without having to first save the Untitled file. 6.22 finds its way to the root
more effectively than Excel with users drive letters not being a problem once
you change the working directory path to the template directory. 

Excel itself however has a problem with the drive letter unless you first save
the untitled file into the Template directory, which is counter productive. The
template directory should be kept free of clutter. Not a Libra problem, but
here the solution is that the links are all specified without a drive letter
and the first thing the operator has to do is Type in the root drive letter for
their cloud service when opening the Template file. 

So yes I have managed to get the solution to work in both Libra and Excel,
However the Libra solution is not viable. The time to open the XLTX or OTS in
Libra is close onto twenty minutes, about the same time for save. The XLTX in
Excel opens the first time in about 14 seconds and subsequent openings in about
3 seconds. Saving is about 8 seconds. 

I hope my rambling assists. There are probably things I have forgotten to
mention as I am under severe time pressure to finish this project. Please feel
free to ask for further clarification.

Have a beautiful day
Bob

-- 
You are receiving this mail because:
You are the assignee for the bug.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.freedesktop.org/archives/libreoffice-bugs/attachments/20190406/6d0b2b9a/attachment.html>