Create macro which moves 1 cell down and pastes

[CannedMan]

The database I work with at work requires hundreds of paste operations every day, and I've begun developing pains in my left hand from doing this. Therefore I'd like to have a macro that moves one cell down and then pastes from the clipboard. I've tried recording a macro that does this, but when I stop the recording, nothing happens – i.e. I am not asked to save my macro; it seems the key presses [down-arrow] then [Ctrl+V] aren't recorded at all. Assistance with this conundrum i greatly appreciated.

[MATT.B]

edit this how you need I have broken down what each section of the code does

Code: Select all

REM  *****  BASIC  *****
sub Main

rem define variables ----------------------------------------------------------------------
dim document   as object
dim dispatcher as object
rem get access to the document ----------------------------------------------------------------------
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem move 1 cell down from selected cell----------------------------------------------------------------------
dim args1(1) as new com.sun.star.beans.PropertyValue
args1(0).Name = "By"
args1(0).Value = 1
args1(1).Name = "Sel"
args1(1).Value = false

dispatcher.executeDispatch(document, ".uno:GoDown", "", 0, args1())

rem move 1 cell right from selected cell----------------------------------------------------------------------
dim args3(1) as new com.sun.star.beans.PropertyValue
args3(0).Name = "By"
args3(0).Value = 1
args3(1).Name = "Sel"
args3(1).Value = false

dispatcher.executeDispatch(document, ".uno:GoRight", "", 0, args3())

rem move 1 cell left from selected cell----------------------------------------------------------------------
dim args4(1) as new com.sun.star.beans.PropertyValue
args4(0).Name = "By"
args4(0).Value = 1
args4(1).Name = "Sel"
args4(1).Value = false

dispatcher.executeDispatch(document, ".uno:GoLeft", "", 0, args4())

rem paste contents of clipboard ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:Paste", "", 0, Array())

rem copy selected cell to clipboard ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())
end sub

[CannedMan]

Thank you. I’ve tried it, but it doesn’t work, unfortunately. Instead it scrolls the main window, and nothing else happens. Why, I do not know. FYI, should it matter, I set it to F4.

[Arineckaig]

I'd like to have a macro that moves one cell down and then pastes from the clipboard

A constructive suggestion might be easier with more information.

For example, what element of Base is to be involved: a form document or direct entry into a source table? Either type of table in Base largely differs from a spreadsheet. Then again how is the macro to be triggered? The paticular DBMS linked to Base may also have relevance, as could be the source of the data to be pasted.

[CannedMan]

If it is of any help, I could post a couple of screenshots; I won’t be able to do this until Monday next week, though, as I only have a 50 % position.

[MATT.B]

what type of doc is this and can you attach a example copy
the macro was written for a calc doc

[CannedMan]

Unfortunately, I am not at liberty to attach a copy. As stated in the first post, it is a database, so that would explain why the macro didn't work. The database contains the 1875 census (Norway), with each group of entries corresponding to the pages in the original document.

[CannedMan]

Here’s a decensitised screenshot of the dbase I’m working in.

[MATT.B]

I'm sorry but i will be no help with databases. good luck on your issue though

[UnklDonald418]

I've been watching this thread with considerable interest, since I have a previous thread which asks essentially the same question.
viewtopic.php?f=13&t=78802
Unfortunately no solution was found on that thread either.
As you mentioned, this feature would be quite useful for data entry, so I'm surprised that no one seems to have already found a solution.
A previous recommendation in this thread involves using the dispatcher. That example is what would result using the Macro Recorder which isn't available in Base so as you noted it doesn't work here. It works fine in Calc. In fact in one situation I did my data entry in Calc so that I could use that macro and then imported the data into a Base table. Not something I would want to do on a regular basis.
Combo Boxes can help for text that repeats, but they aren't always as handy as this feature would be.
It doesn't appear that it should be terribly difficult to create a macro to copy the information from the previous row into the current row of the current column but it does require a higher level of understanding of the structure of Grids on Forms than I can currently claim.

[CannedMan]

I was not aware that there were such differences between how Base and Calc handles the grids, that it would pose such a challenge. Maybe this could be made sticky to attract the attention of potential programmers? Anyone who’s done repetitive data entry knows very well what the hands feel like after a day of lots of copy–paste-operations, so I would expect a potential solution to it to get rather popular.

