Excel et macro en Python

Version imprimable

25/11/2022, 08h48
FadeToBlack

Excel et macro en Python

Bonjour à tous,

Plusieurs utilisateurs de mon entreprise me demandent de leur créer des macros pour traiter des éléments sur leurs fichiers Excel.
N'ayant pas fait de VBA depuis un siècle, je me sens moyen de me replonger dedans, surtout que j'ai appris qu'il était possible de faire des macros en python.

En pratique, j'ai mon fichier excel et mon script python. Je traite les éléments avec Pandas (pour ceux qui connaissent). Cette partie là n'a pas vraiment d'importance.
Ce que j'aimerai savoir c'est comment, au clic dans le fichier excel, déclencher le code python ?
Bien évidemment les utilisateurs n'ont pas Python installé sur leurs ordinateurs

Du coup faut-il que je compile mon script Python pour avoir un .exe qui sera appelé dans Excel ?

J'avoue que je suis un peu perdu.

Auriez vous des pistes afin que je puisse venir en aide à mes collègues.
JE vous remercie par avance.

Bonne journée à tous
25/11/2022, 09h09
jurassic pork

Hello,
Avec OpenOffice et LibreOffice on peut utiliser des macros écrites en python. Reste à savoir si les documents Excel de ton entreprise s'ouvre correctement dans LibreOffice ou OpenOffice (il y a parfois des problèmes de mise en page). Si tu ne peux pas installer OpenOffice ou LibreOffice dans ton entreprise tu peux les installer en version portable (dans un répertoire où tu as tous les droits).
Pour excel il y a aussi pyxll et xlwings (voir ici une comparaison des deux)
Ami calmant, J.P
25/11/2022, 11h17
FadeToBlack

Bonjour Jurassic Pork,

Nous n'avons pas OPenOffice et il ne m'est pas permis d'installer des softs comme cela même en portable. Il me semblait que Office permettait des macro Python. Ce n'est pas le cas ?

Du coup en attendant, je reprends un peu VBA mais je me heurte à un remplacemement de données dans une cellule.

je voudrait remplacer un mot par un texte de plus de 255 charactères. En général, plutôt quelques chose du genre 1000.
et du coup, j'ai une erreur d'exécution 13. JE n'arrive pas à trouver la solution, qui en Python serait réglable en 10 s par un longText...

Merci d'avance
25/11/2022, 11h52
jurassic pork

Peux-tu nous montrer le code qui déclenche une erreur 13 ?

Voici le code qui pose problème :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
 
Sub rename_data()
Dim line As Integer
Dim col As Integer
Dim last_line As Integer
Dim last_col As Integer
Dim val As String
 
Dim oldVal As Text
Dim newVal As Text
 
 
line = 2: col = 1
 
last_line = Cells.SpecialCells(xlCellTypeLastCell).Row
last_col = Cells.SpecialCells(xlCellTypeLastCell).Column
 
For line = 2 To last_line
    oldVal = Sheets("Rename").Cells(line, 1).Value
    newVal = Sheets("Rename").Cells(line, 2).Value
    Sheets("expguer").UsedRange.Replace What:=oldVal, replacement:=newVal
    MsgBox CStr(Len(newVal))
 
Next line
 
End Sub

Cela bloque à cette ligne là
Sheets("expguer").UsedRange.Replace What:=oldVal, replacement:=newVal

La fonction Range.Replace est limitée en nombre de caractères contrairement à la fonction VBA Replace. Mais dans ce cas il faut faire la recherche . Microsoft propose dans ses exemples :

Citation:

Cet exemple montre comment rechercher toutes les cellules de la plage A1:A500 dans la première feuille de calcul contenant la sous-chaîne « abc », et comment remplacer « abc » par « xyz ».
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 Sub FindString() Dim c As Range Dim firstAddress As String With Worksheets(1).Range("A1:A500") Set c = .Find("abc", LookIn:=xlValues) If Not c Is Nothing Then firstAddress = c.Address Do c.Value = Replace(c.Value, "abc", "xyz") Set c = .FindNext(c) Loop While Not c Is Nothing End If End With End Sub
Ami calmant, J.P

25/11/2022, 13h32
FadeToBlack

Je te remercie.

Cela fait trop longtemps que je n'ai pas utilisé VBA......

JE vais quand même étudier la portance et l'utilisation de Python dans les macros.

