Microsoft 365 Incrémentation d'un numéro de devis/facture

[Heavy_B]

Bonjour le forum,

je viens vers vous afin de vous soumettre mon problème.
Je suis occupé à créer un formulaire pour faire des devis/factures et j'aimerais que les numéros s'incrémente via un bouton.
En cherchant un peux j'ai trouvé quelque petite choses mais rien de concluant pour résoudre mon soucis.

Pour planter le décors voici le fonctionnement de mon fichier :
Dans 1 page je remplis les données du devis ou de la facture (y compris les infos clients).
J'ai ensuite la possibilité en appuyant sur un bouton de sauvegarder mon document avec comme forme
soit pour un devis : "c:/MIKE/NomClient PrenomClient/devis_NomClient PrenomClient_n°devis Date_Heure.xlsm"
soit pour une facture "c:/MIKE/NomClient PrenomClient/facture_NomClient PrenomClient_n°facture Date_Heure.xlsm"

donc chaque client à son propre répertoire...
Le but de la "formule magique VBA" serais d'aller voir dans les différents répertoire pour trouver le dernier numéro de devis ou de facture et d'incrémenter le numéro sur ma page principale de 1.
Donc par exemple si mon dernier devis porte le numéro 33 et ma dernière facture le numéro 18, quand j'appuye sur le bouton et suivant ce que j'encôde (défini dans une cellule de la même page) le numéros de devis me donne 34 ou le numéro de facture me donne 19.

J'ai déjà une bonne partie du code mais il me donne une erreur au niveau de la ligne " intMaxInvoice = WorksheetFunction.Max(intMaxInvoice, Mid(strFile, Len(strFile) - 16, 3))"
suivant que je mette -16 ou -18 il me donne "run time error 6 : Overflow" ou "run time error 1004 : Unable to get the max property of the WorksheetFunction class"

Je tiens a préciser que je n'ai pas fait ce code mais que je l'ai récupéré et adapté. Je ne suis pas un utilisateur expérimenté en VBA.

Voici le code :

