von little fingers » Mo, 14.03.2022 03:57
Na Klar!
Ist ein bisschen länger, weil mehrere Makros hintereinander ausgeführt!
Aber schon beim Textimport bemerkt man schon eine heftige Verzögerung!
Gruß Ingo
Code: Alles auswählen
REM ***** BASIC *****
Sub AlleMakros
all_Loeschen()
import()
ROT()
ROT2()
SpaltenOptimiern()
KundenUebertrag()
GewichtsUebertrag()
UmsatzUebertrag()
ddloeschen()
FarbAenderung()
end Sub
Sub CSVImport
all_Loeschen()
import()
ROT()
ROT2()
SpaltenOptimiern()
FarbAenderung()
end Sub
sub all_Loeschen
oCalc = thisComponent
oSheet = oCalc.sheets(0)
oCellRange = osheet.getCellRangeByName("A1:Q200")
' ocellRange.clearContents(5)
end Sub
sub import
Wait 3000 '1000 Millisekunden warten
odoc=thiscomponent
REM odoc.Sheets.insertNewByName("neue Tabelle",odoc.Sheets.count)
otab=odoc.sheets.getByName("LStatistik")
sUrl=converttourl("V:\TXT\L-Statistik-Touren.csv")
otab.link(sUrl,"","Text - txt - csv (StarCalc)","59,34,0,1",com.sun.star.sheet.SheetLinkMode.NORMAL)
oTab.linkmode=com.sun.star.sheet.SheetLinkMode.NONE
End Sub
Sub KundenUebertrag()
Wait 1000 '1000 Millisekunden warten
With ThisComponent.Sheets.getByName("Uebertrag")
ThisComponent.Sheets.getByName("LStatistik").getCellrangeByName("F2:F200").setDataArray(.getCellrangeByName("H2:H200").getDataArray)
End With
End Sub
Sub GewichtsUebertrag()
Wait 1000 '1000 Millisekunden warten
With ThisComponent.Sheets.getByName("Uebertrag")
ThisComponent.Sheets.getByName("LStatistik").getCellrangeByName("N2:N200").setDataArray(.getCellrangeByName("B2:B200").getDataArray)
End With
End Sub
Sub UmsatzUebertrag()
Wait 1000 '1000 Millisekunden warten
With ThisComponent.Sheets.getByName("Uebertrag")
ThisComponent.Sheets.getByName("LStatistik").getCellrangeByName("O2:O200").setDataArray(.getCellrangeByName("E2:E200").getDataArray)
End With
End Sub
Sub ddloeschen
Wait 1000 '1000 Millisekunden warten
ozeile=ThisComponent.CurrentController.ActiveSheet.Columns(5) 'F
oleer=ozeile.queryemptycells
oletzter=oleer(oleer.count-1)
erg = oletzter.rangeaddress.startrow-1
With ThisComponent.CurrentController.ActiveSheet
For i = 0 To erg
k = .getCellByPosition(5, i).String
For j = i+1 To erg
If .getCellByPosition(5, j).String = k Then
For jj = 0 To 5
.getCellByPosition(jj, j).String = ""
Next jj
End If
Next j
Next i
msgbox ("doppelte Einträge wurden gelöscht")
End With
End Sub
sub ROT
Tabelle = ThisComponent.Sheets(0)
CellRange = Tabelle.getCellRangeByName("C1:Q200")
CellRange.CharFontName="Tahoma"
CellRange.CharHeight="11"
CellRange.CellBackColor=RGB(251,131, 90)
' CellRange.CharWeight=com.sun.star.awt.FontWeight.BOLD
'Überschriften positionieren
'horijustify=1 <- links
'horijustify=2 <- mitte
'horijustify=3 <- rechts
'horijustify=4 <- nicht nur umbrochen -->Blocksatz
CellRange.horijustify = com.sun.star.table.CellHoriJustify.CENTER'Mittig
CellRange.IsTextWrapped = false'Umbruch
' CellRange.IsTextWrapped = true'Umbruch
End Sub
sub ROT2
Tabelle = ThisComponent.Sheets(0)
CellRange = Tabelle.getCellRangeByName("A1:A200")
CellRange.CharFontName="Tahoma"
CellRange.CharHeight="11"
CellRange.CellBackColor=RGB(251,131, 90)
' CellRange.CharWeight=com.sun.star.awt.FontWeight.BOLD
'Überschriften positionieren
'horijustify=1 <- links
'horijustify=2 <- mitte
'horijustify=3 <- rechts
'horijustify=4 <- nicht nur umbrochen -->Blocksatz
CellRange.horijustify = com.sun.star.table.CellHoriJustify.CENTER'Mittig
CellRange.IsTextWrapped = false'Umbruch
' CellRange.IsTextWrapped = true'Umbruch
End Sub
Sub SpaltenOptimiern
oDoc = thisComponent
oSheet = oDoc.sheets(0)
oSp = oSheet.getColumns()
oSp.optimalWidth = true
End Sub
Sub FarbAenderung()
oDoc = thisComponent 'das Dokument
oSheet = oDoc.sheets(0) 'erstes Tabellenblatt
For ze = 0 to 200 'Zeilenzähler
For sp = 1 to 1 'Spaltenzähler
oCell = oSheet.getCellByPosition(sp,ze)
Select Case oCell.String
Case ""
oCell.CellBackColor = -1
' 1er Touren
Case "110"
oCell.CellBackColor = RGB(180,199, 220)
Case "120"
oCell.CellBackColor = RGB(0,102, 204)
Case "131"
oCell.CellBackColor = RGB(250,255, 102)
Case "140"
oCell.CellBackColor = RGB(0,174, 0)
Case "125"
oCell.CellBackColor = RGB(255,153, 102)
Case "135"
oCell.CellBackColor = RGB(255,51, 51)
Case "155"
oCell.CellBackColor = RGB(15,252, 204)
Case "175"
oCell.CellBackColor = RGB(167,132, 244)
Case "185"
oCell.CellBackColor = RGB(250,215, 122)
Case "195"
oCell.CellBackColor = RGB(0,134, 7)
' 2er Touren
Case "220"
oCell.CellBackColor = RGB(180,199, 220)
Case "240"
oCell.CellBackColor = RGB(0,102, 204)
Case "290"
oCell.CellBackColor = RGB(250,255, 102)
Case "205"
oCell.CellBackColor = RGB(0,174, 0)
Case "295"
oCell.CellBackColor = RGB(255,153, 102)
' 3er Touren
Case "320"
oCell.CellBackColor = RGB(180,199, 220)
Case "341"
oCell.CellBackColor = RGB(0,102, 204)
Case "340"
oCell.CellBackColor = RGB(250,255, 102)
Case "331"
oCell.CellBackColor = RGB(0,174, 0)
Case "315"
oCell.CellBackColor = RGB(255,153, 102)
Case "335"
oCell.CellBackColor = RGB(255,51, 51)
Case "355"
oCell.CellBackColor = RGB(15,252, 204)
Case "375"
oCell.CellBackColor = RGB(167,132, 244)
Case "385"
oCell.CellBackColor = RGB(250,215, 122)
Case "395"
oCell.CellBackColor = RGB(0,134, 7)
' 4er Touren
Case "440"
oCell.CellBackColor = RGB(180,199, 220)
Case "420"
oCell.CellBackColor = RGB(0,102, 204)
Case "470"
oCell.CellBackColor = RGB(250,255, 102)
Case "405"
oCell.CellBackColor = RGB(0,174, 0)
Case "415"
oCell.CellBackColor = RGB(255,153, 102)
' 5er Touren
Case "510"
oCell.CellBackColor = RGB(180,199, 220)
Case "520"
oCell.CellBackColor = RGB(0,102, 204)
Case "541"
oCell.CellBackColor = RGB(250,255, 102)
Case "540"
oCell.CellBackColor = RGB(0,174, 0)
Case "525"
oCell.CellBackColor = RGB(255,153, 102)
Case "535"
oCell.CellBackColor = RGB(255,51, 51)
Case "555"
oCell.CellBackColor = RGB(15,252, 204)
Case "575"
oCell.CellBackColor = RGB(167,132, 244)
Case "585"
oCell.CellBackColor = RGB(250,215, 122)
Case "595"
oCell.CellBackColor = RGB(0,134, 7)
' 6er Touren
Case "695"
oCell.CellBackColor = RGB(180,199, 220)
Case "690"
oCell.CellBackColor = RGB(0,102, 204)
End Select
next
next
End Sub
Na Klar!
Ist ein bisschen länger, weil mehrere Makros hintereinander ausgeführt!
Aber schon beim Textimport bemerkt man schon eine heftige Verzögerung!
Gruß Ingo
[code]
REM ***** BASIC *****
Sub AlleMakros
all_Loeschen()
import()
ROT()
ROT2()
SpaltenOptimiern()
KundenUebertrag()
GewichtsUebertrag()
UmsatzUebertrag()
ddloeschen()
FarbAenderung()
end Sub
Sub CSVImport
all_Loeschen()
import()
ROT()
ROT2()
SpaltenOptimiern()
FarbAenderung()
end Sub
sub all_Loeschen
oCalc = thisComponent
oSheet = oCalc.sheets(0)
oCellRange = osheet.getCellRangeByName("A1:Q200")
' ocellRange.clearContents(5)
end Sub
sub import
Wait 3000 '1000 Millisekunden warten
odoc=thiscomponent
REM odoc.Sheets.insertNewByName("neue Tabelle",odoc.Sheets.count)
otab=odoc.sheets.getByName("LStatistik")
sUrl=converttourl("V:\TXT\L-Statistik-Touren.csv")
otab.link(sUrl,"","Text - txt - csv (StarCalc)","59,34,0,1",com.sun.star.sheet.SheetLinkMode.NORMAL)
oTab.linkmode=com.sun.star.sheet.SheetLinkMode.NONE
End Sub
Sub KundenUebertrag()
Wait 1000 '1000 Millisekunden warten
With ThisComponent.Sheets.getByName("Uebertrag")
ThisComponent.Sheets.getByName("LStatistik").getCellrangeByName("F2:F200").setDataArray(.getCellrangeByName("H2:H200").getDataArray)
End With
End Sub
Sub GewichtsUebertrag()
Wait 1000 '1000 Millisekunden warten
With ThisComponent.Sheets.getByName("Uebertrag")
ThisComponent.Sheets.getByName("LStatistik").getCellrangeByName("N2:N200").setDataArray(.getCellrangeByName("B2:B200").getDataArray)
End With
End Sub
Sub UmsatzUebertrag()
Wait 1000 '1000 Millisekunden warten
With ThisComponent.Sheets.getByName("Uebertrag")
ThisComponent.Sheets.getByName("LStatistik").getCellrangeByName("O2:O200").setDataArray(.getCellrangeByName("E2:E200").getDataArray)
End With
End Sub
Sub ddloeschen
Wait 1000 '1000 Millisekunden warten
ozeile=ThisComponent.CurrentController.ActiveSheet.Columns(5) 'F
oleer=ozeile.queryemptycells
oletzter=oleer(oleer.count-1)
erg = oletzter.rangeaddress.startrow-1
With ThisComponent.CurrentController.ActiveSheet
For i = 0 To erg
k = .getCellByPosition(5, i).String
For j = i+1 To erg
If .getCellByPosition(5, j).String = k Then
For jj = 0 To 5
.getCellByPosition(jj, j).String = ""
Next jj
End If
Next j
Next i
msgbox ("doppelte Einträge wurden gelöscht")
End With
End Sub
sub ROT
Tabelle = ThisComponent.Sheets(0)
CellRange = Tabelle.getCellRangeByName("C1:Q200")
CellRange.CharFontName="Tahoma"
CellRange.CharHeight="11"
CellRange.CellBackColor=RGB(251,131, 90)
' CellRange.CharWeight=com.sun.star.awt.FontWeight.BOLD
'Überschriften positionieren
'horijustify=1 <- links
'horijustify=2 <- mitte
'horijustify=3 <- rechts
'horijustify=4 <- nicht nur umbrochen -->Blocksatz
CellRange.horijustify = com.sun.star.table.CellHoriJustify.CENTER'Mittig
CellRange.IsTextWrapped = false'Umbruch
' CellRange.IsTextWrapped = true'Umbruch
End Sub
sub ROT2
Tabelle = ThisComponent.Sheets(0)
CellRange = Tabelle.getCellRangeByName("A1:A200")
CellRange.CharFontName="Tahoma"
CellRange.CharHeight="11"
CellRange.CellBackColor=RGB(251,131, 90)
' CellRange.CharWeight=com.sun.star.awt.FontWeight.BOLD
'Überschriften positionieren
'horijustify=1 <- links
'horijustify=2 <- mitte
'horijustify=3 <- rechts
'horijustify=4 <- nicht nur umbrochen -->Blocksatz
CellRange.horijustify = com.sun.star.table.CellHoriJustify.CENTER'Mittig
CellRange.IsTextWrapped = false'Umbruch
' CellRange.IsTextWrapped = true'Umbruch
End Sub
Sub SpaltenOptimiern
oDoc = thisComponent
oSheet = oDoc.sheets(0)
oSp = oSheet.getColumns()
oSp.optimalWidth = true
End Sub
Sub FarbAenderung()
oDoc = thisComponent 'das Dokument
oSheet = oDoc.sheets(0) 'erstes Tabellenblatt
For ze = 0 to 200 'Zeilenzähler
For sp = 1 to 1 'Spaltenzähler
oCell = oSheet.getCellByPosition(sp,ze)
Select Case oCell.String
Case ""
oCell.CellBackColor = -1
' 1er Touren
Case "110"
oCell.CellBackColor = RGB(180,199, 220)
Case "120"
oCell.CellBackColor = RGB(0,102, 204)
Case "131"
oCell.CellBackColor = RGB(250,255, 102)
Case "140"
oCell.CellBackColor = RGB(0,174, 0)
Case "125"
oCell.CellBackColor = RGB(255,153, 102)
Case "135"
oCell.CellBackColor = RGB(255,51, 51)
Case "155"
oCell.CellBackColor = RGB(15,252, 204)
Case "175"
oCell.CellBackColor = RGB(167,132, 244)
Case "185"
oCell.CellBackColor = RGB(250,215, 122)
Case "195"
oCell.CellBackColor = RGB(0,134, 7)
' 2er Touren
Case "220"
oCell.CellBackColor = RGB(180,199, 220)
Case "240"
oCell.CellBackColor = RGB(0,102, 204)
Case "290"
oCell.CellBackColor = RGB(250,255, 102)
Case "205"
oCell.CellBackColor = RGB(0,174, 0)
Case "295"
oCell.CellBackColor = RGB(255,153, 102)
' 3er Touren
Case "320"
oCell.CellBackColor = RGB(180,199, 220)
Case "341"
oCell.CellBackColor = RGB(0,102, 204)
Case "340"
oCell.CellBackColor = RGB(250,255, 102)
Case "331"
oCell.CellBackColor = RGB(0,174, 0)
Case "315"
oCell.CellBackColor = RGB(255,153, 102)
Case "335"
oCell.CellBackColor = RGB(255,51, 51)
Case "355"
oCell.CellBackColor = RGB(15,252, 204)
Case "375"
oCell.CellBackColor = RGB(167,132, 244)
Case "385"
oCell.CellBackColor = RGB(250,215, 122)
Case "395"
oCell.CellBackColor = RGB(0,134, 7)
' 4er Touren
Case "440"
oCell.CellBackColor = RGB(180,199, 220)
Case "420"
oCell.CellBackColor = RGB(0,102, 204)
Case "470"
oCell.CellBackColor = RGB(250,255, 102)
Case "405"
oCell.CellBackColor = RGB(0,174, 0)
Case "415"
oCell.CellBackColor = RGB(255,153, 102)
' 5er Touren
Case "510"
oCell.CellBackColor = RGB(180,199, 220)
Case "520"
oCell.CellBackColor = RGB(0,102, 204)
Case "541"
oCell.CellBackColor = RGB(250,255, 102)
Case "540"
oCell.CellBackColor = RGB(0,174, 0)
Case "525"
oCell.CellBackColor = RGB(255,153, 102)
Case "535"
oCell.CellBackColor = RGB(255,51, 51)
Case "555"
oCell.CellBackColor = RGB(15,252, 204)
Case "575"
oCell.CellBackColor = RGB(167,132, 244)
Case "585"
oCell.CellBackColor = RGB(250,215, 122)
Case "595"
oCell.CellBackColor = RGB(0,134, 7)
' 6er Touren
Case "695"
oCell.CellBackColor = RGB(180,199, 220)
Case "690"
oCell.CellBackColor = RGB(0,102, 204)
End Select
next
next
End Sub
[/code]