[Solved] Plead for solution - Macros

[jgriffs@comcast.net]

I understand anyone on this forum is way ahead of me but - In a former life I was a Lotus freak (not really, but pretty good stuff).
But at 71, I don't want to relearn basic or whatever. Would someone take me under their wing, be patient and let me ask the most basic
and, for you, really stupid questions? Like: In a sheet with the cursor at location where ever, go to cell c47, insert a blank row, put "new row"
in that cell.
Please! You could have a lot of fun laughing at some of my stuff.
Please!

Edit: I removed the email address. Responses will be posted here and showing an email address is an invitation for spam. FJCC

[FJCC]

Does this do what you want? I am not sure where you want the text inserted.

Code: Select all

oSheet = ThisComponent.CurrentController.ActiveSheet
oC47 = oSheet.getCellrangeByName("C47")
oSheet.insertCells(oC47.RangeAddress, com.sun.star.sheet.CellInsertMode.DOWN)
oC47 = oSheet.getCellrangeByName("C47") 'oC47 defined above now points at C48
oC47.String = "new row"

Note that it does not matter where the cursor is. OpenOffice macros mostly do not refer to the cursor position. You just refer to the objects you want to modify.
The above macro affects the currently active sheet.

[jgriffs@comcast.net]

Thank you so much for the reply. My example was only that. This being Super Bowl Saturday, I'll study your reply in more detail. However, in my 123 days, the following would work:
{goto} c47~
/WIR~
"NEW ROW"~
The "~" is a return.
The "/" says that the following is a menu selection "worksheet - insert - row"
Finally, the string is entered into that cell.
If placed in a 123 sheet, it assumes one is instructing manipulations on that sheet.
Another task would be to go to the bottom of entries in that column. "{end}{down}~

I am not asking for anything that remotely does that example.
If you choose to help me, a line of appropriate code, with an explanation of what that line accomplishes is what I'm after.

To me 123 was is just so clean and unambiguous. But it does rely on the current sheet containing the macro.
The true 123 geeks would be able to specify another sheet, even by the file name alone without opening that sheet.
I never got that far.
Another example: When my company did inventories, I had a program referencing a list of all known part numbers.
Data entry consisted of taking inventory tags, entering the part number on the tag so a check
could be made as to if it was a valid part number. If it was, the quantity would be accepted - and ready for the next tag.
If not valid, a message to the entry person, "Have supervisor check the part number"
If valid but a previous qty was entered, it would find that other entry find the qty entered and add the new qty.

I had a lot of fun developing that whole thing and it worked. Reducing data entry down like crazy.

I have no interest in developing such a program now but do download some text stuff into OpenOffice. If the result is one string per cell,
I'd like to sort on a field and create a second column. Or to take a string and have a separate column consisting
of the first 6 characters - another of the last 10 characters.

You might be able to discern what you may be getting into helping this newbee. I implore you to stay with me on my quest to
learn the nuances of OpenOffice spread sheets.

[jgriffs@comcast.net]

By the way, I'm royally pee-ode at IBM for giving up.

[FJCC]

A good place to learn about OpenOffice macros is http://www.pitonyak.org/oo.php

Be careful not to over use macros. There are often easier and faster solutions with formulas to problems people try to solve with macros.

[MrProgrammer]

I don't want to relearn basic or whatever. [How would I} In a sheet with the cursor at location where ever, go to cell c47, insert a blank row, put "new row"
in that cell?

If you don't want to learn Basic or the other programming languages that OpenOffice supports you can't write any macros. Simple as that. And the real obstacle to writing macros is that one must learn the complex UNO interface. Expect to take a week or more to write your first macro, more if you have to (re)learn a language too.

However you can record macros without learning the UNO interface or any language. Read [Tutorial] Favorite Recorded Calc Macros. Your example can probably be done with a recorded macro. You will need to learn how to use Calc first. For your example you need to know how, using standard Calc features, one goes to a cell, inserts a new row, puts text in a cell, etc. Learn about those by reading the user guides. This will be much faster than asking here, one at a time, How do I go to a cell?, How do I insert a row?, ad nauseam.

I'd like to sort on a field and create a second column.

Record a macro. Recorded macros do have limitations, but you should be able to accomplish that. Anticipated question: "What are the limitations?" Read the user guides. Experiment. An important limitation is that recordings can't make decisions; all actions are imperative. Without decisions, one cannot have any loops. But one can sometimes circumvent these limitations by creative handling of the standard Calc user interface. I can't teach anyone that.

Or to take a string and have a separate column consisting of the first 6 characters - another of the last 10 characters.

You were previously directed to [Tutorial] Text to Columns which can do that. Maybe you can use it in a recorded macro, maybe not. Try it and see. Otherwise you can probably record performing those actions with formulas. Read the user guides to learn how to do that. Try it and see.

