So, I’ve got a script that opens a calc template file I’m trying to have a message box come up that tells the user to fill it out before clicking “OK”. I want the msgbox to hold up the macro, but not prevent the user from filling out the template worksheet. Unfortunately, it’s tying up all of LO, so the template worksheet can’t be filled out. Is there a way to do it so it doesn’t tie up all of LO?
Perhaps it has something to do with the way the template file is opened. Perhaps “All of LO” is a bad description.
I’m using:
oDoc = stardesktop.LoadComponentFromUrl(sURL, “_blank”, 0, mArgs())
sURL is the template file. This is after setting
mArgs(0).Name = ReadOnly
mArgs(0).Value = True
mArgs(1).Name = MacroExecutionMode
mArgs(1).Value = 4
mArgs(2).Name = AsTemplate
mArgs(2).Value = False
I lifted the code for opening the calc document from the macro from “Useful Macro Information For OpenOffice.org” By Andrew Pitonyak. Maybe if I modify it slightly, it will open in such a way that it is accessible even while the macro is running.
Attach a push button to the document instead of the Msgbox. A push button can be set unprintable so it is visible on edit screen only. A toolbar is another option.
Yeah, that’s how I used to have it set up, but I was trying to get all the macro action into one macro. I guess I’ll have to shift my objectives. I was hoping to make this work…
In my case, the “Submit” macro performs an “update” on my database. In order to do this, in the past, I’ve had a special database user account just for this activity, and the credentials were coded into the macro. If I don’t do that, can I pass the connection to the database used in Base to the “Submit” macro in the calculation worksheet Template?
You can attach database forms to Calc documents.
So, I think that means if I put in a form with a “Submit” button, I can tie the form that contains the button back to the Base file, and it should use the same login credentials as were being used before the template spreadsheet was opened by that Base file. Is that what you’re getting at?
No, you can edit database tables directly through database forms that are attached to a Calc sheet.
Demo:
https://forum.openoffice.org/en/forum/viewtopic.php?t=88516&p=416210#p416210
Thanks! I appreciate your work to have the spreadsheet contribute to the database without the use of macros, but we’re kind of married to the submit button and macro approach. (We are required to have an illustration of our calculations saved, so I have the macro save a copy of the sheet in a calculations directory.) At the very least, I need to have the results of the calculations in the spreadsheet entered into the database without having to have the user manually copy them. Manual entry for us means we are required to have someone else review the manual entry for mistakes. Is there a good way to avoid manual entry ? (Perhaps a cell could be somehow treated as a control…)
The report sheet is not copied from the database. It is linked. Use menu:Data>Refresh (or some push button or 3 lines auto-refresh macro).
Unfortunately, the document crashed with recent versions of LO. This is a ODF 1.3 version which seems to work properly with LO 7.2
PowerFilter.ods (39.6 KB)
It already has a filter form with a refresh button pointing to one line of macro code.
I think you’ve missed what I’m trying to accomplish. Really, I need to have the results that are calculated on the Calc sheet automatically entered into a database table, so there is no possibility of human entry error. If I can put a form with controls on the spreadsheet, that does me no good if the user has to manually copy the cell contents into form controls.
This is exactly what a database can do automatically for a whole row set without dragging around any formula cells. It can easily calculate column wise for any combination of criteria. I would say that a database calculates better than a spreadsheet as far as the calculations are related to record sets. Most of todays spreadsheets are faked database row sets.
You can also do the same thing on a spreadsheet with imported data. Calc will adjust any formula cells adjacent to the import range. A feature you don’t have with a spreadsheet alone.
The database allows a separation of data, calculation and representation which is a good thing to have.
[Tutorial] Using registered datasources in Calc
[Example] Loading CSV into preformatted spreadsheets
Not in our case. We are expected to be able to simply illustrate how the calculations in question were performed, and a copy of the filled-out spreadsheet is ideal for that.
OK, it’s your app which I can’t comprehend. Sove the UI problem with the availlable tools.
Thanks! That’s exactly what I want to do. My problem is that I don’t know enough about the available tools or how best to use them to accomplish what I want.
A toolbar for instance or a button attached to the document. You could also use a modeless dialog instead of the msgbox but that would be rather complicated.