Microsoft 365 Automatisation d'un calendrier de paiement client

[laurabachau]

bonjour

J'ai un logiciel en ligne CRM qui ne me permet pas d'avoir un pilotage de la trésorerie à la semaine.
aujourd'hui quand nous faisons nos factures nous les rentrons individuellement à la semaine du paiement, ( dans un tableau, colonne N° de semaine, et ligne "nom du client")
Quand j'ai un client qui doit payer plusieurs factures la même semaine nous les additionnons

Je souhaite construire un fichier plus "automatique"
J'ai automatiser un import via power Quercy, donc aujourd'hui j'ai un fichier qui ressemble à la "PJ" feuille 1

Comment automatiser sur une nouvelle feuille (feuille 2 ) deux éléments:
- le clients s'il n'existe pas s'ajoute en bas de ligne avant le total de la semaine
- le montant de la facture TTC s'ajoute au bon endroit...

Merci par avance

 

[laurabachau]

J'ai crée ce premier code VBA, mais il ne me supprime pas les doublons ou me créer quand même le client s'il exsite déjà

Sub AjouterClientSiNexistePas()
Dim wsFA As Worksheet
Dim wsClients As Worksheet
Dim LastRowFA As Long
Dim Client As String
Dim ClientsRange As Range

' Spécifiez le nom de la feuille "FA" et "Clients"
Set wsFA = ThisWorkbook.Sheets("FA")

' Vérifiez si la feuille "Clients" existe
On Error Resume Next
Set wsClients = ThisWorkbook.Sheets("Clients")
On Error GoTo 0

' Si la feuille "Clients" n'existe pas, creer la feuille
If wsClients Is Nothing Then
Set wsClients = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
wsClients.Name = "Clients"
End If

' Trouvez la dernière ligne utilisée dans la feuille "FA"
LastRowFA = wsFA.Cells(wsFA.Rows.Count, "H").End(xlUp).Row

' Initialisez une plage (range) pour les clients existants
Set ClientsRange = wsClients.Range("A2:A" & wsClients.Cells(wsClients.Rows.Count, "A").End(xlUp).Row)

' Parcourez chaque ligne de la feuille "FA"
For i = 2 To LastRowFA ' Commencez à partir de la deuxième ligne (suppose que la première ligne contient des en-têtes)
Client = wsFA.Cells(i, 8).Value ' Le nom du client est dans la colonne H

' Vérifiez si le client existe déjà dans la feuille "Clients"
If Not IsError(Application.Match(Client, ClientsRange, 0)) Then
' Le client existe, supprimez le doublon
wsClients.Cells(Application.Match(Client, ClientsRange, 0) + 1, 1).EntireRow.Delete
End If

' Ajoutez le client à la feuille "Clients"
LastRowClients = wsClients.Cells(wsClients.Rows.Count, "A").End(xlUp).Row
wsClients.Cells(LastRowClients + 1, 1).Value = Client
Next i

' Nettoyez les objets
Set wsFA = Nothing
Set wsClients = Nothing
Set ClientsRange = Nothing
End Sub

 

[chris]

Bonjour