When my company did inventories, I had a program referencing a list of all known part numbers. Data entry consisted of … and add the new qty.

You would do the entry and verification with a Base form and a database, not in Calc.

Another task would be to go to the bottom of entries in that column.

Study the user guides to learn how to do that with the user interface (a keyboard shortcut).

If you choose to help me, a line of appropriate code, with an explanation of what that line accomplishes is what I'm after.

Study Code Snippets.

I implore you to stay with me on my quest to learn the nuances of OpenOffice spread sheets.

You must master the basics first. People waste weeks of time learning how to program with macros for a task which is easily accomplished with the user interface that they haven't bothered to learn.
[Tutorial] Ten concepts that every Calc user should know

[jgriffs@comcast.net]

Thanks for the reply. And let me say that you have sufficiently shoved me back in the corner with a dunce cap on knowing that the new and improved is simply new and more complex.
The user friendly 123 construct of macros is seen as so irrelevant by you all. I refuse to be drawn, kicking and screaming into the inacous way "such a simple way of spreadsheet manipulation"
has been overwritten by .....
I'll take a look at your suggested reference sites. While I am a dog lover and trainer and have disproved the concept of not being able to teach an old dog new tricks, I am that old dog.
My sincere thanks for your information.
John

[Villeroy]

If you think, spreadsheets should be used by means of macros, you better get a copy of MS Excel. Excel's macro language VBA is by far more intuitive because it resembles the graphical user interface. It comes with a working macro recorder and a modern IDE including code completion.

[jgriffs@comcast.net]

I "better get"? Wow. Been there done that...
Open a blank sheet. in column 1 and down just enter silly stuff for 4 or 5 rows. Put the cursor in C12. Start the recorder. Move the cursor over to A3. Insert a blank row.
Now, in cell A3 type "This is a new row" and enter. Stop recording. Now, play what was recorded. Does it work? If so, please post the macro to me. In 123, the macro would
be something like: {goto A3} /wir~ "This is a new row"~ It's been awhile so the syntax may be wrong but show me the VBA macro that does the same thing in a "far more intuitive"
script.

[MrProgrammer]

Open a blank sheet. in column [A] and down just enter silly stuff for 4 or 5 rows. Put the cursor in C12. Start the recorder. Move the cursor over to A3. Insert a blank row.
Now, in cell A3 type "This is a new row" and enter. Stop recording. Now, play what was recorded. Does it work? If so, please post the macro to me.

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

rem ••• Type A3:AMJ3 in Name Box and press Enter
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "ToPoint"
args1(0).Value = "A3:AMJ3"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())

rem ••• Use Insert » Rows
dispatcher.executeDispatch(document, ".uno:InsertRows", "", 0, Array())

rem ••• Press F2
dispatcher.executeDispatch(document, ".uno:SetInputMode", "", 0, Array())

rem ••• Type "This is a new row" and press Enter
dim args4(0) as new com.sun.star.beans.PropertyValue
args4(0).Name = "StringName"
args4(0).Value = "This is a new row"
dispatcher.executeDispatch(document, ".uno:EnterString", "", 0, args4())

End Sub

I just used the macro recorder in OpenOffice and didn't have to write anything myself. Of course it works. The ••• comments show the actions I made in the user interface. Those actions were the only thing I needed to know to create the macro.

The user friendly 123 construct of macros is seen as so irrelevant by you all.

Yes, this is an OpenOffice forum. And isn't it even more friendly when one can accomplish the task without needing to know anything about macro programming?

[jgriffs@comcast.net]

Oh yeah, THAT'S REALLY SIMPLE!. I am properly chastised. I remain unconvinced. Now how many key strokes to enter all that from scratch? Even knowing exactly
what you need to type to have the sheet do what you want? What is "args4(0)? "; "com.sun.star.beans.PropertyValue"???
I can explain "/WIR~ as menu (/) worksheet (W); insert (I); row(R); and "do it" (~).
I know that "sun" is a "star" but what does that have to do with "beans" and the value of my property?

[Zizi64]

Oh yeah, THAT'S REALLY SIMPLE!. I am properly chastised. I remain unconvinced. Now how many key strokes to enter all that from scratch? Even knowing exactly
what you need to type to have the sheet do what you want? What is "args4(0)? "; "com.sun.star.beans.PropertyValue"???
I can explain "/WIR~ as menu (/) worksheet (W); insert (I); row(R); and "do it" (~).
I know that "sun" is a "star" but what does that have to do with "beans" and the value of my property?

A recorded macro code never will be
- short
- simple
- efficient
But you not need know and type-in any commands.

When you write a macro code based on the API functions in the StarBasic IDE, then the macro code will be
- shorter
- simpler
- more efficient and it will run faster.

[RoryOF]