Bon après midi à toi.

PS : Il n'y a rien de mieux que le ZX81, hormis peut être le TO7 :lol:

voici une fonction qui permet de faire l'opération avec en paramètre : la plage à balayer, l'ancienne valeur , la nouvelle valeur :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
Sub RemplacerChaine(Plage, oldVal, NewVal)
Dim c As Range
Dim firstAddress As String
        Set c = Plage.Find(oldVal, LookIn:=xlValues)
        If Not c Is Nothing Then
            firstAddress = c.Address
            Do
                c.Value = Replace(c.Value, oldVal, NewVal)
                Set c = Plage.FindNext(c)
            Loop While Not c Is Nothing
        End If
End Sub

Code de test qui marche chez moi en Excel 2019 :

Code:

1
2
3
4
5
6
7
8
Sub TestBigChaine()
Dim BigChaine As String
BigChaine = "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa" & _
                 "bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb" & _
                 "cccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccc" & _
                 "ddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd"
RemplacerChaine ActiveSheet.UsedRange, "MotARemplacer", BigChaine
End Sub

Bonjour Jurassic Pork,

Désolé de ne pas avoir répondu plus tôt, mais j'étais pris par autrechose.
J'ai adapté ta première solution à mon cas d'étude. Cela donne ceci

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
 
Sub correction_nom()
Dim line As Integer
Dim col As Integer
Dim last_line As Integer
Dim last_col As Integer
Dim val As String
 
Dim oldVal As String
Dim NewVal As String
 
 
line = 2: col = 1
 
last_line = Cells.SpecialCells(xlCellTypeLastCell).Row
last_col = Cells.SpecialCells(xlCellTypeLastCell).Column
 
For line = 2 To last_line
    oldVal = Sheets("Renommer").Cells(line, 1).Value
    NewVal = Sheets("Renommer").Cells(line, 2).Value
    FindMyValue oldVal, NewVal
'    Sheets("expguer").Replace What:=oldVal, replacement:=NewVal
'    MsgBox CStr(Len(NewVal))
 '  replacement oldVal, newVal
Next line
 
End Sub
 
 
Sub FindMyValue(oldV, NewV)
 
    Dim c As Range
    Dim firstAddress As String
 
    With Worksheets(1).Range("B2", Range("B2").End(xlDown))
        Set c = .Find(oldV, LookIn:=xlValues)
        If Not c Is Nothing Then
            firstAddress = c.Address
            Do
                c.Value = NewV
                Set c = .FindNext(c)
            Loop While Not c Is Nothing
        End If
    End With
 
End Sub

Cela fonctionne parfaitement, néanmoins, les remplacements s'effectue sur toute la case et non sur la chaine de caractères.

J'ai par exemple en données de base trois cellules :
Paul Auchon
Pete Agore
Paul Auchon ; Pete Agore

Pour les remplacements j'ai :
Paul Auchon --> P. AUCHON
Pete Agore --> P. AGORE

Je m'attends donc à avoir :
P. AUCHON
P. AGORE
P. AUCHON ; P. AGORE

or j'obtiens :
P. AUCHON
P. AGORE
P. AUCHON

Bizarre

28/11/2022, 15h03
FadeToBlack
Bon après noeuds à ce qu'il me reste de cheveux, j'ai trouvé la solution. Purée c'est loin VBA pour moi.......

Dans la fonction FindMyValue(), J'ai remplacé :
Code:

1 2 c.Value = NewV
Par

Code:

c.Value = Replace(c.Value, oldV, NewV)

Est-ce que tu peux me confiùer, la bonne logique ?

Merci d'avance
28/11/2022, 15h09
jurassic pork

Hello,
en utilisant le Replace comme dans mon code 2 , il n' y a pas ce problème.
c.Value c'est la valeur de toute la cellule c.
Ami calmant, J.P
28/11/2022, 15h21
FadeToBlack

Oui, effectivement, j'ai repris en détails ton code et effectivement c'est ce qui était mentionné.

A faire 10 trucs en même temps on s'emmêle les pinceaux....

Quoiqu'il en soit je te remercie pour ton aide et tes indications.

Bonne fin de journée

JE mets le topic en "Résolu"
29/11/2022, 09h03
FadeToBlack

Bonjour Jurassic Pork,