Il suffit de continuer la requête PowerQuery (il est plus propre d'utiliser des semaines ISO sauf si tu bosses avec la semaine américaine...

La requête complète avec un typage correct

let
    Source = Excel.Workbook(File.Contents("C:\Users\33622\Downloads\Liste des factures (3).xlsx"), null, true),
    Worksheet_Sheet = Source{[Item="Worksheet",Kind="Sheet"]}[Data],
    #"En-têtes promus" = Table.PromoteHeaders(Worksheet_Sheet, [PromoteAllScalars=true]),
    #"Autres colonnes supprimées" = Table.SelectColumns(#"En-têtes promus",{"Client", "Date d'échéance", "Total (TTC)"}),
    #"Type modifié" = Table.TransformColumnTypes(#"Autres colonnes supprimées",{{"Client", type text}, {"Date d'échéance", type date}, {"Total (TTC)", Currency.Type}}),
    #"Personnalisée ajoutée" = Table.AddColumn(#"Type modifié", "Semaine", each Number.ToText(SemaineISO([#"Date d'échéance"]),"Semaine 00")),
    #"Lignes triées" = Table.Sort(#"Personnalisée ajoutée",{{"Date d'échéance", Order.Ascending}, {"Client", Order.Ascending}}),
    #"Colonnes supprimées" = Table.RemoveColumns(#"Lignes triées",{"Date d'échéance"}),
    #"Colonne dynamique" = Table.Pivot(#"Colonnes supprimées", List.Distinct(#"Colonnes supprimées"[Semaine]), "Semaine", "Total (TTC)", List.Sum)
in
    #"Colonne dynamique"

Plus une fonction Semaine ISO

let
    Source = (MaDate as date) =>
let
        Jeudi = Date.AddDays(MaDate, -Date.DayOfWeek(MaDate,1) + 3),
        UnJanvier = #date(Date.Year(Jeudi),1,1),
        EcartJours = Duration.Days(Duration.From(Jeudi - UnJanvier)),
        SemaineISO=  Number.RoundDown(EcartJours/7)+1
in
    SemaineISO
in
    Source

 

[laurabachau]

oulala,

je pense que je vais regarder à tête reposer demain car cela devient du japonais, Je demarre en VBA j'ai BAC -10 sur ça
Merci je te tiens au courant,

 

[Phil69970]

Bonjour à tous

oulala,

je pense que je vais regarder à tête reposer demain car cela devient du japonais, Je demarre en VBA j'ai BAC -10 sur ça
Merci je te tiens au courant,

Cela tombe bien car c’est pas du VBA

 

[bof]

Bonjour,
Déjà moi je me débarrasserai de la ligne de totaux qui pollue ton tableau :
La restructuration que j'avais faite en pièce jointe :
Une page tableau de bord qui totalise les différentes feuilles
Une page de factures
Une page d'encaissements
Une page de décaissements
Mais pour coder ça va être chaud...
Le fichier joint résume l'état de ma réflexion Le bouton dans la colonne Facture ventile les factures dans les semaines respectives sauf la deuxième ou il y a un bogue à corriger en effet la date du premier janvier est envoyé en semainee 52... Mais le problème est qu'il s'agit de la semaine 52 de l'année précédente !
Bon c'est mineur et on doit trouver un correctif sans trop de problème...
Edit : On peut se demander s'il est pertinent de trouver des factures antérieures à la date en cours ?
Les clients en orange dans les factures n'existent pas donc ils sont ajoutés.
Bon on pourrait trier ensuite par ordre alphabétique ou autre, filtrer ou pas... Méfiance avec les filtres car il faudra ôter le filtre pour faire les mises à jour...
C'est juste pour te montrer comment je vois la chose.
Nota les colonnes 2 et 3 de Enc et Dec m'ont servi pour la construction (pas de ligne à 0, et colonne 2 j'ai détaillé un peu par type de Dec pour différencier les Fournisseur et les récurrents ça permet de filtrer et ça aide pour le TBord...
Bon enfin c'est juste pour te montrer que c'est chaud !!!

A+

 

[Staple1600]

Bonsoir le fil

[aparté mignonet]
Personne ne l'a relevée celle-ci ?
Alors je le fais

J'ai automatiser un import via power Quercy

C'est vrai que les versions d'Excel vendues là-bas sont spéciales
avec un vrai potentiel touristique
[/aparté mignonet]

 

[laurabachau]

Bonsoir le fil

[aparté mignonet]
Personne ne l'a relevée celle-ci ?
Alors je le fais

C'est vrai que les versions d'Excel vendues là-bas sont spéciales
avec un vrai potentiel touristique
[/aparté mignonet]

Roh c'est petit ! mais mignon

 

[chris]

Bonjour à tous

Déjà moi je me débarrasserai de la ligne de totaux qui pollue ton tableau

La ligne des totaux s'active ou se désactive à volonté, donc au lieu de la supprimer...

Je vois que ce fil est la suite d'un autre https://excel-downloads.com/threads...-de-tresorerie-annuel.20078554/#post-20606664

On chevauche 2 chevaux à la fois...

 

[laurabachau]

Bonjour à tous,

"Je vois que ce fil est la suite d'un autre https://excel-downloads.com/threads...-de-tresorerie-annuel.20078554/#post-20606664"

Dans ce premier Post j'ai voulu aller vite en postant mon fichier complet et trouver des solutions mais voyant que c'était complique j'ai fais un nouveau post dans le but d'apprendre à coder ou simplifier.

Le post" Automatisation d'un calendrier de paiement client"​

Le but selon moi est d'apprendre à reconstruire le tableau en commençant par la base et apprendre a faire les choses au fur et à mesure.
Le plus compliqué pour moi est de faire du VBA donc avec les infos clients.

 

[laurabachau]

J'ai crée ce premier code VBA, mais il ne me supprime pas les doublons ou me créer quand même le client s'il exsite déjà


Sub AjouterClientSiNexistePas()
Dim wsFA As Worksheet
Dim wsClients As Worksheet
Dim LastRowFA As Long
Dim Client As String
Dim ClientsRange As Range

' Spécifiez le nom de la feuille "FA" et "Clients"
Set wsFA = ThisWorkbook.Sheets("FA")

' Vérifiez si la feuille "Clients" existe
On Error Resume Next
Set wsClients = ThisWorkbook.Sheets("Clients")
On Error GoTo 0

' Si la feuille "Clients" n'existe pas, creer la feuille
If wsClients Is Nothing Then
Set wsClients = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
wsClients.Name = "Clients"
End If

' Trouvez la dernière ligne utilisée dans la feuille "FA"
LastRowFA = wsFA.Cells(wsFA.Rows.Count, "H").End(xlUp).Row

' Initialisez une plage (range) pour les clients existants
Set ClientsRange = wsClients.Range("A2:A" & wsClients.Cells(wsClients.Rows.Count, "A").End(xlUp).Row)

' Parcourez chaque ligne de la feuille "FA"
For i = 2 To LastRowFA ' Commencez à partir de la deuxième ligne (suppose que la première ligne contient des en-têtes)
Client = wsFA.Cells(i, 8).Value ' Le nom du client est dans la colonne H

' Vérifiez si le client existe déjà dans la feuille "Clients"
If Not IsError(Application.Match(Client, ClientsRange, 0)) Then
' Le client existe, supprimez le doublon
wsClients.Cells(Application.Match(Client, ClientsRange, 0) + 1, 1).EntireRow.Delete
End If

' Ajoutez le client à la feuille "Clients"
LastRowClients = wsClients.Cells(wsClients.Rows.Count, "A").End(xlUp).Row
wsClients.Cells(LastRowClients + 1, 1).Value = Client
Next i

' Nettoyez les objets
Set wsFA = Nothing
Set wsClients = Nothing
Set ClientsRange = Nothing
End Sub

J'ai donc repris mon fichier à 0 et fait ceci.

Puis @chris à émis d'autres solutions que je ne comprends pas... J'apprends en prenant des fichiers à droite à gauche et en décodant les codes macros du fichier et/ou grâce à ce site https://www.thespreadsheetguru.com/listobject-tables-vba/

Puis @bof a simplifier le tableau complet du premier Post

et je viens de le tester et je le comprend ! même si je dois progresser encore pour comprendre et connaitre les codes utilisés
mais j'avoue que j'aimerai comprendre ton hypothèse @chris

 

[laurabachau]

Bonjour,
Déjà moi je me débarrasserai de la ligne de totaux qui pollue ton tableau :
La restructuration que j'avais faite en pièce jointe :
Une page tableau de bord qui totalise les différentes feuilles
Une page de factures
Une page d'encaissements
Une page de décaissements
Mais pour coder ça va être chaud...
Le fichier joint résume l'état de ma réflexion Le bouton dans la colonne Facture ventile les factures dans les semaines respectives sauf la deuxième ou il y a un bogue à corriger en effet la date du premier janvier est envoyé en semainee 52... Mais le problème est qu'il s'agit de la semaine 52 de l'année précédente !
Bon c'est mineur et on doit trouver un correctif sans trop de problème...
Edit : On peut se demander s'il est pertinent de trouver des factures antérieures à la date en cours ?
Les clients en orange dans les factures n'existent pas donc ils sont ajoutés.
Bon on pourrait trier ensuite par ordre alphabétique ou autre, filtrer ou pas... Méfiance avec les filtres car il faudra ôter le filtre pour faire les mises à jour...
C'est juste pour te montrer comment je vois la chose.
Nota les colonnes 2 et 3 de Enc et Dec m'ont servi pour la construction (pas de ligne à 0, et colonne 2 j'ai détaillé un peu par type de Dec pour différencier les Fournisseur et les récurrents ça permet de filtrer et ça aide pour le TBord...
Bon enfin c'est juste pour te montrer que c'est chaud !!!

A+

Du coup en reprenant ton fichier, j'ai fais quelque modifs sur le total TTC plutôt que le HT a faire apparaitre dans le tableau encaissement.

Autre complexité, je reçois le paiement 5 jours après la date d'échéance puisque je suis en LCR via les banques donc forcement ils font un peu travailler les €... donc j'ai opté pour ça, mais visiblement non...

Public Function NoSemIso%(d1 As Date)
' Attributed to Daniel Maher
    Dim d2&
    d1 = d1 + 5
    d2 = DateSerial(Year(d1 - Weekday(d1 - 1) + 4), 1, 3)
    NoSemIso = Int((d1 - d2 + Weekday(d2) + 5) / 7)
End Function

 

[bof]

Messie ! C'est une très bonne idée et ça marche très bien.
Mais il faut modifier dans la macro principale le :

ArrT = Range("TFac[TOTAL (TTC)]").Value

En fait j'ai modifié pas mal cette macro pour installer un contrôle de pertinence de date car je suppose que les dates de factures ne peuvent pas être antérieure à la date du jour ?
Je travaille encore un peu sur le classeur et je te le joins.
A part ça si tu veux déléguer des taches, il faut installer un contrôle d'accès avec des mots de passe selon les utilisateurs ?
A+

 

[chris]

RE

mais j'avoue que j'aimerai comprendre ton hypothèse @chris

Tu as une requête PowerQuery dans ton fichier qui récupère ton tableau de l'onglet FA via un import du CRM

On peut directement la présenter semaine par semaine comme l'image que j'ai jointe en la modifiant

Mais, comme ce fil fait suite à d'autres manifestement, avec des demandes diverses et un processus opérationnel non décrit, ce n'est pas sûre que cela corresponde...

 

[laurabachau]

Messie ! C'est une très bonne idée et ça marche très bien.
Mais il faut modifier dans la macro principale le :

ArrT = Range("TFac[TOTAL (TTC)]").Value

En fait j'ai modifié pas mal cette macro pour installer un contrôle de pertinence de date car je suppose que les dates de factures ne peuvent pas être antérieure à la date du jour ?
Je travaille encore un peu sur le classeur et je te le joins.
A part ça si tu veux déléguer des taches, il faut installer un contrôle d'accès avec des mots de passe selon les utilisateurs ?
A+

Hello

les dates de factures ne peuvent pas être antérieure à la date du jour ?

c'est à dire ? La date de facture c'est la date ou le document est émit et envoyé au client et la date d'échéance ce calcule sur le CRM automatiquement en fonction de sa fiche client, généralement 30 jours de plus que la date de facture.

Oui je vais déléguer une partie pour le moment rentrer les factures fournisseurs à la bonne date à la main tant que nous n'utilisons pas le logiciel à 100% sur la partie fournisseurs pour faire comme les clients