Sub GetNextInvoiceNumber()

    Dim wb As Workbook
    Dim ws As Worksheet
    Dim rng As Range
    Dim strPath As String
    Dim strFolder As String
    Dim strClient As String
    Dim strFile As String
    Dim intMaxInvoice As Integer
    Dim strInvoice As String
    Dim intYear As Integer
    Dim strYear As String

    ' Définir le chemin d'accès
    strPath = "C:\MIKE\"

    ' Choisir le workbook et le worksheet approprié
    Set wb = ThisWorkbook
    Set ws = wb.Sheets("Maison type") ' Remplacer par le nom de votre feuille

    ' Récupérer le nom et le prénom du client depuis votre feuille de calcul
    strInvoice = ws.Range("M1").Value ' Remplacer A2 par la cellule contenant le type de document (devis ou facture)

    ' Récupérer l'année en cours
    intYear = Year(Date)
    strYear = CStr(intYear)

    ' Nouveau code pour parcourir tous les sous-dossiers
    Dim FSO As New Scripting.FileSystemObject
    Dim Folder As Scripting.Folder
    Dim Subfolder As Scripting.Folder

    ' Choisir le dossier parent
    Set Folder = FSO.GetFolder(strPath)

    ' Parcourir tous les sous-dossiers
    For Each Subfolder In Folder.SubFolders
        strFile = Dir(Subfolder.Path & "\" & strInvoice & "_" & "*" & "_*")
        ' Parcourir tous les fichiers dans le sous-dossier
        Do While strFile <> ""
            intMaxInvoice = WorksheetFunction.Max(intMaxInvoice, Mid(strFile, Len(strFile) - 16, 3))
            strFile = Dir
        Loop
    Next Subfolder

    ws.Range("C3").Value = strYear & Format(intMaxInvoice + 1, "000")

End Sub

 

[sylvanu]

Bonjour Heavy,
sans fichier test, impossible de tester. Alors au pif ...
Peut être que "Mid(strFile, Len(strFile) - 16" est considérée comme une chaine de caractère, donc erreur.
Tentez de la transformer en nombre avec :

intMaxInvoice = WorksheetFunction.Max(intMaxInvoice, Val(Mid(strFile, Len(strFile) - 16)), 3)

 

[Heavy_B]

Bonjour Sylvanu,

Merci d'avoir pris le temps d'y regarder.
Plus d'erreur mais suivant que je place - 16 ou - 18 la valeur change en 2023006 ou 2023015.
Je place mon fichier ici afin que vous puissiez peut-être y voir plus clair...

 

[sylvanu]

Bonjour,
Il me manque quelques Ref pour faire "runner" votre PJ, en particulier Microsoft Outlook Sharepoint Social Provider.
Ceci dit, quelque chose me chagrine. Vous déclarez "intMaxInvoice As Integer" soit un nombre compris entre -32768 et +32767. En dehors de cette plage vous aurez une erreur.
Et vous trouvez intMaxInvoice=2023006, je ne comprends pas pourquoi vous n'avez pas d'erreur sur ce point.
( un essai possible est de supprimer "as integer" pour voir le résultat )
D'autre part, on ne peut guère analyser puisque le résultat va dépendre du nom de vos fichiers.
pourriez vous nous donner quelques noms de fichier pour approfondir ?

 

[Heavy_B]

j'ai essayé de supprimer le "as integer" mais pas de changement
voici le zip de mes essais, a mettre dans un répertoire "C:\MIKE"

 

[sylvanu]

Il eût été plus simple d'expliquer ce que vous vouliez faire.

En PJ un décorticage du code pour les valeurs 16 et 18.

Avec 16	devis_NomClient PrénomClient_2023001 11-07-23_10-00.xlsm	On extrait le mois
Avec 18	devis_NomClient PrénomClient_2023001 11-07-23_10-00.xlsm	On extrait le jour

Donc tout dépend de ce que vous attendez, soit 2023004 si on considère le mois, soit 2023012 si on considère le jour.

NB : Le pb du "as integer" est une erreur de ma part. J'avais lu que 2023006 était le résultat donc j'ai compris de intMaxInvoice, ça ne pouvait pas marcher. Mais en fait c'est le résultat de la concaténation, donc rien à voir.

 

[Heavy_B]

Merci pour la réponce mais le numéro à incrémenter est bien le "2023001" et non extraire le mois ou le jour.
Donc si je fait tourner la macro le numéro dois s'incrémenter de 1 par rapport au dernier "invoice" trouvé
le Invoice se compose de l'année en 4 chiffre et d'un numéro d'ordre en 3 chiffres
Donc si je fait 5 devis sur la journée et que le dernier trouvé dans le répertoire/sous répertoire "c:/Mike" et le devis 2023012, les devis suivant serons alors les numéros 2023013, 2023014, ... jusque 2023017.
Idem pour les numéros de facture mais qui sont indépendant (pour la partie numéro d'ordre), donc le même jour que mes devis je fait 2 factures dont la dernière trouvée est la 2023005, elles porterons les numéros 2023006 et 2023007.
Le premier devis ou facture que je ferai en 2024 sera du format 2024001.
Désolé de ne pas avoir été assez clair sur ce point dès le départ, mais comme j'ai exactement ce que je voudrais dans la tête j'avais crus être clair et complet dans mon explication...

 

[sylvanu]

Alors, désolé, je ne comprends rien à votre code. A t-il déjà donné de bons résultats ?

1- Je ne vois pas où vous vérifiez qu'on est en début d'année et qu'il faut recommencer à 001.
2- "Mid" compte à partir de la gauche, donc suivant "NomClient PrénomClient" la position du début du N° changera. Qu'on utilise 16 ou 18 dans le Mid, je ne vois pas comment on extrait le N° de facture.
J'aurais plutôt pensé à passer par un Split qui donne ainsi l'année et le numéro. Voir PJ.

T = Split(strFile, "_")         ' Séparateur : "_"
Année = Val(Left(T(2), 4))      ' Extraction de l'année
 Numéro = Val(Mid(T(2), 5, 3))   ' Extraction du N°

 

[Heavy_B]

Non, il n'as jamais fonctionné, c'est pour cela que je suis venu chercher de l'aide. Sorry mais je suis débutant et j'essaye de trouver mon chemin.

Si je comprend bien votre code cela se raporte a la colonne A mais ces nom de fichier sont dans un répertoire... je vais essayer de regarder pour modifier mon code existant.

Merci

 

[sylvanu]

Si je comprend bien votre code cela se raporte a la colonne A mais ces nom de fichier sont dans un répertoire...

Ca n'a pas d'importance. En fait on traite "strfile", d'où qu'il vienne.
Au pif sans avoir essayé :

    ' Parcourir tous les sous-dossiers
    For Each Subfolder In Folder.SubFolders
        strFile = Dir(Subfolder.Path & "\" & strInvoice & "_" & "*" & "_*")
        ' Parcourir tous les fichiers dans le sous-dossier
        Do While strFile <> ""
            T = Split(strFile, "_")         ' Séparateur : "_"
            Année = Val(Left(T(2), 4))      ' Extraction de l'année
            Numéro = Val(Mid(T(2), 5, 3))   ' Extraction du N°
            intMaxInvoice = WorksheetFunction.Max(intMaxInvoice, Numéro)
            strFile = Dir
        Loop
    Next Subfolder

Mais là encore vous risquez un souci.
Si dans votre directory vous avez :

devis_NomClient PrénomClient_2022048 11-07-23_10-02.xlsm

devis_NomClient PrénomClient_2023001 11-07-23_10-00.xlsm

devis_NomClient PrénomClient_2023002 11-07-23_10-01.xlsm

devis_NomClient PrénomClient_2023003 11-07-23_10-02.xlsm

Alors le max vous donnera 048, ce qui est faux car c'est du 2022. Il devrait trouver 003, le dernier de 2023.
Je pense qu'il vous faut extraire les 7 chiffres 2023003 car alors 2022048 sera inférieur à 2023003, donc ce sera bon. Mais là c'est à vous de voir si le problème est réel où si je n'ai rien compris. ( par ex si le dossier ne contient qu'une seule année )

 

[dysorthographie]

Bonjour,
Je ne comprends pas l'intérêt de se torturer le cerveau avec des recherches alembiqué !

Dans ton classeur applicatif tu ajoutes un onglet Nº incrément dans lequel on trouve le nom des clients et le dernier nº de devis.

Pour les factures, je te suggère d'ajouter dans le même onglet un colonne Nº Facture.

Il sera judicieux de créé manuellement le nom des clients ainsi que leurs derniers Nº de devis et Nº de facture pour initialiser cet onglet de configuration

Pour ma part,dans mes applications,les Nº de devis ont toujours été plus simple. La date du jour au format nº série avec un endescor puit le énième devis de la journée et peut importe le client.

 

[mapomme]

Le Nº de facture n'étant qu'un devis accepté tu peux le transformer Nº de facture, sauf si tu fait des factures sans devis.

Bonjour @dysorthographie ,

Le numéro de facture n'est pas un simple numéro. Les numéros de factures d'une année doivent se suivre. Si un numéro de facture a été attribué, il ne doit pas être supprimé. Il ne doit pas y avoir de "trous" dans la numérotation. On peux faire des tas de devis qui ne seront pas acceptés par les clients. Je ne pense donc pas que le n° de devis puissent se transformer en numéro de facture.

 

[dysorthographie]

je vois pas le rapport, je ne me rappel pas avoir parlé de supprimer quoi que ce soit même dans la première version de mon poste!

Public Function AutoIncrement(Cient As String, Fact As Boolean) As String
Dim L As Integer
With ThisWorkbook.Sheets("N° Incrémental")
    L = SerchXls(.Range("A:A"), .Range("A1"), Cient, True)
    If L = 0 Then L = .Cells(.Cells.Rows.Count, "A").End(xlUp).Row + 1
 
    If .Cells(L, "D") <> Year(Date) Then
        .Cells(L, "B") = 0: .Cells(L, "C") = 0
        .Cells(L, "D") = Year(Date)
    End If
    .Cells(L, "A") = Cient
   If Fact Then
        .Cells(L, "C") = .Cells(L, "C") + 1
      AutoIncrement = Format(.Cells(L, "C"), "000")
    Else
        .Cells(L, "B") = .Cells(L, "B") + 1
        AutoIncrement = Format(.Cells(L, "B"), "000")
    End If
End With
End Function
'devis_NomClient PrénomClient_2022048 11-07-23_10-02.xlsm
Function Numerot(NomClient As String, PrenomClient As String, Fact As Boolean)
Const Repertoire As String = "c:\MIKE\[NomClient] [PrenomClient]\"
Const Devevis As String = "devis_[NomClient] [PrenomClient]_[n°devis] [Date]_[Heure].xlsm"
Const Facture As String = "facture_[NomClient] [PrenomClient]_[n°devis] [Date]_[Heure].xlsm"
Dim Rep As String
Rep = Replace(Replace(Repertoire, "[NomClient]", NomClient), "[PrenomClient]", PrenomClient)
Creer_Repertoires Rep
        Numerot = Rep & Replace(Replace(Replace(Replace(Replace(IIf(Fact, Facture, Devevis), "[NomClient]", NomClient), "[PrenomClient]", PrenomClient), "[n°devis]", AutoIncrement(NomClient & " " & PrenomClient, Fact)), "[Date]", Format(Date, "dd-mm-yy")), "[Heure]", Format(Now, "hh-mm"))
End Function

Sub test()
Debug.Print Numerot("dysorthographie", "Robert", False), Numerot("dysorthographie", "Robert", True)
Debug.Print Numerot("toto", "Robert", False), Numerot("toto", "Robert", True)
End Sub

 

[mapomme]

je vois pas le rapport, je ne me rappel pas avoir parlé de supprimer quoi que ce soit même dans la première version de mon poste!

J'avais compris que tu transformais le n° de devis en Facture. Tout ce que je disais, c'est qu'un devis n'étant pas forcément accepté, transformer un numéro de devis en facture présenterait des trous. Mais à priori tu gères d'une part les devis et d'autre part les numéros de factures.

Ce que je n'ai pas bien saisi (mais je me trompe peut-être) : j'ai l'impression que ta numérotation des factures dépend des clients ce qui, je pense, n'est pas autorisé par la comptabilité mais je n'ai pas approfondi ta méthode.

 

[dysorthographie]

Bonjour,
En contabilite il est possible d'utiliser la date en Nº de série par exemple.
C'est exactement pour cela que Microsoft prévoit un format Nº série pour les dates. Ce format sous entend des trous car on ne fait pas forcément de devis tous les jours. Cette méthode sous entend également un indexage des devis pour une même journée ce qui du point de vue contable est légal.

La numérotation des factures et des devis doit être univoque et pas forcément ce suivre. La seule obligation étant que le Nº soit genré de façon automatique par un algorithme et pas par saisie manuel.

Avec un exacompta on pouvait voir si un page était arraché mais le nº était écrit à la main

Le devis "Dev0010" peut sans problème créer la "Fact0010" sans pour autant qu'il existe de facture "Fact0001" dès l'instant que le nº est univoque et gênéré par algorithme informatique !

Un devis ne peut pas être supprimé, une facture s'annule par la création d'un avoir"Avoir0010" par exemple

Personnellement je feraiune numérotation part type de documents, mais le poste #1 ma fait penser que le demandeur souhaitait par client !

Il me semble que quelque soit la forme de numérotation il est préférable que ce soit centralisée dans un endroit connu plutôt que d'autopsier les fichiers existant dans le ou les répertoire client !