Running Macros in Calc

[barryc]

I recorded a macro, and it performed the desired result while recording. However, if I run it again, it does nothing. This is after installing Java JRE, as prompted.

Do macros simply not work, or am I doing something wrong?

To clarify: I started with the assumption that I did not need to write or understand any language for this task - I expected it to record my keystrokes and mouse clicks, as Excel does. However, in troubleshooting, I looked at the program (it got written in OO Basic, as it turns out). It appears that there is not enough information in the recorded macro to represent all the keystrokes I made (unless it stored this elsewhere?).

Here are the macros:
REM ***** BASIC *****

Sub Main

End Sub


sub Analysis
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 ----------------------------------------------------------------------
rem dispatcher.executeDispatch(document, ".uno:DataFilterSpecialFilter", "", 0, Array())

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

rem ----------------------------------------------------------------------
dim args3(0) as new com.sun.star.beans.PropertyValue
args3(0).Name = "aExtraHeight"
args3(0).Value = 0

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


end sub


sub Anal1
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 ----------------------------------------------------------------------
rem dispatcher.executeDispatch(document, ".uno:DataFilterSpecialFilter", "", 0, Array())

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


end sub


sub HWTest
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 ----------------------------------------------------------------------
rem dispatcher.executeDispatch(document, ".uno:DataFilterSpecialFilter", "", 0, Array())

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

rem ----------------------------------------------------------------------
dim args3(0) as new com.sun.star.beans.PropertyValue
args3(0).Name = "aExtraHeight"
args3(0).Value = 0

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


end sub



sub Anal2
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 ----------------------------------------------------------------------
rem dispatcher.executeDispatch(document, ".uno:DataFilterSpecialFilter", "", 0, Array())

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

rem ----------------------------------------------------------------------
dim args3(0) as new com.sun.star.beans.PropertyValue
args3(0).Name = "aExtraHeight"
args3(0).Value = 0

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

rem ----------------------------------------------------------------------
dim args4(0) as new com.sun.star.beans.PropertyValue
args4(0).Name = "Sel"
args4(0).Value = false

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


end sub

It has placed all my experimental macros in this one module.
An example in the above is Anal2, a macro to do the following:
1. Go to sheet 'All'
2. Do Data – Filter – Advanced Filter -
3. In popup window, select the desired filter criterion in left box (I selected "AnalysisCriteria").
4. Click “More”
5. Select in bottom left checkbox a range to copy the results to (I selected "Analysis")
6. This brings me to the destination sheet (called "Analysis"). Do Format – Row – Optimal Height to clean up the appearance.
7. Stop recording.

I see some of these actions (sort of) in the recorded macro, but not all, so I wonder where the rest of the macro "instructions" are.

Also, since the macro is recorded in Basic, why did it ask for JRE? I se reference to "beans", so does it need JRE even to run Basic?

I see this is the same issue as in topic 34575.
Bottom line problem: "The macro recorder records some things and not others".

Is there a solution for the non-programmer user?

[noranthon]

Hi, barryc. You have come up against a deficiency of the macro recorder. There is no solution for a non-programmer and the only one I've seen is no trivial matter.

It wasn't the macro recorder that required the JRE. It must be the tools you used which had that requirement.
_________________
search forum by month

[Villeroy]

I can provide a macro. Adjust the test-routine to your needs.