Makro auf bestimmte Tabellenblätter anwenden

Das Tabellenkalkulationsprogramm

Moderator: Moderatoren

MichaelSP
Beiträge: 7
Registriert: Mi, 06.01.2010 15:47

Makro auf bestimmte Tabellenblätter anwenden

Beitrag von MichaelSP »

Hallo Forum,

ich möchte ein Script auf bestimmte Tabellenblätter (Sheets) anwenden. Momentan habe ich es für jede Tabelle einzeln z.B. so gelöst:

Code: Alles auswählen


Sub Tabelle2
Dim Doc As Object
Dim Sheet As Object
Dim Cell As Object   
 
Doc = ThisComponent
Sheet = Doc.Sheets(1)

Cell = Sheet.getCellByPosition(3, 66)
Cell.Formulalocal = "=SUMMENPRODUKT(((F5:F45=""Begriff""))*(D5:D45))"

End Sub
 
Wie schaffe ich es, dass ich dieses Script auf mehrere Tabellenblätter anwende??

M.
F3K Total
********
Beiträge: 3724
Registriert: Mo, 28.02.2011 17:49

Re: Makro auf bestimmte Tabellenblätter anwenden

Beitrag von F3K Total »

Hallo, das geht recht einfach:

Code: Alles auswählen

Sub fuer_fuenf_Tabellen

Dim Doc As Object
Dim Sheet As Object
Dim Cell As Object 

Dim Sheetname(5) as string
Sheetname(1)="Tabelle1"'hier die Namen der Tabellen eintragen, bei denen die Formel in die Zelle (3,66) eingearbeitet werden soll
Sheetname(2)="Tabelle2"
Sheetname(3)="TabelleUmsatzsteuer"
Sheetname(4)="TabelleMehrwertsteuer"
Sheetname(5)="TabelleVereinsmitglieder"

Doc = ThisComponent
for sheetnumber =1 to 5
Sheet = Doc.Sheets.getbyname(Sheetname(sheetnumber))
Cell = Sheet.getCellByPosition(3, 66)
Cell.Formulalocal = "=SUMMENPRODUKT(((F5:F45=""Begriff""))*(D5:D45))"
next sheetnumber
End Sub
Wenn es sich um alle Tabellen in der Datei handelt geht es noch einfacher:

Code: Alles auswählen

Sub fuer_alle_Tabellen

Dim Doc As Object
Dim Sheet As Object
Dim Cell As Object 

Doc = ThisComponent
for sheetnumber =0 to doc.sheets.count-1
Sheet = Doc.Sheets(sheetnumber)
Cell = Sheet.getCellByPosition(3, 66)
Cell.Formulalocal = "=SUMMENPRODUKT(((F5:F45=""Begriff""))*(D5:D45))"
next sheetnumber
End Sub
Gruß R
MichaelSP
Beiträge: 7
Registriert: Mi, 06.01.2010 15:47

Re: Makro auf bestimmte Tabellenblätter anwenden

Beitrag von MichaelSP »

Danke, das hilft mir sehr!
MichaelSP
Beiträge: 7
Registriert: Mi, 06.01.2010 15:47

Re: Makro auf bestimmte Tabellenblätter anwenden

Beitrag von MichaelSP »

Ich möchte nun eine Auswertung mehrere Tabellen in einem eigenen Tabellenblatt erstellen.

Code: Alles auswählen

Sub Auswertung
Dim Doc As Object
Dim Sheet As Object
Dim Cell As Object   
 
Doc = ThisComponent
Sheet = Doc.Sheets(20) 
 

Cell = Sheet.getCellByPosition(1, 1)
Cell.Formulalocal = "=SUMMENPRODUKT(Tabelle.F5:F45=""Begriff1"";Tabelle.D5:D45)"
Cell = Sheet.getCellByPosition(1, 2)
Cell.Formulalocal = "=SUMMENPRODUKT(Tabelle.F5:F45=""Begriff2"";Tabelle.D5:D45)" 


End Sub
Momentan muss ich dieses Script für alle betreffenden Tabellen neu erstellen. Wie kann ich mehrere Tabellen in einer Schleife auswerten? Es müsste der Tabellenname im Summenprodukt variable sein. Kann mir jemand bei der Syntax helfen oder einen Tipp für ein Beispiel geben??

