Ich habe die Makros, es sind doch einige und ich konnte auch nicht eindeutig feststellen in welchem der Fehler auftritt, ursprünglich wegen ihrer Länge nicht beigelegt. Nachdem es sich dabei aber um einen (organisatorisch) relativ aufwändigen Konstrukt handelt habe ich jetzt alles hereinkopiert.
Code: Alles auswählen
Sub SiFo_Aktualisieren
'Zugriff auf die aktuelle Datei
oDoc=ThisComponent
'JumpToSheetsName
oView = oDoc.CurrentController
oSheet = oDoc.Sheets.getByName("SiFo")
oView.setActiveSheet(oSheet)
rem get access to the document
document = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
'Startmeldung
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "ToPoint"
args1(0).Value = "$F$1"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())
dim args2(0) as new com.sun.star.beans.PropertyValue
args2(0).Name = "StringName"
args2(0).Value = "Tabelle wird aktualisiert"
dispatcher.executeDispatch(document, ".uno:EnterString", "", 0, args2())
SiKo_CSV_Import
SiKo_ID_finden
SiFo_CSV_Import
SiFo_Zeilen_markieren
SiFo_Ueberschuss_loeschen
SiFo_Spalten_kopieren
'Fertigmeldung
dim args3(0) as new com.sun.star.beans.PropertyValue
args3(0).Name = "ToPoint"
args3(0).Value = "$F$1"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args3())
dim args4(0) as new com.sun.star.beans.PropertyValue
args4(0).Name = "StringName"
args4(0).Value = "Tabelle ist aktuell"
dispatcher.executeDispatch(document, ".uno:EnterString", "", 0, args4())
wait 1000
dim args6(0) as new com.sun.star.beans.PropertyValue
args6(0).Name = "Nr"
args6(0).Value = 1
dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, args6())
dim args7(0) as new com.sun.star.beans.PropertyValue
args7(0).Name = "ToPoint"
args7(0).Value = "$C$3"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args7())
End Sub
Sub SiKo_CSV_Import
'Zugriff auf die aktuelle Datei
oDoc=ThisComponent
'Öffnen der csv-Datei
Dim args(1) as New com.sun.star.beans.PropertyValue
args(0).Name= "FilterName"
args(0).Value = "Text - txt - csv (StarCalc)"
'ASCII-Codes 44 (Komma), 32 (Leerzeichen), 9 (Tabulator)
'Mehrfachauswahl durch / trennen, z. B. 44/9
sFeldSeparator="44"
'ASCII-Codes 34 (doppelte Hochkomma), 39 (einfache Hochkomma)
sTextBegrenzer="34"
'Systemzeichensatz 0 - I: Unicode (UTF.8): 76
sZeichensatz="76"
'erste einzulesende Zeile
sErste="1"
'Spaltenformat für jede Spalte festlegen
'Spalte/Format/Spalte/Format/...
'Formate: 1 Standard, 2 Text, 3 Datum (MTJ), 4 Datum (TMJ), 5 Datum (JMT), 9 nicht importieren, 10 US-englisch
'nicht aufgeführte Spalten werden als Standard importiert
sSpaltenFormate="1/1/2/2/3/2"
args(1).Name="FilterOptions"
args(1).Value =sFeldSeparator & "," & sTextBegrenzer & "," & sZeichensatz & "," & sErste & "," & sSpaltenformate
'Aktuelles Verzeichnis einlesen und Datei Sassen.csv öffnen
surl = thiscomponent.url
apfad = split(surl,"/")
apfad(ubound(apfad)) = "sippungskopf.csv"
sURL = convertToURL(join(apfad,"/"))
oCSV = StarDesktop.LoadComponentFromURL(sURL, "_blank", 0, args())
'1: Auslesen der Daten in ein Array der Daten
aDaten=oCSV.Sheets(0).getCellRangeByName("A1:C500").getDataArray
'Schreiben der Daten in die aktuelle Datei
oDoc.Sheets(4).getCellRangeByName("K3:M502").setDataArray(aDaten)
'CSV-Datei schließen
oCSV.close(false)
End Sub
sub SiKo_ID_finden
rem define variables
dim document as object
dim dispatcher as object
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 = "$J$4"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())
dim args2(0) as new com.sun.star.beans.PropertyValue
args2(0).Name = "StringName"
args2(0).Value = "=WENN(UND(WERT(L4)=Parameter.D$4;WERT(M4)=WERT(LINKS(Parameter.D$6;3))+1859);9;0)"
dispatcher.executeDispatch(document, ".uno:EnterString", "", 0, args2())
dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())
dim args4(0) as new com.sun.star.beans.PropertyValue
args4(0).Name = "ToPoint"
args4(0).Value = "$J$5"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args4())
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())
dim args6(1) as new com.sun.star.beans.PropertyValue
args6(0).Name = "By"
args6(0).Value = 1
args6(1).Name = "Sel"
args6(1).Value = true
dispatcher.executeDispatch(document, ".uno:GoDownToEndOfData", "", 0, args6())
dim args7(1) as new com.sun.star.beans.PropertyValue
args7(0).Name = "By"
args7(0).Value = 1
args7(1).Name = "Sel"
args7(1).Value = true
dispatcher.executeDispatch(document, ".uno:GoLeft", "", 0, args7())
dispatcher.executeDispatch(document, ".uno:Paste", "", 0, Array())
dim args9(0) as new com.sun.star.beans.PropertyValue
args9(0).Name = "ToPoint"
args9(0).Value = "$G$1"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args9())
dim args10(0) as new com.sun.star.beans.PropertyValue
args10(0).Name = "StringName"
args10(0).Value = "=SVERWEIS(9;J4:K500;2;1)"
dispatcher.executeDispatch(document, ".uno:EnterString", "", 0, args10())
dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())
dim args12(0) as new com.sun.star.beans.PropertyValue
args12(0).Name = "ToPoint"
args12(0).Value = "$H$1"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args12())
dim args13(0) as new com.sun.star.beans.PropertyValue
args13(0).Name = "ToPoint"
args13(0).Value = "$G$1"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args13())
dim args14(5) as new com.sun.star.beans.PropertyValue
args14(0).Name = "Flags"
args14(0).Value = "SVD"
args14(1).Name = "FormulaCommand"
args14(1).Value = 0
args14(2).Name = "SkipEmptyCells"
args14(2).Value = false
args14(3).Name = "Transpose"
args14(3).Value = false
args14(4).Name = "AsLink"
args14(4).Value = false
args14(5).Name = "MoveMode"
args14(5).Value = 4
dispatcher.executeDispatch(document, ".uno:InsertContents", "", 0, args14())
dim args15(0) as new com.sun.star.beans.PropertyValue
args15(0).Name = "ToPoint"
args15(0).Value = "J1:M500"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args15())
dispatcher.executeDispatch(document, ".uno:JumpToNextCell", "", 0, Array())
dim args17(0) as new com.sun.star.beans.PropertyValue
args17(0).Name = "Flags"
args17(0).Value = "L"
dispatcher.executeDispatch(document, ".uno:DeleteCell", "", 0, args17())
end sub
Sub SiFo_CSV_Import
'Zugriff auf die aktuelle Datei
oDoc=ThisComponent
'Öffnen der csv-Datei
Dim args(1) as New com.sun.star.beans.PropertyValue
args(0).Name= "FilterName"
args(0).Value = "Text - txt - csv (StarCalc)"
'ASCII-Codes 44 (Komma), 32 (Leerzeichen), 9 (Tabulator)
'Mehrfachauswahl durch / trennen, z. B. 44/9
sFeldSeparator="44"
'ASCII-Codes 34 (doppelte Hochkomma), 39 (einfache Hochkomma)
sTextBegrenzer="34"
'Systemzeichensatz 0 - I: Unicode (UTF.8): 76
sZeichensatz="76"
'erste einzulesende Zeile
sErste="1"
'Spaltenformat für jede Spalte festlegen
'Spalte/Format/Spalte/Format/...
'Formate: 1 Standard, 2 Text, 3 Datum (MTJ), 4 Datum (TMJ), 5 Datum (JMT), 9 nicht importieren, 10 US-englisch
'nicht aufgeführte Spalten werden als Standard importiert
sSpaltenFormate="2/1/3/1/4/1/5/1/7/2"
args(1).Name="FilterOptions"
args(1).Value =sFeldSeparator & "," & sTextBegrenzer & "," & sZeichensatz & "," & sErste & "," & sSpaltenformate
'Aktuelles Verzeichnis einlesen und Datei Sippungsfolgen.csv öffnen
surl = thiscomponent.url
apfad = split(surl,"/")
apfad(ubound(apfad)) = "sippungsfolgen.csv"
sURL = convertToURL(join(apfad,"/"))
oCSV = StarDesktop.LoadComponentFromURL(sURL, "_blank", 0, args())
'1: Auslesen der Daten in ein Array der Daten
aDaten=oCSV.Sheets(0).getCellRangeByName("B1:E20000").getDataArray
'Schreiben der Daten in die aktuelle Datei
oDoc.Sheets(4).getCellRangeByName("K3:N20002").setDataArray(aDaten)
'2: Auslesen der Daten in ein Array der Daten
aDaten=oCSV.Sheets(0).getCellRangeByName("G1:G20000").getDataArray
'Schreiben der Daten in die aktuelle Datei
oDoc.Sheets(4).getCellRangeByName("O3:O20002").setDataArray(aDaten)
'CSV-Datei schließen
oCSV.close(false)
End Sub
sub SiFo_Zeilen_markieren
rem define variables
dim document as object
dim dispatcher as object
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 = "$J$4"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())
dim args2(0) as new com.sun.star.beans.PropertyValue
args2(0).Name = "StringName"
args2(0).Value = "=WENN(UND(K4=WERT(G$1);WERT(L4)>0);"+CHR$(34)+"X"+CHR$(34)+";"+CHR$(34)+CHR$(34)+")"
dispatcher.executeDispatch(document, ".uno:EnterString", "", 0, args2())
dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())
dim args4(0) as new com.sun.star.beans.PropertyValue
args4(0).Name = "ToPoint"
args4(0).Value = "$J$5"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args4())
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())
dim args6(1) as new com.sun.star.beans.PropertyValue
args6(0).Name = "By"
args6(0).Value = 1
args6(1).Name = "Sel"
args6(1).Value = true
dispatcher.executeDispatch(document, ".uno:GoDownToEndOfData", "", 0, args6())
dim args7(1) as new com.sun.star.beans.PropertyValue
args7(0).Name = "By"
args7(0).Value = 1
args7(1).Name = "Sel"
args7(1).Value = true
dispatcher.executeDispatch(document, ".uno:GoLeft", "", 0, args7())
dispatcher.executeDispatch(document, ".uno:Paste", "", 0, Array())
dim args16(0) as new com.sun.star.beans.PropertyValue
args16(0).Name = "ToPoint"
args16(0).Value = "J4:O25000"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args16())
dim args17(8) as new com.sun.star.beans.PropertyValue
args17(0).Name = "ByRows"
args17(0).Value = true
args17(1).Name = "HasHeader"
args17(1).Value = false
args17(2).Name = "CaseSensitive"
args17(2).Value = false
args17(3).Name = "IncludeAttribs"
args17(3).Value = true
args17(4).Name = "UserDefIndex"
args17(4).Value = 0
args17(5).Name = "Col1"
args17(5).Value = 10
args17(6).Name = "Ascending1"
args17(6).Value = false
args17(7).Name = "Col2"
args17(7).Value = 10
args17(8).Name = "Ascending2"
args17(8).Value = true
dispatcher.executeDispatch(document, ".uno:DataSort", "", 0, args17())
dim args18(0) as new com.sun.star.beans.PropertyValue
args18(0).Name = "ToPoint"
args18(0).Value = "$J$1"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args18())
end sub
sub SiFo_Ueberschuss_loeschen
rem define variables
dim document as object
dim dispatcher as object
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 = "$J$1"
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 = "$A$34:$O$25000"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args3())
dim args4(0) as new com.sun.star.beans.PropertyValue
args4(0).Name = "Flags"
args4(0).Value = "U"
dispatcher.executeDispatch(document, ".uno:DeleteCell", "", 0, args4())
dim args5(0) as new com.sun.star.beans.PropertyValue
args5(0).Name = "ToPoint"
args5(0).Value = "$L$4"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args5())
end sub
sub SiFo_Spalten_kopieren
rem define variables
dim document as object
dim dispatcher as object
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 = "L4:L500"
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 = "$B$4"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args3())
dim args4(5) as new com.sun.star.beans.PropertyValue
args4(0).Name = "Flags"
args4(0).Value = "SVD"
args4(1).Name = "FormulaCommand"
args4(1).Value = 0
args4(2).Name = "SkipEmptyCells"
args4(2).Value = false
args4(3).Name = "Transpose"
args4(3).Value = false
args4(4).Name = "AsLink"
args4(4).Value = false
args4(5).Name = "MoveMode"
args4(5).Value = 4
dispatcher.executeDispatch(document, ".uno:InsertContents", "", 0, args4())
dim args5(0) as new com.sun.star.beans.PropertyValue
args5(0).Name = "ToPoint"
args5(0).Value = "$N$4"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args5())
dim args6(0) as new com.sun.star.beans.PropertyValue
args6(0).Name = "ToPoint"
args6(0).Value = "N4:N500"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args6())
dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())
dim args8(0) as new com.sun.star.beans.PropertyValue
args8(0).Name = "ToPoint"
args8(0).Value = "$C$4"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args8())
dim args9(5) as new com.sun.star.beans.PropertyValue
args9(0).Name = "Flags"
args9(0).Value = "SVD"
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())
dim args10(0) as new com.sun.star.beans.PropertyValue
args10(0).Name = "ToPoint"
args10(0).Value = "$M$4"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args10())
dim args11(0) as new com.sun.star.beans.PropertyValue
args11(0).Name = "ToPoint"
args11(0).Value = "M4:M500"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args11())
dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())
dim args13(0) as new com.sun.star.beans.PropertyValue
args13(0).Name = "ToPoint"
args13(0).Value = "$D$4"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args13())
dim args14(5) as new com.sun.star.beans.PropertyValue
args14(0).Name = "Flags"
args14(0).Value = "SVD"
args14(1).Name = "FormulaCommand"
args14(1).Value = 0
args14(2).Name = "SkipEmptyCells"
args14(2).Value = false
args14(3).Name = "Transpose"
args14(3).Value = false
args14(4).Name = "AsLink"
args14(4).Value = false
args14(5).Name = "MoveMode"
args14(5).Value = 4
dispatcher.executeDispatch(document, ".uno:InsertContents", "", 0, args14())
dim args15(0) as new com.sun.star.beans.PropertyValue
args15(0).Name = "ToPoint"
args15(0).Value = "$O$4"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args15())
dim args16(0) as new com.sun.star.beans.PropertyValue
args16(0).Name = "ToPoint"
args16(0).Value = "O4:O500"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args16())
dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())
dim args18(0) as new com.sun.star.beans.PropertyValue
args18(0).Name = "ToPoint"
args18(0).Value = "$F$4"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args18())
dim args19(5) as new com.sun.star.beans.PropertyValue
args19(0).Name = "Flags"
args19(0).Value = "SVD"
args19(1).Name = "FormulaCommand"
args19(1).Value = 0
args19(2).Name = "SkipEmptyCells"
args19(2).Value = false
args19(3).Name = "Transpose"
args19(3).Value = false
args19(4).Name = "AsLink"
args19(4).Value = false
args19(5).Name = "MoveMode"
args19(5).Value = 4
dispatcher.executeDispatch(document, ".uno:InsertContents", "", 0, args19())
dim args20(0) as new com.sun.star.beans.PropertyValue
args20(0).Name = "ToPoint"
args20(0).Value = "$G$1"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args20())
dim args21(0) as new com.sun.star.beans.PropertyValue
args21(0).Name = "StringName"
args21(0).Value = ""
dispatcher.executeDispatch(document, ".uno:EnterString", "", 0, args21())
dim args22(0) as new com.sun.star.beans.PropertyValue
args22(0).Name = "ToPoint"
args22(0).Value = "$J$1"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args22())
dim args23(0) as new com.sun.star.beans.PropertyValue
args23(0).Name = "ToPoint"
args23(0).Value = "J1:O510"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args23())
dim args24(0) as new com.sun.star.beans.PropertyValue
args24(0).Name = "Flags"
args24(0).Value = "L"
dispatcher.executeDispatch(document, ".uno:DeleteCell", "", 0, args24())
dim args25(0) as new com.sun.star.beans.PropertyValue
args25(0).Name = "ToPoint"
args25(0).Value = "$A$3"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args25())
end sub
Der Fehler bzw. die Ursache der Meldung, den nach Bestätigung mit Enter werden alle folgenden Befehle fehlrfrei ausgeführt, scheint im Bereich der Sub SiKo_ID_finden zu liegen, kann aber auch danach sein. Für mich seltsam ist auch, dass fast alles, außer dem Auslesen der CSV-Datein (dieser Code wurde von deinem früher geposteten Beispiel abgeleitet, mitgeschrieben, also von OO generiert wurde.
Ergänzung: interessant ist auch, dass diese Meldung intermittierend auftritt, was ja bei Software eher nicht vorkommet.