[Solved] Use Base to create a Calc spreadsheet

[doncell6]

I am a novice at using LibreOffice. In reading the documentation it seems Base can read Calc spreadsheets but not write to them. So, I am wondering: How would one use Base to create a Calc spreadsheet?

In other words, what interoperability is possible between Base and Calc?

If this is not possible, where could I find information about creating Calc documents using something like MySql?

Thank you for your help.

[FJCC]

It's easy to import data from a database to a calc spreadsheet. From the spreadsheet you press F4 which opens a window listing all your registered databases. You can expand the list associated with the database by clicking the + next to the database icon and drag any table or query into the spreadsheet.
You can use a macro to create a spreadsheet from scratch but we'd have to know more about what you want to do to comment more about that.

[Villeroy]

Base is not a database. It is a set of tools to connect Writer and Calc with databases. You can feed your calculation models, serial letters, bibliographies with database data from various sources and you can use the office suite for pretty printouts of raw database data.. So the "normal" flow goes from databases to office documents.
[Tutorial] Using registered datasources in Calc

When you connect a Base document to a spreadsheet, Base treats sections of the spreadsheet document as if they were database tables. This way you can read lists from a spreadsheet into text documents or spreadsheets using Base as a bridge. For the user of serial letter it makes no difference if data come from a text file, a spreadsheet or from an Oracle server. Base makes them equal in this respect. Nevertheless, Base is not Jesus. It can not turn spreadsheets into databases. For editing a spreadsheet you still need a spreadsheet program and the availlable features for such "file based databases" are limited.

[doncell6]

Hello FJCC:

My goal is to track all my investments in a spreadsheet. I want to use a spreadsheet as the final repository for my investment information because my broker has a DDE link which will update the prices in real time. The DDE link works well in Calc. It does not work in Access. I don't know if it will work in Base.

I am told Calc does not support forms. I want to be able to create a Calc workbook . My understanding is a Calc worknbook is similar to an Excel work book in that it contains multiple spreadsheets with a tab for each spreadsheet. I don't want to use Excel as I want to eliminate using Windows operating system. Calc does everything I need; I need a way to enter data quickly and have something create the Calc workbook.

Each spreadsheet in the Calc workbook would contain a specific set of investments. The first tab in the Calc spreadsheet would contain the summary information.

If I were to use Access and Excel, I'd have forms in Access which would ask for date the investment started, entry price, etc. Then I would click on a button and the spreadsheet would be created. My understanding is Base cannot do this.

Why do I need a form when I could enter all the data into a Calc spreadsheet? I have a lot of data to enter. For each line of data in the spreadsheet I have to manually create the DDE call to the broker. It's a pain to do this each time.

Then, as each line is entered into the spreadsheet, I want to summarize the data in the first tab of the workbook. May be this could be done using a macro in Calc. I don't know.

To summarize:
- 1,) somehow create a Calc worksheet
- 2.) Have a form of some kind to enter data into cells in a spreadsheet
- 3.) As soon as the data is entered, create a summary entry in the first tab of the workbook (using a macro? )
- 4.) Also create the DDE call to get real time prices from broker.
- Enter the next investment at step 1 without overwriting the existing cells.

May be I am making this too complicated. Perhaps I could create a "master" workbook, a template of sorts? In there would be macros to create a "production" workbook? I am a total novice with LibreOffice so I am seeking advice on ways to approach this project. Perhaps you have some ideas?

[Villeroy]

You can not track investments in spreadsheets (even if millions of users try to do that). Any professional IT person would always collect data in databases and then use a reporting tool kit for the aggregated output.
There are no forms in spreadsheets because spreadsheet cells are already overloaded with all kinds of features. Every single cell is a form control, data storage and a calculator. Using the validation feature you may simulate combo boxes, list boxes, check boxes without any development effort.
There are plenty of extremely powerful reporting engines for all kinds of databases. Calc and Excel have pivot tables which simulate database reports to some extent. They work totally different than any other spreadsheet feature which indicates clearly that spreadsheets are not made for this. In Calc you can also generate pivot tables from database data.

- 4.) Also create the DDE call to get real time prices from broker.

Pardon?

[MTP]

You can certainly have a form in Calc, it's just a matter of where does the information go. For most applications, you have to tie the form to a database for it to be useful. For yours, if you are looking to save the information from each session a database would be needed. If you are only looking to evaluate current conditions, just a Calc file may do what you want.

From your summary, I think steps 1 and 2 can be solved by the suggestion I made in your other post; did you have any success with that?
Step 3 should be easy with a macro. StarBasic is different from VBA; still, since you have experience with VBA I think you would pick up StarBasic without too much trouble.
I have zero experience with DDE so I'll just skip that step. You said in an earlier post that you have written the DDE calls with VBA, so again, I believe you can learn to translate that into StarBasic.
To not overwrite cells, there are different ways to approach it. One is to go ahead and make a database so your form would go to a table and you would write new lines in the table for each call you wanted to make. If you want to stick with Calc, why not have the macro copy the information in the linked cells to a holding location after each round of form entry? It's straightforward to, for example, make a macro that will check line x to see if it is empty, if not go to line x+1 and check if it's empty, keep going until an empty line is found to write the data. And then when the DDE-creating macro is called, it would do something similar to make sure it reads all the lines and gets all the needed information.

[DACM]

The technique is, however, still in use, particularly for distribution of financial data.

Laughable Web Services

[doncell6]

Hello Villeroy:
>>There are no forms in spreadsheets because spreadsheet cells are already overloaded with all kinds of features. Every single cell is a form control, data storage and a calculator. Using the validation feature you may simulate combo boxes, list boxes, check boxes without any development effort.<<

Good point, thank you.

Hello MTP:
Good ideas, I will try these, thank you for your help.