Page 1 of 1

Problems with macro recording.

Posted: Sun Jan 18, 2015 11:01 pm
by LGriffin
I created a macro to edit a cell with a function and convert it to text by putting a space at the beginning of the cell.
When recording a macro, more than key strokes are being recorded. That is I start the recording, click at beginning of cell(selecting it), spacebar( changing contents from function to text), enter, stop recording. Create the macro, then run the macro on the next cell. The next cell I run the macro on the contents are over writen. The contents of the cell from the recording is part of the macro and over writes the data. is that the way it is suppose to work.

Re: Problems with macro recording.

Posted: Sun Jan 18, 2015 11:29 pm
by Zizi64
Can you upload your recorded macro code here?
And what you want to do really with that macro?

Re: Problems with macro recording.

Posted: Mon Jan 19, 2015 12:31 am
by LGriffin
I'm not sure how to upload here, so cut & paste;
###

Code: Select all

REM  *****  BASIC  *****

Sub Main

End Sub


sub insqute
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 = CHR$(34)+"=com.smf.ticker.getinfo.python.smfimpl.getyahoo(A1,3)"+CHR$(34)

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


end sub


sub addq
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 = CHR$(34)+"=com.smf.ticker.getinfo.python.smfimpl.getyahoo(A4,4)"+CHR$(34)

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


end sub


sub sp
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 = " =GETYAHOO(A16,4)"

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


end sub
###

The SP is the one I'm referring to here. The macro is suppose to work on the currently selected cell. The cursor rtedselects the beginning of the cell before the contents, then a space is inserted. then enter. That should change the contents from function (=GETYAHOO(A16,4) to text.

Re: Problems with macro recording.

Posted: Mon Jan 19, 2015 12:32 am
by Zizi64
((Use the "Code" button for the code snippets in this forum))

Re: Problems with macro recording.

Posted: Mon Jan 19, 2015 12:57 am
by Zizi64
There are three macro SUBs with same functionality in your attachment. All of them can put a string constant into the active cell, and nothing more.
What you needed really, what you to do with the code? Do you want edit an existing text in a few cell? Do you want insert 'spaces' into an existing text located in some selected cell?

If the answer is "YES", then the macro recorder is not the best choice for it. The macro recorder can not record every activity of the user.

You need study the API functions, and apply them in the BASIC program code.
With the API functions you can get a Document, can get a Sheet, can get a Cellrange, can get a Cell, can get the content of a cell, and you can modify the content and put the result back into the cell.

Re: Problems with macro recording.

Posted: Mon Jan 19, 2015 1:15 am
by LGriffin
Zizi64 wrote:There are three macro SUBs with same functionality in your attachment. All of them can put a string constant into the active cell, and nothing more.

The string is not what I want.

What you needed really, what you to do with the cede? Do you want edit an existing text in a few cell? Do you want insert 'spaces' into an existing text located in a cell?

I've got about 50 cells that I would like to insert a space at the beginning.

If the answer is "YES", then the macro recorder is not the best choice for it. The macro recorder can not record every activity of the user.

Is there something that would record just the key strokes?

That's what I did in Lotus 123. It was simple and got the job done. If I can't get it done simple with LibreOffice Calc, I'll have to bring up a computer with Excel.


You need study the API functions, and apply them in the BASIC program code.
With the API functions you can get a Document, can get a Sheet, can get a Cellrange, can get a Cell, can get the content of a cell, and you can modify the content and put the result back into the cell.
That's more than I want to get into.

Re: Problems with macro recording.

Posted: Mon Jan 19, 2015 8:41 am
by Zizi64
I've got about 50 cells that I would like to insert a space at the beginning.
Is there something that would record just the key strokes?
There is not. The AOO or LO is not Lotus 123 or MSExcel. The macrorecorder of AOO/LO Calc and other Spreadsheets work on different base.


If all of those cell contain pure strings, then you can WRITE (not to RECORD) something similar BASIC macro code:

Code: Select all

Sub AddSpaceToBegin
	oSheet=thiscomponent.getcurrentcontroller.activesheet
	oCell = ThisComponent.getCurrentSelection()
	sContent = oCell.String
	if sContent<>"" then
		oCell.SetString(" "&sContent)
	end if
End Sub
((You MUST select one cell only to modify to run this symple testmacro without error... Yo must edit the macrocode if you want to modify a selected CELLRANGE.))
AddSpace.ods
(25.55 KiB) Downloaded 267 times

But when the cells contains NUMBERS (dates, decimal values, etc) or FORMULAS, then you need to examine the type of the cell content, and in accordance the Type, you can realize the "space" inserting - if it necessary. (Note: My example SUB will convert the numbers or formulas to string content in the adjusted cells.)

In your examples above I see, that your cells contain some formulas. In this case you need "edit" the formula by the macrocode, or you need insert more cell functions (for example the function CONCATENATE()) to achieve task.

Re: Problems with macro recording.

Posted: Mon Jan 19, 2015 7:28 pm
by LGriffin
I did some research and now I'm not sure a spreadsheet is the answer to what I want to do. Progress doesn't always seem to be the answer, at least this case. When I was working with spreadsheets capturing key strokes and saving them as a macros was the hot new feature. Now it appears that writing macros in Basic is what is being done. Was capturing key strokes abandoned because of security reasons, because I don't see writing basic as a replacement.

I DL your ODS, it looks simple enough, but I'm retired, and don't need anything more on my resume. Thanks for your help.