As a side note, Base does seem to be the part of the OpenOffice/LibreOffice solution receiving the least attention. Not much happened from LO4 to 5, as I understand from my tech guy. It is understandable, though, as the number of people using Writer and Calc by far outweighs the number of people using Base.

[Villeroy]

spreadsheet/database <=> motor bike/airplane (both have combustion engines).
Your screen shot indicates that you designed an airplane with no wings.
The true thing would not require any macros. I run fairly sophisticated Base applications with no more than 20 lines of code (if any).

[CannedMan]

spreadsheet/database <=> motor bike/airplane (both have combustion engines).

I like your comparison; it explains the difference well.

Your screen shot indicates that you designed an airplane with no wings.

I'm sorry Villeroy, but I don't understand what you mean. Be advised, the database is restricted by the task at hand: What we are doing, is entering the data of the 1875 census, so all fields are dictated by how the forms of the 1875 census were designed; we cannot concatenate fields, for instance, though we have added quite a few fields to get cleaner data (such as standardised names, so that both the original name is preserved and a strict, easily searchable version of the name is available; birth place area codes; occupation number codes; et c.).

The true thing would not require any macros. I run fairly sophisticated Base applications with no more than 20 lines of code (if any).

The macros I’m hoping for, are macros that would reduce the number of copy-paste operations. When all members of a household are born the same place, I would prefer only writing that on the first person, and then have a macro assigned to a function key, so I could simply press that single function key to run the macro, i.e. to copy the selected “cell’s” contents, move one “cell” down and paste the information there. I don’t see any way this could be achieved by different design, unless there actually exists software that can read handwriting (scanned from microfilms) well enough to do everything automatically.

[mgroenescheij]

As Arineckaig already suggested some more information will help to find a solution.

For example, what element of Base is to be involved: a form document or direct entry into a source table? Either type of table in Base largely differs from a spreadsheet. Then again how is the macro to be triggered? The paticular DBMS linked to Base may also have relevance, as could be the source of the data to be pasted.

What database do you interface with?
What is the structure of the data you receive e.g. is it a csv file?
The fact that you can copy and past suggest that you have at least the records in electronic format.

[UnklDonald418]

While a well functioning macro would provide a universal solution List/Combo boxes can provide a solution for specific fields. After reading your most recent post

When all members of a household are born the same place, I would prefer only writing that on the first person,

I think you might find a List or Combo Box useful for entering the birthplace data. Of course if there are more than a few possible birthplaces that begin with the same 3 or 4 characters their effectiveness quickly diminishes.

[CannedMan]

The fact that you can copy and past suggest that you have at least the records in electronic format.

All the data has been entered by others; our job is to control their entries (and there are … enough errors) and correct them as needed. So we have all the data available electronically, pre-entered into the dbase, with the source document showing at the bottom (as showed in the provided screen shot). The technical details of the dbase, I can try to provide after Monday.

I think you might find a List or Combo Box useful for entering the birthplace data. Of course if there are more than a few possible birthplaces that begin with the same 3 or 4 characters their effectiveness quickly diminishes.