JE réouvre le post car je me heurte à un nouveau problème :
J'ai fait des tests hier soir pour voir si la macro fonctionnait convenablement et ne me générait pas de problèmes.
Et ben j'ai bien fait :

Je suis partie d'un liste de 1554 noms de personnes, dont certains se répètent plusieurs fois et certaines cellules en contiennent plusieurs à côté.

Sur la seconde feuille, j'ai une liste de 600 noms unique avec dans la colonne d'à côté la modification à effectuer.
Si je lance la macro, Excel se bloque, prend 50 % de mon CPU et reste figé. Je suis du coup obligé de tuer le processus.

Je peux comprendre que pour une telle liste, les modifications prennent entre 5 et 10 minutes (ce qui me parait long quand même), mais pas figé l'appli.
Il doit y avoir des boucles qui tournent dans le vide... Mais je ne comprends pas ce qui les provoque.

Bonne journée à toi
29/11/2022, 11h05
jurassic pork

Hello,
peux-tu nous mettre ton code actuel ?
As-tu aussi surveiller la mémoire utilisée ? Est-ce que ça bloque avec 200, 300 , 400 , 500 noms ?
Ami calmant, J.P

voici le code du mon fichier Modif_nom.xlsm

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
 
Sub correction_nom()
Dim line As Integer
Dim col As Integer
Dim last_line As Integer
Dim last_col As Integer
Dim val As String
 
Dim oldVal As String
Dim NewVal As String
 
 
line = 2: col = 1
 
last_line = Range("B2", Range("B2").End(xlDown)).Count
last_col = Cells.SpecialCells(xlCellTypeLastCell).Column
 
 
For line = 2 To 5
    oldVal = Sheets("Renommer").Cells(line, 1).Value
    NewVal = Sheets("Renommer").Cells(line, 2).Value
    FindMyValue oldVal, NewVal
 
Next line
 
End Sub
 
 
Sub FindMyValue(oldV, NewV)
 
    Dim c As Range
    Dim firstAddress As String
 
    With Worksheets(1).Range("B2", Range("B2").End(xlDown))
        Set c = .Find(oldV, LookIn:=xlValues, SearchOrder:=xlByColumns)
        If Not c Is Nothing Then
            firstAddress = c.Address
            Do
                c.Value = Replace(c.Value, oldV, NewV)
 
                Set c = .FindNext(c)
            Loop While Not c Is Nothing
        End If
    End With
 
End Sub

En fait, j'ai essayé en mettant dans ma boucle principale :

Code:

1
2
3
4
5
For line = 2 To 5
    oldVal = Sheets("Renommer").Cells(line, 1).Value
    NewVal = Sheets("Renommer").Cells(line, 2).Value
    FindMyValue oldVal, NewVal
Next line

Donc en théorie, je ne fais la recherche que sur 3 noms cela bloque là aussi.

Merci de ton aide

29/11/2022, 13h37
jurassic pork

oops ! :oops: c'est le Loop While Not c Is Nothing qui n'est pas bon : avec ceci on ne sort pas de la boucle car on revient refaire la recherche au début de la plage sur le dernier find next et ainsi de suite. Il faut utiliser :

Code:

Loop While c.Address <> firstAddress

je me demandais pourquoi on n'utilisait pas firstAddress :mouarf:
29/11/2022, 13h49
FadeToBlack

Magnifique, extraordinaire, géantissime.

Cela fonctionne du feu de dieu. Du coup, j'ai essayé sur un fichier avec un peu plus de 10 000 ligne avec une liste de base de 6000 nom à modifier.

Temps d'exécution : 14 secondes....

Une merveille....

Un grand merci à toi.
30/11/2022, 12h45
FadeToBlack

ET non, cela ne fonctionne plus :

Si dans mes listes je n'ai que de l'alphabétique, cela fonctionne à merveille. Mais dès qu'il y un caractère spécial (autre qu'un guillemet), cela plante. Idem pour la présence d'un chiffre.

Par exemple si je veux transformer "Jean-Marc" en "toto", ou "Henri 4" en toto, cela fait bien la correction, mais au moment de passer à la suite, il bloque.

J'obtiens l'erreur d'exécution 91 Varaible objet ou variable de bloc With non définie. Tout cela au niveau de la ligne

Code:

Loop While c.Address <> firestAddress

En utilisant la fenêtre de variable locales, je remarque bien non pas c.value, mais c.value2 qui est effectivement en string. Donc cela ne devrait pas poser de problème.

