Erste leere Zeile in Tabelle finden

[herbert r.]

Hallo Forum,
ich möchte Daten aus einem Formularblatt in ein Tabellenblatt schreiben, aber immer in die nächste leere Zeile.
Was ich noch suche ist ein Basic-Routine mit der ich die erste leere Zeile einer Tabelle ermitteln kann.
Es würde reichen, zu prüfen ob die Zelle in Spalte A leer ist.

In Excel machte ich das mit: "Cells(65536, 1).End(xlUp).Offset(1, 0).Select"

In Calc kenne ich mich noch nicht so gut aus, deshalb bitte ich um Eure Hilfe.
Vielen Dank im Voraus.
herbert r.

[turtle47]

Hallo Herbert,

versuch es mal hiermit:

Code: Alles auswählen

Sub Leere_Zelle_Spalte_A_Suchen
       odoc=thiscomponent
       oSheet = ThisComponent.CurrentController.getActiveSheet() 'aktives Tabellenblatt
       oEndrow = 0
       while oSheet.getcellbyPosition(0,oEndrow).string <> ""
       oEndrow = oEndrow+1
       wend
       z= oEndrow 'Achtung: der Index fängt bei Null an zu Zählen. z.B. Zeile 17 ist Index 16
       MsgBox z  'Anstelle der MsgBox eigenen Code fortführen
End Sub

Viel Erfolg.

Jürgen

[herbert r.]

hallo Jürgen,
vielen Dank für Deine schnelle Problemlösung! Das funktioniert.
Aber mir stellt sich leider schon das nächste Problem:
Ich muss nicht einzelne Zellen aus einem Datenblatt kopieren und in einem anderen Einfügen sondern einen ganzen Bereich.
Dier Quellbereich ist bekannt ("A2:Y2"). Der Zielbereich liegt in der o.g. leeren Zeile von Spalte A an.
Durch deine Prozedur weiß ich jetzt die Zeilennummer. Aber da ich mit einem Array arbeite (keine Ahnung wie ich es anders machen könnte)
muss ich auch ein Array einfügen und dies klappt nicht.
Meine Datenquelle erhalte ich über:
.
oBereichQuelle=oDoc.Sheets.getByName("Hilfstabelle").getCellRangeByName("a2:y2").getDataArray()
aDat=oBereichQuelle
.
Ziel war bisher:
oBereichZiel=oDoc.Sheets.getByName("Daten").getCellRangeByName("a3:y3")
oBereichZiel.setDataArray(aDat())
.
Beim Versuch mit Deinem code und in meinem "Zielteil" ergänzt durch .GetCellByPosition(0, z+1)
erhalte ich die Fehlermeldung
"Laufzeitfehler ...Type:com.sun.star.uno.Runtime.Exception Message:. "
Es wäre super, wenn du mir hier nochmal weiterhelfen könntest!
Momentan stehe ich wohl etwas auf der Leitung ;-(
vielen Dank!
Gruß, herbert

[turtle47]

Hi Herbert,

Du musst nicht mit einem Array arbeiten, es geht auch so:

Code: Alles auswählen

Sub Zeilenbereich_kopieren
   oDocument = ThisComponent
   oSheet1 = oDocument.Sheets.getByName("Hilfstabelle")
   oQuelleRange=oSheet1.getCellRangeByPosition(0,1,24,1)
   oQuellRangeAddresse = oQuelleRange.getRangeAddress
   oSheet2 = oDocument.Sheets.getByName("Daten")
   oEndrow = 0
   while oSheet2.getcellbyPosition(0,oEndrow).string <> ""
   oEndrow = oEndrow+1
   wend
   z= oEndrow
   oZiel = oSheet2.getCellByPosition(0,z)
   oZielCellAdresse=oZiel.getCellAddress
   oSheet2.copyRange(oZielCellAdresse,oQuellRangeAddresse)
End Sub

Viel Erfolg.

Jürgen

[herbert r.]

Hallo Jürgen,
vielen Dank!
Das ist eine (fast) perfekte Lösung!
Ich möchte ja nicht unverschämt erscheinen, aber jetzt wüsste ich nur noch gerne, wie statt der Formeln im Ziel die Werte eingefügt werden.
Das muss wohl eine alternative Form von "copyRange(...) sein, oder?

Aber erst mal schönes Wochenende. Vielleicht finde ich die Lösung ja auch selbst raus.

herbert

[Toxitom]

Hallo zusammen,

man kann zwar die erste leere Zelle durch eine Schleife ermiteln (kleine Bitte am Rand: Keine "While ... wend" Schleife nutzen. Die ist "überholt". Nehmt die "Do.... loop" Schleife, die könnt ihr zur Not auch auch verlassen.

Ansonsten schaut Euch mal die Methode "gotoEndOfUsedArea(bFlag)" des Tabellenblatt-Cursors an. Die ist nicht nur eleganter - auch performer bei großen Tabellen!

Um eine Codetipp abzugeben, bräcute ich mehr Informationen: Gibt es unterhalb der Tabelle generell keine weiteren Daten? Alos auch links oder rechts davon? Dann könnt ihr den allgemeinen Tabellencursor (createCursor())nehmen.
Wenn doch, dann nehmt den Tabellenbereichscursor (CreateCursorByRange()).
Um das mal mal Beispiel zu erläutern:

Code: Alles auswählen

sub letzteZeile
  oCursor = thisComponent.sheets(0).createCursor()
  oCursor.goToEndOfUsedArea(False)  'setzt den cursor in die letzte Zelle (links unten) des benutzen Bereiches
  msgbox oCursor.getRangeAddress.EndRow  'liefert den Index der letzten genutzten Zeile
  msgbox "die nächste leere Zeile ist die Nr: " & oCursor.getRangeAddress.EndRow +2
end sub

Viele Grüße
Thomas

[turtle47]

Hallo Thomas,

man kann zwar die erste leere Zelle durch eine Schleife ermiteln (kleine Bitte am Rand: Keine "While ... wend" Schleife nutzen. Die ist "überholt". Nehmt die "Do.... loop" Schleife, die könnt ihr zur Not auch auch verlassen.

Danke für den Hinweis. Werde es mir hinter die Ohren schreiben.

Was ich noch suche ist ein Basic-Routine mit der ich die erste leere Zeile einer Tabelle ermitteln kann.
Es würde reichen, zu prüfen ob die Zelle in Spalte A leer ist.

Deshalb habe ich den Code für Spalte A durchsuchen genommen. Das hat natürlich den Nachteil, dass wirklich die erste leere Zelle in Spalte A gesucht wird. Wenn danach noch etwas kommt dann kann das unter Umständen zu Problemen führen. Das hatte ich jetzt aber für mich einmal ausgeschlossen.

...ich nur noch gerne, wie statt der Formeln im Ziel die Werte eingefügt werden.

dafür habe ich folgendes mit dem Makrorecorder aufgezeichnet um die Formeln zu eliminieren:

Code: Alles auswählen

sub Zeile_kopieren
	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$2:$Y$2"
	
	dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())
	
	rem ----------------------------------------------------------------------
	dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())
	
	rem ----------------------------------------------------------------------
	dim args3(0) as new com.sun.star.beans.PropertyValue
	args3(0).Name = "Nr"
	args3(0).Value = 2
	
	dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, args3())
	
	rem ----------------------------------------------------------------------
	dim args4(0) as new com.sun.star.beans.PropertyValue
	args4(0).Name = "ToPoint"
	args4(0).Value = "$A$3:$Y$3"
	
	dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args4())
	
	rem ----------------------------------------------------------------------
	dim args5(5) as new com.sun.star.beans.PropertyValue
	args5(0).Name = "Flags"
	args5(0).Value = "SVDNT"
	args5(1).Name = "FormulaCommand"
	args5(1).Value = 0
	args5(2).Name = "SkipEmptyCells"
	args5(2).Value = false
	args5(3).Name = "Transpose"
	args5(3).Value = false
	args5(4).Name = "AsLink"
	args5(4).Value = false
	args5(5).Name = "MoveMode"
	args5(5).Value = 4
	dispatcher.executeDispatch(document, ".uno:InsertContents", "", 0, args5())
