Cherche Macro à intégrer

[claudius723]

Bonjour tout le monde, j'aurais besoin de quelques conseils, pour intégrer dans une macro existante, des codes pour ne copier que les lignes avec au moins une cellule pleine?
C'est dans calc, pour remplir un tableau avec un formulaire, je joins la macro existante et les conditions que je souhaite:
Si valeur cellule H4, vraie, copier la ligne A4-J4
Si valeur cellule H5, vraie, copier la ligne A5-J5
Pour l'instant la macro me copie tout, avec les lignes vides.
Je ne suis pas encore assez compétent pour écrire ces codes.
Merci à ceux qui voudront bien m'aider
Claudius

Code : Tout sélectionner

sub EnregistrerSaisies
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 = "Nr"
args1(0).Value = 2

dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, args1())

rem ----------------------------------------------------------------------
dim args2(0) as new com.sun.star.beans.PropertyValue
args2(0).Name = "ToPoint"
args2(0).Value = "$A$19:$K$23"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args2())

rem ----------------------------------------------------------------------
dim args3(0) as new com.sun.star.beans.PropertyValue
args3(0).Name = "Flags"
args3(0).Value = "V"

dispatcher.executeDispatch(document, ".uno:InsertCell", "", 0, args3())

rem ----------------------------------------------------------------------
dim args4(0) as new com.sun.star.beans.PropertyValue
args4(0).Name = "ToPoint"
args4(0).Value = "$A$2:$J$5"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args4())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())

rem ----------------------------------------------------------------------
dim args6(0) as new com.sun.star.beans.PropertyValue
args6(0).Name = "ToPoint"
args6(0).Value = "$A$19"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args6())

rem ----------------------------------------------------------------------
dim args7(5) as new com.sun.star.beans.PropertyValue
args7(0).Name = "Flags"
args7(0).Value = "SVDT"
args7(1).Name = "FormulaCommand"
args7(1).Value = 0
args7(2).Name = "SkipEmptyCells"
args7(2).Value = false
args7(3).Name = "Transpose"
args7(3).Value = false
args7(4).Name = "AsLink"
args7(4).Value = false
args7(5).Name = "MoveMode"
args7(5).Value = 4

dispatcher.executeDispatch(document, ".uno:InsertContents", "", 0, args7())

rem ----------------------------------------------------------------------
dim args8(0) as new com.sun.star.beans.PropertyValue
args8(0).Name = "ToPoint"
args8(0).Value = "$A$13:$K$519"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args8())

rem ----------------------------------------------------------------------
rem dispatcher.executeDispatch(document, ".uno:DataSort", "", 0, Array())

rem ----------------------------------------------------------------------
dim args10(9) as new com.sun.star.beans.PropertyValue
args10(0).Name = "ByRows"
args10(0).Value = true
args10(1).Name = "HasHeader"
args10(1).Value = false
args10(2).Name = "CaseSensitive"
args10(2).Value = false
args10(3).Name = "NaturalSort"
args10(3).Value = false
args10(4).Name = "IncludeAttribs"
args10(4).Value = true
args10(5).Name = "UserDefIndex"
args10(5).Value = 0
args10(6).Name = "Col1"
args10(6).Value = 1
args10(7).Name = "Ascending1"
args10(7).Value = true
args10(8).Name = "IncludeComments"
args10(8).Value = false
args10(9).Name = "IncludeImages"
args10(9).Value = true

dispatcher.executeDispatch(document, ".uno:DataSort", "", 0, args10())

rem ----------------------------------------------------------------------
dim args11(0) as new com.sun.star.beans.PropertyValue
args11(0).Name = "Nr"
args11(0).Value = 1

dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, args11())

rem ----------------------------------------------------------------------
dim args12(0) as new com.sun.star.beans.PropertyValue
args12(0).Name = "ToPoint"
args12(0).Value = "$C$8"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args12())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:ClearContents", "", 0, Array())

rem ----------------------------------------------------------------------
dim args14(0) as new com.sun.star.beans.PropertyValue
args14(0).Name = "ToPoint"
args14(0).Value = "$C$10"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args14())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:ClearContents", "", 0, Array())

rem ----------------------------------------------------------------------
dim args16(0) as new com.sun.star.beans.PropertyValue
args16(0).Name = "ToPoint"
args16(0).Value = "$C$12"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args16())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:ClearContents", "", 0, Array())

rem ----------------------------------------------------------------------
dim args18(0) as new com.sun.star.beans.PropertyValue
args18(0).Name = "ToPoint"
args18(0).Value = "$C$13"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args18())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:ClearContents", "", 0, Array())

rem ----------------------------------------------------------------------
dim args20(0) as new com.sun.star.beans.PropertyValue
args20(0).Name = "ToPoint"
args20(0).Value = "$C$15"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args20())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:ClearContents", "", 0, Array())