There is, as far as I know, nothing currently available that will suit your wish without modification.

Many macros are available for Calc - directed searching on Internet may locate these for you. You have been pointed to Andrew Pitonyak's works on Calc macro programming, which are complete, in as much as any such work can ever be complete, but daunting.

You may find, in conjunction with any pre-written macros you find, that the OpenOffice BASIC manual at

https://wiki.openoffice.org/wiki/Docume ... ASIC_Guide

Will give you sufficient to adjust any such macros to your desired ends, but you will have to do some work.

[Villeroy]

I "better get"? Wow. Been there done that...
Open a blank sheet. in column 1 and down just enter silly stuff for 4 or 5 rows. Put the cursor in C12. Start the recorder. Move the cursor over to A3. Insert a blank row.
Now, in cell A3 type "This is a new row" and enter. Stop recording. Now, play what was recorded. Does it work? If so, please post the macro to me. In 123, the macro would
be something like: {goto A3} /wir~ "This is a new row"~ It's been awhile so the syntax may be wrong but show me the VBA macro that does the same thing in a "far more intuitive"
script.

Microsoft killed Lotus 1-2-3 25 years ago. There is no resurrection nor replacement. It is gone.

[MrProgrammer]

Oh yeah, THAT'S REALLY SIMPLE!

Thank you.

Now how many key strokes to enter all that from scratch?

Twelve, plus the number of characters you want in the new cell. They are the ones in the ••• lines. You only need to enter them once to record the macro.

What is "args4(0)? "; "com.sun.star.beans.PropertyValue"???

Who cares? You don't need to understand the macro. You don't type this; the macro recorder does. It knows how to create the macro. Calc then knows how to execute the macro. You don't ever need to look at the macro. You just record it and use it/ I only showed it to you because you specifically asked to see it.

This third question tells me that you haven't even tried to use the macro recorder. I am now done helping you, because you haven't tried my suggestion and mostly because I believe you will not be satisfied with any futher assistance I could offer. Bye.

Even knowing exactly what you need to type to have the sheet do what you want?

Not knowing how to use Calc is a separate problem which has nothing to do with programming macros. You learn about this by studying the user guides. If you need help, you open a new topic for a new problem.

A recorded macro code never will be
- short
- simple
- efficient

These considerations are not important. A macro's length, complexity, and efficiency are not relevant using modern hardware with gigahertz clock rates. What is important is that one can save dozens, even hundreds, of hours by avoiding having to master the complex UNO interface. The typical Calc user will not want to invest that much time, even though it is fun to learn. I am an experienced programmer but I find that recorded macros serve my needs quite well. Of course I understand there are tasks which I cannot perform with a recorded macro. This has not been a problem for me. Perhaps one day I will want to devote a week or so to learning more about the UNO interface for a good project. But not today.

Microsoft killed Lotus 1-2-3 25 years ago. There is no resurrection nor replacement. It is gone.

It seems to me as if the OP would like to resurrect it. This could be far more interesting than learning how to use Calc. Jonathan Sachs, the original programmer, used assembly language. The task would be easier now with big, fast machines where one can use a modern language like Python.

[Zizi64]

A macro's length, complexity, and efficiency are not relevant using modern hardware with gigahertz clock rates.

With the Basic Interpreter, and when you want to manipulate many millions of cells (maybe) they are relevant -
in my opinion.

[Zizi64]

Here is some samples for the recorded macros:

viewtopic.php?f=75&t=100724

One of them is named as "TimeStamp":


TimeStamp

Puts the current date/time in the selected cell

The recorded activity:
Insert → Function → Category:Date&time
→ Function:NOW → Next → OK → Edit → Copy
→ Edit → Paste Special → Selection:Date&Time
→ Options:None → Operations:None
→ ShiftCells:Don't → OK

The recorded code:

Code: Select all

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

rem ----------------------------------------------------------------------
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "StringName"
args1(0).Value = "=NOW()"

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

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())

rem ----------------------------------------------------------------------
dim args3(5) as new com.sun.star.beans.PropertyValue
args3(0).Name = "Flags"
args3(0).Value = "D"
args3(1).Name = "FormulaCommand"
args3(1).Value = 0
args3(2).Name = "SkipEmptyCells"
args3(2).Value = false
args3(3).Name = "Transpose"
args3(3).Value = false
args3(4).Name = "AsLink"
args3(4).Value = false
args3(5).Name = "MoveMode"
args3(5).Value = 4

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

end sub

*******************************************************************************************************************


And here is the written code based on the API functions - with same functionality:

Code: Select all

Sub TimeStampAPI
 Dim oCell as object 
	oCell = ThisComponent.getCurrentSelection()
	oCell.Value = Now
	oCell.CellStyle = "MyTimeStamp" 'the cell style named "MyTimeStamp" must be exist
end sub