One would think that there would be a small selection of place names to choose from, but one of the requirements is that we enter the data as recorded in the original source (there are a few exceptions, such as not entering abbreviation symbols (back then both a full stop and a colon could work as an abbreviation symbol) for common short forms, such as e.g. “P.”/“Prgj.”/“Pgjd.” for “Præstegjæld” (parish), which electronically will be recorded as simply “S”/“Prgj”/“Pgjd”. Place names could, however, be written in a multitude of ways, and we are to always register them as in the source. Take Glemmen, back then called Glemminge: I’ve found it recorded as Gleminge, Glemminge, Glemingen, Glemmingen, Glemige, or also as abbreviations, e.g. Gl., Glmg., and so on. So having drop down menus clearly would not solve the problem. As said, we record this as per the source, and then add a unique number in a second grid cell, identifying the actual place (a sort of zip code). The registrators were luckily fond of using a ditto marker (usually “Dº”), which in effect was their version of doing a copy-paste operation. And when they dittoed something, we do a copy-paste, and this is what's killing my hand.

Ergonomically, it is preferable to work with a keyboard; working with a mouse has several disadvantages, as anyone working in a computer environment can vouch for. Therefore, any tedious tasks that can be repeated by creating a macro for it and assigning it to preferably single key strokes, is a win.

[mgroenescheij]

Why are you entering data in a new record as it's much simpler to have the Place name twice in your database 1st Place name Entered 2nd Place Name Corrected.
To automate data cleanup you or your database administrator could create some lookup tables e.g. a table for Place names which contains the entered Place name and the standardized Place name.
This way you could clean your data for about 80% with simple SQL scripts.
What you can do for Place names is also possible for other common differences like the abbreviations etc.

But be aware if you automate to much your without a job.

[CannedMan]

I was probably not specific enough: We have two entries for place names: One for the actual source data, and one with the numerical code matching that place. In one household one could get the following:
First name • Surname • Marital status • Family status • Occupation • Age • Born • Curr. res., status • Curr. res. at • Birth place • Birth Place Code • Building
Jane • Doe • w • Mother • Manager of the house and farm • 45 • 1830 • [null] • [null] • Glemming • 0132 • [null]
Jane • Johndaughter • um • Daughter • Helps her mother at home • 13 • 1862 • aw • Christiania • Fr.stad • 0132 • [null]
John • Johnson • um • Son • Helps with the farm • 12 • 1861 • [null] • [null] • Gleminge • 0132 • [null]
Richard • Richardson • m • Servant • Dº • 48 • 1827 • aw • Glemige • Dº • • Servants’ house

Also, all data that can be automated is (as far as I’ve been able to learn) automated already, and a lot of work is saved by designing queries to find information we are expecting to have been incorrectly entered into the database before it was handed over to us.

[Villeroy]

You need a database developer. Development is something where "learning by doing" fails because you have no concept of what you could do from a developers point of view. You can not approproach something which you don't know.
A perfectly designed database does not allow any Null values.
In a perfectly designed database every string value (name, article ID, account number) occurs only once.
Perfectly designed databases are rare but in theory it is possible to design a perfect net of relations with no duplicate information and no gaps.

[CannedMan]

Again, I reiterate, the purpose of the database is to eletronically make available the censuses for use by historians and others with demographic interests. As historians, a source is always to be cited as is; this means that if the original source has no information on, say, a person’s habitual residence, so should the database entry representing this field be empty. However, for fields that require an entry to be made, such as birth place, we enter the dummy value of “9999” if a person’s birth place is unknown. The same goes for fields such as name, gender, and birth year; if these are empty, we enter “!!” to indicate that the field is intentionally left blank.

In case there is confusion: I am not the designer of the database – for that we have an engineer whose main task is just that – I am just working with it. The database in it self is a national endeavour with plenty of resource allocated for it, so I would find it rather strange should it be found to be amateurishly designed. What I am hoping for by asking the community for help, is to find some ways of reducing the stress the entering of repetitive data entries – that by their very nature are impossible to automate at a reasonable cost with today’s hand writing recognition technology – cause on my hands.

[Sliderule]

Please, forgive me for asking a few questions, because, I think this may ( depending on some of your answers ) really help you to POPULATE ( add data ) the database.

1. What is your database back-end . . . for example . . . HSQL or MySQL or PostGRE or H2 or Microsoft Access etc ?
2. If your are using HSQL, is it as an Embedded Database, or, a Split Database ( where the database is stored outside of the *.odb file )?
3. Am I correct, you have a TEXT file, one line per row you want in the database table, using some character, for example a comma, or a semi-colon, or a tab character etc between each value you want entered into the database table?

I am asking all of this, because, perhaps, you ( or your users ) do NOT need to enter the data, but rather, it can all be done, automatically ( AKA - Also Known As - automagically ) by using a tool like, sqltool.jar , and, setting some DEFAULT VALUES for the data, as you have described above.

Sliderule

[CannedMan]

Please, forgive me for asking a few questions, because, I think this may ( depending on some of your answers ) really help you to POPULATE ( add data ) the database.

You seem to have caught on to my frustration. Thank you.

1. What is your database back-end . . . for example . . . HSQL or MySQL or PostGRE or H2 or Microsoft Access etc ?
2. If your are using HSQL, is it as an Embedded Database, or, a Split Database ( where the database is stored outside of the *.odb file )?
3. Am I correct, you have a TEXT file, one line per row you want in the database table, using some character, for example a comma, or a semi-colon, or a tab character etc between each value you want entered into the database table?

1. I asked the tech guy, and he said it’s Oracle.
2. I would presume it is a split database, as I ATM get an I/O error when trying to launch it (the power just came back, and our network hasn’t finished relaunching). The odb-file is stored locally, and the data on a local network drive.
3. If you by this are asking where the source data is from, it is scanned, handwritten documents from 1875. These have been transcribed by others into the dbase, and we are controlling their data by looking at the same sources as they do. I have added a screenshot of my screen setup, which hopefully explains this better than me.

Were these answers of any help?

[Villeroy]

Now we gathered the most important information at last. Rebuilding the database might not be an option and writing a macro is difficult.
When you need to copy some value top-down a field, does that process stop at a certain row or does it go down until the last record? In other words: is there some way to filter the form so all records would be affected by the same change? I'm thinking of an SQL UPDATE statement in a macro.

[CannedMan]

There are instances in which we do this, but rarely for me; I’ve only had this when entering households from scratch, allowing him to run a script to copy the four first rows and the image url to all entries below. Normally, I will have to stop at a certain row, yes.

[Villeroy]

The attached file with an embedded HSQL is just another example how difficult it is to program anything with Base.
I am not able to read clipboard content nor paste into form controls, therefore I introduced a field selector, a text box and a repeating push button (keep pressed to repeat its action). You paste your text into the text box, select the field where to write and press the button.

This works well with 0 and 1 for the boolean field, not at all for the date-time field, partially for the decimal field and the text field behaves ... crazy. And of course, the whole thing has a tendency to crash.

If you work with an Oracle DB and there is a system admin for that database, I would strongly recommend to abstain from Libre/OpenOffice forms. Any professional front-end for Oracle might be far more productive (may I recomment MS Access?). Nevertheless you can read data from the DB to your office documents but input forms with home grown extra functionality is not an option (in my honest opinion). I guess there are no more 100 people on planet earth able to develop the required functionality. I am not among those experts. Possibly the Access2Base extension could help in this particular case but I can't figure out how. I spent 2 hours with A2B and another 2 hours with my own macro.

[CannedMan]

If you work with an Oracle DB and there is a system admin for that database, I would strongly recommend to abstain from Libre/OpenOffice forms. Any professional front-end for Oracle might be far more productive (may I recomment MS Access?).

We do work with Oracle. The reason we have opted for LibreOffice Base in favour of MS Access, is because there is some strange screen width limitation in Access; the records we work with are easiest to work with when using super-wide screens. After having had a long, educational conversation with Sliderule, it would appear my request at the time being is insolvable, however desired. Might this be something that could be called an issue?

[UnklDonald418]

I've posted a Macro under a question I posed that was asking for something similar to what you are looking for here. It may provide a partial solution for you.

Copy data from previous row 
viewtopic.php?f=13&t=78802

Eventually I am hoping for a more robust solution, but this Macro is a first step. When this Macro is assigned to a key from Tools->Customize->Keyboard it will copy the contents of the column in the previous row into the current row and column. It only works on text fields, and only on a single column whose name you must enter into the Macro. Actually, I set it up so that you could use it on more than one column but that would require a different key for each column. There are several other conditions that I detail in my posting. If you try it be sure to use it on a test table first, since it hasn't gone through a great deal of testing.

[Villeroy]

OK, another evening with new energy.
Download the demo (embedded HSQLDB) to a trusted directory. It contains a Basic macro to install a Python macro.
Open form "PythonCode" and push the install button which saves the form as Python source code in your user profile.
Open form "Table1", focus any value in the grid, push and hold the right button which calls the installed Python macro. The Basic macro is no longer needed.
The installed macro copies down the value of any focussed form control. This requires that the triggering button does not steal the focus from the value to be copied (button property "Take focus on click"=No). When you click (and focus) the left dummy button and then the fill down button, you get an error message with "focussed control is not bound ...".
The macro works for all simple data types bound to any form control. I disabled the macro for binary types, arrays and field types I don't know.

Simply add a push button to your own form or subform, bind the execute event to the installed Python macro and disable "focus on click" for that button. You may want to set the repeat and delay properties to repeat the action when you keep the button pushed. The delay should be higher than the amount of time needed for the macro action.

[CannedMan]

I am sorry that I didn’t reply to this back then. I ended up being put on 100 % sick leave (still not fully well from it), then gradually had a crash and burn. I cannot remember whether I was able to use your work, but in any case: Thank you everyone for your kind help. I no longer work there (in fact, we no longer live in the same country). But this community is always there for people, and for that I am grateful.