end sub

Wie bringe ich jetzt das Makro für die letzte Zelle Spalte A und das Makro Zeile_kopieren zusammen?

Das müsste meiner Meinung nach ja hier passieren:

Code: Alles auswählen

args4(0).Value = "$A$3:$Y$3"

Vorab schon mal vielen Dank.

Jürgen

[Karolus]

Hallo
Ich hab das mal weitestgehend mit aufgezeichnetem Code gelöst, ich musste lediglich eine if...elseif Abfrage einbauen damit ".uno:GoDownToEndOfData" bei leerer erster Zeile nicht in die letzte Zeile springt.

Code: Alles auswählen

 sub Hilfzeile_nach_Daten_kopieren
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")

dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "ToPoint"
args1(0).Value = "Hilfstabelle.A2:Y2"

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

dim args3(0) as new com.sun.star.beans.PropertyValue
args3(0).Name = "ToPoint"
args3(0).Value = "Daten.A1"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args3())

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 = false

if thisComponent.sheets().getbyname("Daten").getCellRangeByName("A1").string <> ""_
and thisComponent.sheets().getbyname("Daten").getCellRangeByName("A2").string <> "" then
	dispatcher.executeDispatch(document, ".uno:GoDownToEndOfData", "", 0, args4())
	'^^^entspricht 'Strg+↓'
	dispatcher.executeDispatch(document, ".uno:GoDown", "", 0, args4())