rem ----------------------------------------------------------------------
dim args22(0) as new com.sun.star.beans.PropertyValue
args22(0).Name = "ToPoint"
args22(0).Value = "$C$17"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args22())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:ClearContents", "", 0, Array())

rem ----------------------------------------------------------------------
dim args24(0) as new com.sun.star.beans.PropertyValue
args24(0).Name = "ToPoint"
args24(0).Value = "$C$18"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args24())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:ClearContents", "", 0, Array())

rem ----------------------------------------------------------------------
dim args26(0) as new com.sun.star.beans.PropertyValue
args26(0).Name = "ToPoint"
args26(0).Value = "$C$21"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args26())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:ClearContents", "", 0, Array())

rem ----------------------------------------------------------------------
dim args28(0) as new com.sun.star.beans.PropertyValue
args28(0).Name = "ToPoint"
args28(0).Value = "$D$21"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args28())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:ClearContents", "", 0, Array())

rem ----------------------------------------------------------------------
dim args30(0) as new com.sun.star.beans.PropertyValue
args30(0).Name = "ToPoint"
args30(0).Value = "$C$24"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args30())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:ClearContents", "", 0, Array())

rem ----------------------------------------------------------------------
dim args32(0) as new com.sun.star.beans.PropertyValue
args32(0).Name = "ToPoint"
args32(0).Value = "$D$24"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args32())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:ClearContents", "", 0, Array())

rem ----------------------------------------------------------------------
dim args34(0) as new com.sun.star.beans.PropertyValue
args34(0).Name = "ToPoint"
args34(0).Value = "$C$25"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args34())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:ClearContents", "", 0, Array())

rem ----------------------------------------------------------------------
dim args36(0) as new com.sun.star.beans.PropertyValue
args36(0).Name = "ToPoint"
args36(0).Value = "$D$25"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args36())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:ClearContents", "", 0, Array())

rem ----------------------------------------------------------------------
dim args38(0) as new com.sun.star.beans.PropertyValue
args38(0).Name = "ToPoint"
args38(0).Value = "$C$26"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args38())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:ClearContents", "", 0, Array())

rem ----------------------------------------------------------------------
dim args40(0) as new com.sun.star.beans.PropertyValue
args40(0).Name = "ToPoint"
args40(0).Value = "$D$26"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args40())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:ClearContents", "", 0, Array())

rem ----------------------------------------------------------------------
dim args42(0) as new com.sun.star.beans.PropertyValue
args42(0).Name = "ToPoint"
args42(0).Value = "$C$8"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args42())

rem ----------------------------------------------------------------------
dim args43(0) as new com.sun.star.beans.PropertyValue
args43(0).Name = "StringName"
args43(0).Value = "=D8+1"

dispatcher.executeDispatch(document, ".uno:EnterString", "", 0, args43())


end sub

[Migguel]

Salut Claudius,

Pour modifier ta macro afin qu'elle ne copie que les lignes avec au moins une cellule pleine, tu devrais intégrer une condition qui vérifie si la cellule spécifique (par exemple H4, H5, etc.) n'est pas vide avant de copier la ligne correspondante. Voici un exemple de code que tu peux ajouter :

Code : Tout sélectionner

dim oSheet as object
oSheet = document.Sheets.getByName("NomDeTaFeuille") 'Remplace par le nom de ta feuille

for i = 4 to 5 ' Ajuste ces valeurs selon les lignes que tu veux vérifier
    if oSheet.getCellRangeByName("H" & i).String <> "" then
        ' Ton code pour copier la ligne, par exemple:
        dim range as object
        range = oSheet.getCellRangeByName("A" & i & ":J" & i)
        ' Ajoute ici le code pour copier 'range' où tu veux
    end if
next i

Ce code parcourt les lignes 4 à 5 et vérifie si la cellule dans la colonne H est non vide. Si c'est le cas, il copie la ligne correspondante. Tu peux ajuster la plage de lignes et les cellules à vérifier selon tes besoins. Ajoute ce code à l'endroit approprié dans ta macro existante.

Espérant que cela t'aide ! N'hésite pas à demander si tu as besoin de plus de précisions.

[claudius723]

Code : Tout sélectionner

rem ----------------------------------------------------------------------
dim args12(0) as new com.sun.star.beans.PropertyValue
args12(0).Name = "ToPoint"
args12(0).Value = "$A$4:$J$4"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args12())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())

rem ----------------------------------------------------------------------
dim args14(0) as new com.sun.star.beans.PropertyValue
args14(0).Name = "ToPoint"
args14(0).Value = "$A$15:$J$15"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args14()

Voila ce que j'ai comme code pour la copie de la ligne A4:J4, la même chose pour la ligne A5:J5
Ce que tu m'as écrit ne veut pas fonctionner, il ne veut pas de "sheets"
Comme je n'y connait pas grand chose, il est possible que je fasse des erreurs!!