Danke vorab!!!
M.
Zuletzt geändert von MichaelSP am Mo, 11.04.2011 10:51, insgesamt 1-mal geändert.
F3K Total
********
Beiträge: 3724
Registriert: Mo, 28.02.2011 17:49

Re: Makro auf bestimmte Tabellenblätter anwenden

Beitrag von F3K Total »

ein Tip dazu:

Doc = ThisComponent
Sheet = Doc.Sheets(20)

Doc.sheets(0) ist das erste Tabellenblatt
Doc.sheets(1) ist das zweite Tabellenblatt
...
Doc.sheets(19) ist das zwanzigsteTabellenblatt

Code: Alles auswählen

for i=0 to 19
Doc.sheets(i) 
...
next i
ist die Schleife


Gruß R
F3K Total
********
Beiträge: 3724
Registriert: Mo, 28.02.2011 17:49

Re: Makro auf bestimmte Tabellenblätter anwenden

Beitrag von F3K Total »

kleine Korrektur:

Code: Alles auswählen

Doc=ThisComponent

for i=0 to 19
sheet=Doc.sheets(i)
...
next i
Gruß R
MichaelSP
Beiträge: 7
Registriert: Mi, 06.01.2010 15:47

Re: Makro auf bestimmte Tabellenblätter anwenden

Beitrag von MichaelSP »

Hallo,

ich habe nun Zeit gefunden, das Ganze umzusetzen, stehe aber auf der Stelle.
Ich habe 3 Tabellenblätter mit Werten und ein Auswertungstabellenblatt.
Nun möchte ich alle 3 Wertetabellen durchlaufen und mehrere Zeilen und Spalten auf Begriffe abfragen. In der Auswertungstabelle soll dann jede Wertetabelle eine eigene Spalte bekommen.

Leider funktioniert mein Script nicht. Wer kann mir bitte einen Tipp geben???


m

Code: Alles auswählen

Sub Auswertung

Dim Doc As Object
Dim Sheet As Object
Dim Cell As Object

Dim Sheetname(1) as string
Sheetname(1)="Auswertung"


Doc = ThisComponent
for i=0 to 2
sheet=Doc.sheets(i)


Sheet = Doc.Sheets.getbyname(Sheetname(1))
Cell = Sheetname(1).getCellByPosition(3, 1)
Cell.Formulalocal = "=SUMMENPRODUKT(((sheets(i).B5:B45=""Begriff1""))*(sheets(i).A5:A45))"
Cell = Sheetname(1).getCellByPosition(3, 2)
Cell.Formulalocal = "=SUMMENPRODUKT(((sheets(i).B5:B45=""Begriff2""))*(sheets(i).A5:A45))"
Cell = Sheetname(1).getCellByPosition(3, 3)
Cell.Formulalocal = "=SUMMENPRODUKT(((sheets(i).B5:B45=""Begriff3""))*(sheets(i).A5:A45))"


next i
End Sub
F3K Total
********
Beiträge: 3724
Registriert: Mo, 28.02.2011 17:49

Re: Makro auf bestimmte Tabellenblätter anwenden

Beitrag von F3K Total »

Hallo, schau dir mal diese zwei Zellen an:

Code: Alles auswählen

sheet=Doc.sheets(i)
Sheet = Doc.Sheets.getbyname(Sheetname(1))
In der ersten Zeile sagst Du: nimm das i-te Tabellenblatt als "sheet" und in der Zeile danach weist Du genau der selben Variablen "sheet"
das Tabellenblatt mit dem Namen "Auswertung" zu. Ab hier ist also "sheet" die Tabelle "Auswertung".
Was willst Du den nun?
Gruß R
MichaelSP
Beiträge: 7
Registriert: Mi, 06.01.2010 15:47

Re: Makro auf bestimmte Tabellenblätter anwenden

Beitrag von MichaelSP »

Dein Einwurf ist berechtigt!

Habe es nun so versucht:

Code: Alles auswählen

Sub Auswertung

Dim Doc As Object
Dim Sheeta As Object
Dim Cell As Object

Dim Sheetname(1) as string
Sheetname(1)="Auswertung"



Doc = ThisComponent


for sheetnumber =0 to 2
Sheet = Doc.Sheets.getbyname(Sheetname(1))

