[gelöst]Macro setzt einen Zellenwert "statisch"

Programmierung unter AOO/LO (StarBasic, Python, Java, ...)

Moderator: Moderatoren

Antworten
No[X]
*
Beiträge: 12
Registriert: Mo, 25.06.2007 11:34

[gelöst]Macro setzt einen Zellenwert "statisch"

Beitrag von No[X] »

huhu,

habe ein kleines problemchen :)
ich hab mit dem Macroaufzeichner (ja ich kann es sonst nicht :D ) mir einen Macro gezaubert.. funktioniert soweit auch ganz gut ABER wenn er zum schluss die Summe aus den verschiedenen Ergebnissen ziehen soll, macht er immer die gleiche Formel wie als ich es aufgezeichnet habe...

gibt es eine möglichkeit das "dynamisch" zu machen so das es wie der rest funktioniert?

Besten dank für Ideen Anregungen oder Code Schnipsel...

Gruss
Marcus

P.S. Anbei auch der Macro Code

Code: Alles auswählen

sub daten2
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 = "ToPoint"
args1(0).Value = "$A$19"

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

rem ----------------------------------------------------------------------
dim args2(1) as new com.sun.star.beans.PropertyValue
args2(0).Name = "By"
args2(0).Value = 1
args2(1).Name = "Sel"
args2(1).Value = true

dispatcher.executeDispatch(document, ".uno:GoDownToEndOfData", "", 0, args2())

rem ----------------------------------------------------------------------
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 = true

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

rem ----------------------------------------------------------------------
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 = true

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

rem ----------------------------------------------------------------------
dim args5(1) as new com.sun.star.beans.PropertyValue
args5(0).Name = "By"
args5(0).Value = 1
args5(1).Name = "Sel"
args5(1).Value = true

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

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

rem ----------------------------------------------------------------------
dim args7(0) as new com.sun.star.beans.PropertyValue
args7(0).Name = "Nr"
args7(0).Value = 2

dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, args7())

rem ----------------------------------------------------------------------
dim args8(0) as new com.sun.star.beans.PropertyValue
args8(0).Name = "ToPoint"
args8(0).Value = "$A$11"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args8())

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

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

rem ----------------------------------------------------------------------
dim args10(0) as new com.sun.star.beans.PropertyValue
args10(0).Name = "ToPoint"
args10(0).Value = "$D$11"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args10())

rem ----------------------------------------------------------------------
dim args11(1) as new com.sun.star.beans.PropertyValue
args11(0).Name = "By"
args11(0).Value = 1
args11(1).Name = "Sel"
args11(1).Value = true

dispatcher.executeDispatch(document, ".uno:GoDownToEndOfData", "", 0, args11())

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

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

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

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

rem ----------------------------------------------------------------------
dim args14(0) as new com.sun.star.beans.PropertyValue
args14(0).Name = "StringName"
args14(0).Value = "=SUMME(D11:D25)"

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


end sub
Zuletzt geändert von No[X] am Mi, 19.09.2007 17:06, insgesamt 1-mal geändert.
Nach oben
Karolus
********
Beiträge: 7517
Registriert: Mo, 02.01.2006 19:48

Re: Macro setzt einen Zellenwert "statisch"

Beitrag von Karolus »

Hallo Markus

Ich versuch mal in deiner Aufzeichnung etwas aufzuräumen:

Code: Alles auswählen

 sub datenzwei_ein_halb
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 = "ToPoint"
args1(0).Value = "$A$19"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())

rem ----------------------------------------------------------------------
dim args2(1) as new com.sun.star.beans.PropertyValue
args2(0).Name = "By"
args2(0).Value = 1
args2(1).Name = "Sel"
args2(1).Value = true
dispatcher.executeDispatch(document, ".uno:GoDownToEndOfData", "", 0, args2())

rem ----------------------------------------------------------------------
dim args3(1) as new com.sun.star.beans.PropertyValue
args3(0).Name = "By"
args3(0).Value = 3     ' !Wert erhöhen, statt mehrmals aufrufen !
args3(1).Name = "Sel"
args3(1).Value = true
dispatcher.executeDispatch(document, ".uno:GoRight", "", 0, args3())

rem --------------------------
'args 4+5 rausgenommen
rem --------------------------

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

rem ------
'Sprung zum Tabellenblatt in args8 eingetragen
rem -----

dim args8(0) as new com.sun.star.beans.PropertyValue
args8(0).Name = "ToPoint"
args8(0).Value = "$Tabelle2.$A$11"   '! Richtigen Namen des 2. Tabellenblatts eintragen!
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args8())

rem ----------------------------------------------------------------------
dim args9(5) as new com.sun.star.beans.PropertyValue
args9(0).Name = "Flags"
args9(0).Value = "SDNT"
args9(1).Name = "FormulaCommand"
args9(1).Value = 0
args9(2).Name = "SkipEmptyCells"
args9(2).Value = false
args9(3).Name = "Transpose"
args9(3).Value = false
args9(4).Name = "AsLink"
args9(4).Value = false
args9(5).Name = "MoveMode"
args9(5).Value = 4
dispatcher.executeDispatch(document, ".uno:InsertContents", "", 0, args9())

rem ----------------------------------------------------------------------
dim args10(0) as new com.sun.star.beans.PropertyValue
args10(0).Name = "ToPoint"
args10(0).Value = "$D$11"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args10())

rem ----------------------------------------------------------------------
dim args11(1) as new com.sun.star.beans.PropertyValue
args11(0).Name = "By"
args11(0).Value = 1
args11(1).Name = "Sel"
args11(1).Value = true
dispatcher.executeDispatch(document, ".uno:GoDownToEndOfData", "", 0, args11())

rem ----------------------------------------------------------------------
dim args12(1) as new com.sun.star.beans.PropertyValue
args12(0).Name = "By"
args12(0).Value = 2
args12(1).Name = "Sel"
args12(1).Value = false
dispatcher.executeDispatch(document, ".uno:GoDown", "", 0, args12())

rem ----------------------------------------------------------------------
dim args14(0) as new com.sun.star.beans.PropertyValue
args14(0).Name = "StringName"
args14(0).Value = "=SUMME(INDIREKT(""D11:D""&ZEILE()-1))"
dispatcher.executeDispatch(document, ".uno:EnterString", "", 0, args14())
end sub
!! Falls dein 2.Tabellenblatt nicht mehr mit: "Tabelle2" benamt ist musst du Args8 anpassen !

Gruß Karo
LO7.4.7.2 debian 12(bookworm) auf Raspberry4b 8GB (64bit)
LO24.8.2.1 flatpak debian 12(bookworm) auf Raspberry4b 8GB (64bit)
Nach oben
No[X]
*
Beiträge: 12
Registriert: Mo, 25.06.2007 11:34

Re: Macro setzt einen Zellenwert "statisch"

Beitrag von No[X] »

dannkköö *knuff*
bist ein schatz *duck* ^^


schönen Mittwoch noch
Gruss
Marcus
Nach oben
Antworten

Zurück zu „Makros und allgemeine Programmierung“