Encore Bizarre
30/11/2022, 13h14
jurassic pork

Hello,
firestAddress ???? Quel est le code complet ?
Ami calmant, J.P

Hello,

Voici le code

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
Sub correction_nom()
Dim line As Integer
Dim col As Integer
Dim last_line As Integer
Dim last_col As Integer
Dim val As String
 
Dim oldVal As String
Dim NewVal As String
 
 
line = 2: col = 1
'Worksheets("Renommer").Activate
last_line = Sheets("renommer").Cells.SpecialCells(xlCellTypeLastCell).Row
'last_line = Sheets("renommer").Range("B2", Range("B2").End(xlDown)).Count
last_col = Sheets("renommer").Cells.SpecialCells(xlCellTypeLastCell).Column
 
 
For line = 2 To last_line
    oldVal = Sheets("Renommer").Cells(line, 1).Value
    NewVal = Sheets("Renommer").Cells(line, 2).Value
    FindMyValue oldVal, NewVal
'    RemplacerChaine ActiveSheet.UsedRange, oldVal, NewVal
 
Next line
 
MsgBox "Traitement terminé !!"
 
End Sub
 
 
Sub FindMyValue(oldV, NewV)
 
    Dim c As Range
    Dim firstAddress As String
 
 
    With Worksheets(1).Range("B2", Range("B2").End(xlDown))
        Set c = .Find(oldV, LookIn:=xlValues, SearchOrder:=xlByColumns, MatchCase:=False)
        If Not c Is Nothing Then
            firstAddress = c.Address
            Do
'                c.Value = c.Value.ToString
 
                c.Value = Replace(c.Value, oldV, NewV, , , vbTextCompare)
 
                Set c = .FindNext(c)
            Loop While c.Address <> firstAddress
        End If
    End With
 
End Sub

Et encore une fois merci pour ton aide

30/11/2022, 14h54
jurassic pork
bon cette fois-ci au lieu de reboucler on a un objet vide quand il n'y a plus rien à remplacer. En mettant ce code qui fait sortir de la boucle si l'objet c est vide cela devrait fonctionner :
Code:

1 2 3 Set c = .FindNext(c) If c Is Nothing Then Exit Do Loop While c.Address <> firstAddress
30/11/2022, 16h16
FadeToBlack

Je comprends la logique, mais pourquoi dans le cas de caractères numériques ou spéciaux, la c.value passe à Nothing en provoquant une erreur. ?
Alors que dans la cas "classique", il boucle et si c.find(next) renvoie null, c.value passe aussi en nothing mais sans provoquer d'erreur....

Je ne comprends pas
30/11/2022, 16h22
jurassic pork

je ne suis pas un spécialiste mais si tu regardes les exemples Microsoft sur le findNext ici, il y a bien un double test sur les deux derniers exemples.

Hello,
Remarques concernant l'utilisation de Find et de FindNext.
1 - Attention si on change des options dans les boîtes de dialogue Rechercher, Remplacer d'Excel, si on ne change pas ces options dans le code VBA, ce sont les options des boîtes de dialogue qui seront prises en compte.
2 - Si l'on remplace ce que l'on recherche, normalement on ne boucle pas car la valeur que l'on recherche n'est plus présente et donc on a normalement un Nothing sur le findNext qui devait nous ramener au départ. Si cela n'est pas le cas c'est que le remplacement de la première valeur trouvée n'a pas fonctionné ou que l'on remplace par la même valeur.
3 - Attention à l'option LookAt du Find, si l'on fait une recherche sur une partie du contenu de la cellule, il faut mettre l'option à xlPart.

Dans le cas de FadeToBlack, il faut donc s'interroger de savoir si tous les remplacements ont eu lieu car normalement si c'est le cas on ne doit pas boucler et donc toujours sortir sur un c à Nothing. Pour piéger le cas ajouter :
Code:

1 2 3 4 5 6 If c Is Nothing Then Debug.Print "c = Nothing" Exit Do ElseIf c.Address = firstAddress Then Debug.Print "Bouclage : ", firstAddress, c.Value, oldV, NewV End If
Dans la fenêtre d'exécution de VBA s'affiche alors la première adresse trouvée avec la dernière valeur de la cellule, la valeur à remplacer, la valeur de remplacement.

Ami calmant, J.P