Cell = Sheet.getCellByPosition(i, 1)
Cell.Formulalocal = "=SUMMENPRODUKT(((B5:B45=""Begriff1""))*(A5:A45))"
Cell = Sheet.getCellByPosition(i, 2)
Cell.Formulalocal = "=SUMMENPRODUKT(((B5:B45=""Begriff2""))*(A5:A45))"
Cell = Sheet.getCellByPosition(i, 3)
Cell.Formulalocal = "=SUMMENPRODUKT(((B5:B45=""Begriff3""))*(A5:A45))"


next sheetnumber
End Sub

Auch das funktioniert leider nicht wie gewünscht. Ich bin am Ende meiner Nerven....
TEST.ods
Testdatei Auswertungstabelle
(10.21 KiB) 88-mal heruntergeladen
Benutzeravatar
balu
********
Beiträge: 3812
Registriert: Fr, 24.08.2007 00:28
Wohnort: Warstein

Re: Makro auf bestimmte Tabellenblätter anwenden

Beitrag von balu »

Hallo Michael,

Code: Alles auswählen

Cell.Formulalocal = "=SUMMENPRODUKT(((sheets(i).B5:B45=""Begriff1""))*(sheets(i).A5:A45))"
Das geht so nicht!
Denn Formulalocal schreibt die Formel, genau so wie sie da steht, direrkt in die Tabellenzelle. Und das bedeutet, dass es Fehlermeldungen in der Tabelle hagelt weil Calc mit *(sheets(i)* nix anfangen kann.

Warum trägst Du die Formel mit den Tabellennamen nicht dirket in die Zelle rein?
Oder, warum versuchst Du nicht im Makro selber zu rechnen, und lässt dir das Ergebnis an gewünschter Stelle ausgeben? Wie das genau funktioniert kann ich dir aus Mangel an Erfahrung auch noch nicht sagen, aber hier ein Link um damit mal zu üben.
http://www.dannenhoefer.de/faqstarbasic ... l#Zweig229

Wenn Du aber unbedingt die Formel im Makro erst erstellen und dann in die Tabelle eintragen willst, so würde das wohl gehen. Das aber dann mit einem größeren Aufwand, wie das angehängte Beispiel für einen feststehenden Suchbegriff beweist :wink:.

Kannst Du das jetzt selber umstricken :) ?


Gruß
balu
Dateianhänge
FormelnZusammenbauen.ods
(8.79 KiB) 100-mal heruntergeladen
Sei öfter mal ein Faultier, sag öfter mal "Ach was!" Dann kriegst du keinen Herzinfarkt, und hast ne menge Spass.

wehr rächtschraipfähler findet khan si behalden :D
F3K Total
********
Beiträge: 3724
Registriert: Mo, 28.02.2011 17:49

Re: Makro auf bestimmte Tabellenblätter anwenden

Beitrag von F3K Total »

So, bei mit läufts:

Code: Alles auswählen

Sub Auswertung

Dim Doc As Object
Dim Sheet As Object
Dim Cell As Object
dim Begriff(3)
Doc = ThisComponent

Auswertungssheet=Doc.sheets.getbyname("Auswertung")

for i = 1 to 3 'Suchbegriffe eingeben
begriff(i)=inputbox ("Begriff "+i+": ","Suchbegriffe eingeben")
next i

