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

Antwort erstellen

BBCode ist eingeschaltet
[img] ist ausgeschaltet
[url] ist eingeschaltet
Smileys sind ausgeschaltet
Die letzten Beiträge des Themas
   

Ansicht erweitern Die letzten Beiträge des Themas: [gelöst]Macro setzt einen Zellenwert "statisch"

Re: Macro setzt einen Zellenwert "statisch"

von No[X] » Mi, 19.09.2007 16:21

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


schönen Mittwoch noch
Gruss
Marcus

Re: Macro setzt einen Zellenwert "statisch"

von Karolus » Mi, 19.09.2007 15:46

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

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

von No[X] » Mi, 19.09.2007 10:29

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

Nach oben