elseif thisComponent.sheets().getbyname("Daten").getCellRangeByName("A1").string <> "" then
	dispatcher.executeDispatch(document, ".uno:GoDown", "", 0, args4())
end if
rem ----------------------------------------------------------------------
dim args6(5) as new com.sun.star.beans.PropertyValue
args6(0).Name = "Flags"
args6(0).Value = "SVDNT"
args6(1).Name = "FormulaCommand"
args6(1).Value = 0
args6(2).Name = "SkipEmptyCells"
args6(2).Value = false
args6(3).Name = "Transpose"
args6(3).Value = false
args6(4).Name = "AsLink"
args6(4).Value = false
args6(5).Name = "MoveMode"
args6(5).Value = 4

dispatcher.executeDispatch(document, ".uno:InsertContents", "", 0, args6())
end sub

Gruß Karo

[turtle47]

Hallo Karolus,

vielen Dank für die mal wieder professionelle Hilfe.

Jürgen

[herbert r.]

Guten Morgen miteinander,
ich möchte mich bei euch allen sehr herzlich für die Hilfe bedanken!
Habe einiges dazu gelernt.
Und das Beste: Die Lösung von Karolus funktioniert einwandfrei!
Jetzt sollte ich nur noch versuchen auch alle Code-Zeilen zu verstehen

Viele Grüße
herbert r.

[Karolus]

Hallo

Als Nachtrag liefere ich hier noch "richtigen" Code nach, das ganze funktioniert mit 'get... und setdataArray' wunderbar.

Code: Alles auswählen

 Sub Daten_in_erste_Leerzeile
odoc = ThisComponent
inblatt = odoc.sheets().getbyname("Hilfstabelle") 'Richtigen Tabellennamen eintragen
outblatt = odoc.sheets().getbyname("Daten")'"""^
inbereich = inblatt.getcellrangebyname("A2:Y2").getdataArray()
endSp = ubound(inbereich(0)) ' ! ubound(inbereich()) liefert den Zeilenindex (1. Dimension des Arrays)
oCursor = outblatt.createCursor()
  oCursor.goToEndOfUsedArea(False)  'setzt den cursor in die letzte Zelle (links unten) des benutzen 'Bereiches
  endzeile = oCursor.getRangeAddress.EndRow
outbereich = outblatt.getcellrangebyposition(0,endzeile +1,endsp,endzeile +1)
outbereich.setdataArray(inbereich)
End Sub

Gruß Karo

[herbert r.]

Hallo Karolus,
deine neue Prozedur funktioniert im Prinzip sehr gut und ist auch für mich als OOo-Anfänger halbwegs verständlich, aber:

es wird der sog. benutzte Bereich angesteuert "goToEndOfUsedArea()", der bei mir z.Zt. bis Zeile 10.000 geht, da ich dort früher schon mal was eingetragen habe (was inzwischen wider gelöscht wurde).
D.h. leere Zeilen von z.B. Zeile 4 bis 10.000 werden übersprungen und ab Zeile 10.001 wird geschrieben, da dort vorher noch nie etwas stand.
- (das gilt übrigens auch für deine vorherige Prozedur "Hilfzeile_nach_Daten_kopieren", wie ich erst jetzt durch Test herausgefunden habe.-

Für meine Anwendung ist das nicht weiter tragisch, da ich vorher selbst die sog. benutzten Zeilen durch "Bearbeiten/Zellen löschen" bereinigen kann, und der User hat in diesem Tabellenblatt sowieso nichts zu suchen.

Also nochmals vielen Dank für die professionelle Hilfe
Gruß, herbert

[Karolus]

Hallo Herbert
Ich bin davon ausgegangen daß das Blatt 'Daten' von oben gefüllt wird, und sonst leer ist, wenn das nicht so ist dann:

.....
Um eine Codetipp abzugeben, bräcute ich mehr Informationen: Gibt es unterhalb der Tabelle generell keine weiteren Daten? Alos auch links oder rechts davon? Dann könnt ihr den allgemeinen Tabellencursor (createCursor())nehmen.
Wenn doch, dann nehmt den Tabellenbereichscursor (CreateCursorByRange()).

Gruß Karo

[herbert r.]

Hallo Karolus,
es gibt normalerweise keine weiteren Daten unterhalb der bestehenden Tabelle, aber wenn in einer Zeile unterhalb irgendwann mal -z.B. testhalber- Daten eingetragen waren, dann gilt bis dorthin der sog. benutze Bereich.
Ist aber, wie erwähnt nicht so wichtig, da ich vor Benutzung die Tabelle bereinige und der Anwender dort nichts einzutragen hat.
Außerdem kann ich das oben Gesagte momentan nicht mehr nachvollziehen, vielleicht hatte ich auch nur einen Fehler gemacht, und doch noch irgenwo, weit unten Daten stehen.
Vielen Dank für eure Bemühungen

Gruß, Herbert