for i =0 to 2
   osheetname=Doc.sheets(i).name 'Namen der Tabellen bestimmen
   for k = 1 to 3
      Cell = Auswertungssheet.getCellByPosition(i, k)
      Cell.Formulalocal = "=SUMMENPRODUKT((("+osheetname+".B5:"+osheetname+".B45="""+begriff(k)+"""))*("+osheetname+".A5:"+osheetname+".A45))"
   next k
next i
End Sub
Auswertung_3_Sheets.ods
Gruß R
Benutzeravatar
balu
********
Beiträge: 3812
Registriert: Fr, 24.08.2007 00:28
Wohnort: Warstein

Re: Makro auf bestimmte Tabellenblätter anwenden

Beitrag von balu »

Hallo R,

warum erstellst Du denn so einen Zellbereich?

Code: Alles auswählen

Tabelle1.B5:Tabelle1.B45
Was hast Du denn gegen?

Code: Alles auswählen

Tabelle1.B5:B45
Bei einer Formel die 3D kann, wie z.B. =SUMME(), könnte ich es ja noch verstehen. Aber nicht bei SUMMENPRODUKT, denn die kann nämlich nicht 3D. Und deshalb bringt das hier nichts.

3D ist die Fähigkeit über mehrere Tabellenblätter hinaus Berechnungen durchzuführen. Und bei SUMME wäre das dann also das Summieren aller Werte in den Zellbereichen A5 bis A45 und das in den Blättern Tabelle1 bis Tabelle3.

Code: Alles auswählen

=SUMME(Tabelle1.A5:Tabelle3.A45)
Also würde ich deinen Makro-Code an entsprechender Stelle wie folgt ändern.

Code: Alles auswählen

Cell.Formulalocal = "=SUMMENPRODUKT(("+osheetname+".B5:B45="""+begriff(k)+""")*("+osheetname+".A5:A45))"
Und außerdem hattest Du noch eine öffnende Klammer '(' und eine schließende Klammer ')' zuviel drin. :wink:


Gruß
balu
Sei öfter mal ein Faultier, sag öfter mal "Ach was!" Dann kriegst du keinen Herzinfarkt, und hast ne menge Spass.

wehr rächtschraipfähler findet khan si behalden :D
F3K Total
********
Beiträge: 3724
Registriert: Mo, 28.02.2011 17:49

Re: Makro auf bestimmte Tabellenblätter anwenden

Beitrag von F3K Total »

Na, Balu, du nimmst es ja ganz genau.

Unsere beiden Beiträge haben sich wohl zeitlich überschnitten?
Du Hast Recht, mit der kürzeren Schreibweise des Cellrange, auch mit den Klammern, die ich übernommen habe, das Berechnungsergebnis ist aber gleich!
Mir ging es in erster Linie darum, Michael bzgl. der sheets zu helfen.

Darum noch ein Hinweis für Michael:

Es gibt mehrere Methoden ein Tabellenblatt (Sheet) anzusprechen:
- doc.Sheets(0)
- doc.sheets.getbyIndex(0)
- doc.sheets.getbyName("Tabelle1")
sind die mir bekannten.

Die drei Methoden sprechen hier genau das gleiche Tabellenblatt an, das erste von links, weil das erste Blatt den Index 0 und den Namen "Tabelle1" hat.
Das zweite Tabellenblatt in der Datei von Michael wäre:
- doc.Sheets(1)
- doc.sheets.getbyIndex(1)
- doc.sheets.getbyName("Tabelle2")

Also ist "doc.Sheets(3)" hier gleichwertig mit "doc.sheets.getbyname("Auswertung")"

Ich habe nun den Code den Wünschen Balus entsprechend verfeinert:

Code: Alles auswählen

Sub Auswertung

Dim Doc As Object
Dim Sheet As Object
Dim Cell As Object
dim Begriff(3)
Doc = ThisComponent
Auswertungssheet=Doc.sheets.getbyname("Auswertung")

for i = 1 to 3 'Suchbegriffe eingeben
Begriff(i)=inputbox ("Begriff "+i+": ","Suchbegriffe eingeben")
next i

for i =0 to 2
osheetname=Doc.sheets(i).name
for k = 1 to 3
Cell = Auswertungssheet.getCellByPosition(i, k)
Cell.Formulalocal = "=SUMMENPRODUKT(("+osheetname+".B5:B45="""+begriff(k)+""")*("+osheetname+".A5:A45))"
next k
next i
End Sub
Auswertung_3_Sheets.ods
Gruß R
Benutzeravatar
balu
********
Beiträge: 3812
Registriert: Fr, 24.08.2007 00:28
Wohnort: Warstein

Re: Makro auf bestimmte Tabellenblätter anwenden

Beitrag von balu »

Hallo R,
Na, Balu, du nimmst es ja ganz genau.
Och nöööö. Hab ja nur angesprochen was mir so auffiel, und deshalb was dazu gefragt. Hätt ja durchaus sein können das Du einen bestimmten Grund hattest warum Du das so gemacht hast. Zum Beispiel als Umsteiger von einem anderen Tabellenprogram wo so etwas normal wäre.

Also nix für ungut. Is alles halb so wild. :wink:


Nun warten wirs mal ab was Michael sagen wird, ob er nu zurecht kommt.


Gruß
balu
Sei öfter mal ein Faultier, sag öfter mal "Ach was!" Dann kriegst du keinen Herzinfarkt, und hast ne menge Spass.

wehr rächtschraipfähler findet khan si behalden :D
MichaelSP
Beiträge: 7
Registriert: Mi, 06.01.2010 15:47

Re: Makro auf bestimmte Tabellenblätter anwenden

Beitrag von MichaelSP »

DANKE!!! Das war meine Rettung.
Antworten