How to link OLE object to specific sheet from template in Writer?

[at0micbunnygirl]

First, if anyone has a suggestion for a different way to do this, please suggest it! I’m flexible and open to any way to get the same result.

Using Software: Libre Office 5.0.5.2 on a MacBook Pro running OS 13.2 High Sierra, and Android 7.0 on Samsung Galaxy S7 Edge with WPS Office 10.7.1 (Tomato Fish)

Ultimately, I will be creating a single document (template in Writer) which I will be emailing to someone else as a locked PDF once a month. The final document has a logo on it, a few key text items that remain the same, the name of the most recent month (date field in template), my digital signature and date.

The content that changes will be a table with simply three columns and a variable number of rows listing items by date which I want to be able to update in a spreadsheet that I will access on my Android phone using WPS Office (unless someone can suggest a free app on Google Play that’s better).

There won’t be very many entries each month, perhaps between six and ten, so I’d like to capture each month on a sheet within the same spreadsheet. This seems to be my problem. I figured out how to create the template in Writer and insert an OLE object that refers and links to a spreadsheet by name, but I don’t see how to specify which sheet the data is coming from. Each month I’ll add the next months sheet in the spreadsheet (using names such as Jan 2018, Feb 2018, etc.) and would like to find a way to just update the reference of the OLE object to the sheet whose name matches the current month (or that I manually choose).

Is there any way to do this???

If not, my last resort will be to overwrite the data in the same spreadsheet for each month, but to import that data into a new sheet capturing the data into one document first. This seems a bit cumbersome and not a great workflow, but I’ll do it if there’s no other way. Can someone please suggest a slick way to do this please???

Ultimately, once my data from my spreadsheet (from my Android phone) is complete, I’ll transfer it to my laptop within the folder containing the linked file. Then I’ll open a new Writer document from template which will update the date field and choose to update the linked object to display the latest data. Then save to PDF. Once I open the PDF, I’ll date it, then digitally sign it, locking it in the process. I’ll check it once and then email this to my colleague once per month. Is there a better way to do this?

I’d love your tips and advice! Thanks!!!

[Ratslinger]

There are two ways to insert a linked spreadsheet - Create new & Create from file. To which are you referring? With Create from file you get all sheets & with Create new you link to a Data Range (could be anywhere in doc) which can be re-assigned anytime. Not certain I understand where the problem is.

[at0micbunnygirl]

I tried using “Create from file”. When there was only one sheet in the file, it linked to it properly. When I added a second sheet, it just showed a table with no content. I tried adding a few sheets into the original spreadsheet, adding identifying content in each one, then updating the linked file to see which sheet it was referring to, and it still showed no content even though every sheet had data on it.

I haven’t tried Create New yet, so I’ll try that quick and brb. Thanks.

[at0micbunnygirl]

OK, thank you Ratslinger. I figured it out.

I can’t use “Create New” because while it does create a spreadsheet that is editable, it’s not an external file that I can save and edit on my phone separately. It’s an embedded object, editable only within the template.

But after deleting the object from my first template and trying “Create from File” linking the spreadsheet, I discovered the way to access each new sheet that I want to display for printing is by right-clicking on the object, choosing to “Edit” the object, then selecting the tab of the sheet I want. It sometimes take a bit of clicking around in the sheet to get it to update to that sheet, but that will work. Thank you!!!

[Ratslinger]

Please do NOT use an answer (it is for response to original question only) for a response to an answer. Instead use a comment to the answer you are responding to.

You can link using Create new. Once activated, from menu select Sheet->Link to external data and you can link to Range names in a Calc doc. There are different ways to do just what you want including more automated things if you can code macros.

[Ratslinger]

Hello,

It may be your original setup is not quite right. Start with a new Writer doc. Insert a new Ole Object. In dialog presented, select Spreadsheet & the select Create from file & this dialog appears:

Click `Search button & select the Calc file to link to & when selected brings you back to:

Then make sure Link to file is selected & click OK button.

My linked sheet appears as:

When double left mouse click in the object it becomes active:

When activated it returns the currently saved data. Now if you go to the linked Calc file, add a sheet, add some data to it & SAVE the Calc file you can get the updated info in Writer.

After saving the Calc file go back to writer, select anything else except the Ole object (so it is not active) the activate it again (with the double click) & all your changes should appear:

Now in writer, with the active object, just select which sheet to view with a click on its’ tab.

If this answers your question please tick the (upper left area of answer). It helps others to know there was an accepted answer.

[LobaLuna]

Funny, but I had to untick the option ‘Link to file’ in order to get the Calc table shown on my Writer document.
I’m on Ubuntu 18.04, with Libre Office version: 6.1.2.1
Build ID: 1:6.1.2~rc1-0ubuntu0.18.04.1
CPU threads: 4; OS: Linux 4.15; UI render: default; VCL: gtk3;
Locale: es-MX (en_US.UTF-8); Calc: group threaded

[Ratslinger]

@celiapgt I have switched over to Ubuntu 18.04 Mate with almost identical LO to yours (different Locale):

Version: 6.1.2.1
Build ID: 1:6.1.2~rc1-0ubuntu0.18.04.1
CPU threads: 4; OS: Linux 4.15; UI render: default; VCL: gtk3; 
Locale: en-US (en_US.UTF-8); Calc: group threaded

Following the steps in my answer works without problem. If Link to File is NOT checked, any updates in the Calc document will not be realized in the Writer document.