OpenOffice.info

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

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

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

Code: Alles auswählen

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

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

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

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