Combine Two Dissimilar LO Calc Workbooks With Macros

English
#1

Hello @ LibreOffice Forum

I have two dissimilar Workbooks in .ods format with Macros which I wish to combine so that the functionality of the Macros is NOT impaired. The two workbooks are as follows:-

  1. A shares and fund prices downloader for prices from FT.com and Yahoo.com & Shares.com. It contains Macros and five Sheets This is not my creation but comes from Lemon Fool.

  2. A Portfolio management Workbook to record our (Wife and Self) NetWorth of shares and funds on a weekly basis. It contains Macros and 54 Sheets This is my creation.

I have successfully MOVED the Sheets from (2) into (1) but because of the number of sheets and other factors it takes a very long time. So if the Price Updater changes version it becomes seriously problematic. Generally speaking the Macros work so I can update Prices.

I have recently tried the reverse, that is to MOVE (1) into (2), that is, the 5 Sheets of the Price updater into the Workbook containing 54 sheets but I received a Python Error Message when I pressed the button to update the prices in the combined Workbook.

Could anyone suggest any solutions that might address the issues raised here. Macros I know very little about.

Thanks

Boss

#2

Merge sheets of spreadsheet documents

#6

Use one of my merge document for each of the 3 different sources. One collects data from ft.com, another one collects yahoo, the third one collects shares.com.
Then merge the remaining 3 spreadsheet tables manually.

#7

I will not create accounts at ft, yahoo and shares.com in order to find out which flavour of csv they actually export.
There is not the faintest chance to help with a csv related problem without knowing the exact structure of the csv in question (3 different structures in this case).
The only tool I can offer is my spreadsheet template. It includes a helper macro to get the right import options for any csv import possibly importable with Calc.

#3

Hi Villeroy

I am in your debt yet again. Thank you. It will take some time to read the notes and “play about” with it, but I will let you know if it works for the benefit of others.

Boss

#4

Hi Villeroy

I am struggling to work out how to use the Template you provided but I will keep at it.

As an aside and for others I have been able to “merge” the two workbooks. If I merge the Portfolio workbook into the Updater there is a lot of work to be done tidying up, but it can be done. Doing the reverse, that is merging the Updater into the Portfolio Workbook is much quicker but I am left with the problem that when the Updater is Copied into the Portfolio the Updater “loses” its ability to update prices. In other words the access to the Python Macro is missing.

It isn’t easy or perhaps possible with a normal installation to find out where the Python Macros are located much less edit them (even if I as clued up enough), however I have been reading up about an extension for LO Calc called APSO (Alternative Script Organiser for Python).

With a fair amount of testing I found out that you can copy the Python Script from the Updater using APSO and in the merged version use Substitute from APSO to overwrite the Python script that is not being recognised. Then to reassign it to the update button in the updater part.

The whole process including Copying or Merging took no more than15 minutes.

I hope this helps others.

Boss

#5

As with BASIC macros are either in your profile or in the file (.ods here) itself.
Location in profile is documented and you can check gor a similiar scripts-folder inside your ods, when you remember all LibreOffice-documents are also zip-archives. But using apso is now the suggested way in most cases.
https://wiki.documentfoundation.org/UserProfile#User_profile_content
.
To transfer a macro may be much more complicated, than in your case, as there are different ways to find a sheet. If the macro uses “the first sheet” or ther sheet-numbers you may need to change numbers after merge. Using names of sheets is more likely to work directly unless you have the same sheet-names in both workbooks…
.
Trouble waits also when you use global variable in both workbooks.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.