Discussion :

[alpijo]

Bonjour à tous,

Je souhaiterais incrémenter un numéro de facture automatiquement et que ce numéro soit composer de l'année, du mois et d'un numéro qui sera réinitialiser à 0 tous les mois (exemple :201812-001 etc..)

J'avais créer un code vba me permettant d'enregistrer ce numéro dans un document Text (Compteur) et que l'on de l'ouverture la prochaine fois du fichier excel que le numéro sera augmenter de 1.

Mon code marchais bien mais je n'arrive pas à le paramétrer pour ma nouvelle version.

Voici mon code d'enregistrement du nouveau numéro dans le fichier compteur.txt :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
Sub numerotation()
 
Dim chemin_fichier As String: Dim Numero As Integer
Dim objet_fichier: Dim le_fichier
 
chemin_fichier = ThisWorkbook.Path & "\Compteur.txt"
 
Numero = Range("D3").Value
 
Set objet_fichier = CreateObject("scripting.filesystemobject")
Set le_fichier = objet_fichier.getfile(chemin_fichier)
 
le_fichier.Attributes = 0
 
Open chemin_fichier For Output As #1
Print #1, Replace(Numero, "", "") + 1
Close #1
 
le_fichier.Attributes = 3
 
End Sub

Voici mon code pour la récupération du numéro qui est dans le fichier compteur:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
Private Sub worksheet_activate()
Dim chemin_fichier As String: Dim Numero As String
 
chemin_fichier = ThisWorkbook.Path & "\Compteur.txt"
 
Open chemin_fichier For Input As #1
Line Input #1, Numero
Close #1
 
Range("D3").Value = "" & Numero
End Sub

Ce code marche très bien si je ne souhaite que le numéro. Or maintenant, je voudrais qu'il y ai avant le numéro l'année et le mois que l'on va appeler "Date" et que le numéro se situant après la date se réinitialise à 0 tous les mois

Exemple : J'ai le numéro 201812-013 à l'ouverture de mon fichier Excel le 28/12/2018 et je voudrais que lorsque j'ai cliquer sur le bouton archiver qui correspond à mon 1er code (Sub Numérotation) que cela enregistre dans le fichier compteur 014.
De plus, je voudrais que la prochaine fois que j'ouvre le fichier que cela m'affiche soit 201812-014 (ce qui correspond au fichier compteur) si je suis toujours en Décembre 2018 soit 201901-001 si j'ai attaqué un nouveau mois => qui sera représenté dans mon code n°2

Je vous en remercie infiniment pour toute l'aide

[Philippe Tulliez]

Bonjour,

Je souhaiterais incrémenter un numéro de facture automatiquement et que ce numéro soit composer de l'année, du mois et d'un numéro qui sera réinitialiser à 0 tous les mois (exemple :201812-001 etc..)

La solution à ton problème est en fait très simple.
Il te faut juste créer une fonction personnalisée qui sera un compteur répondant à plusieurs critères.

Quant au format, il est parfaitement indépendant du compteur et peut être résolu à l'aide de la fonction Format de VBA ou en plaçant une formule générant le format dans une cellule d'excel se trouvant dans une feuille paramètre et que le code VBA ira lire (c'est la solution que je préconise)

J'ai développé, une fonction que j'utilise dans Excel, Word et Access et qui incrémente un numéro en fonction

• du dernier numéro utilisé
• d'une périodicité pour la réinitialisation du compteur
• la date de la dernière utilisation de ce numéro

Voir ce billet Excel – Incrémenter un numéro de facture, devis, à l’aide d’une fonction personnalisée. Il y a un fichier exemple en téléchargement

Exemple du formatage du numéro en concaténant le numéro du document renvoyé par la fonction Counter

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=TEXTE(AUJOURDHUI();"AA-MM/") & Counter(C5;C6;C7;C8)

Renverra pour la date d'aujourd'hui 18-12/16
et si l'on souhaite obtenir le numéro avec 5 chiffres fixes soit 18-12/00016

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=TEXTE(AUJOURDHUI();"AA-MM/") & TEXTE(Counter(C5;C6;C7;C8);"00000")

Illustration de l'utilisation de la fonction Counter avec affichage du résultat renvoyé



Il est évident qu'en lieu et place des références C5, C6, C7 et C8 on utilisera des cellules nommées

[unparia]

Bonjour
1) pourquoi cette double démarche (utilisation de ce poids ralentisseur qu'est FSO et de l'instruction Open) ? -->>
Ouvre ton aide VBA, les les rubriques GetAttr, instruction et SetAttr, instruction

2) Pourquoi mémoriser dans un fichier texte ce que tu peux mémoriser dans une cellule de ton choix ?
3) Quelle est donc ta question essentielle ? Serait-ce finalement (une fois isolée) :
- comment concaténer un millésime, un n° de mois, un tiret et un chiffre que l'on incrémente et que l'on remet à 1 lorsque le N° du mois en cours est autre que celui utilisé précédemment ?

Par ailleurs :
1) qu'est selon toi le résultat d'un remplacement de "" par "" ?
2) qu'est selon toit la concaténation de "" et d'un chiffre ?
Tu alourdis pour alourdir ?

[alpijo]

Je vous remercie à tous les 2 pour votre réponse.
Concernant ton fichier Philippe il est vraiment très bien mais je n'arrive pas à voir comment l'adapter au mien :/

En faite pour être plus précis sur l'utilisation de mon fichier qui est plutôt un bon de commande.
J'ai 2 fichiers Excel :
- Bon de commande : Qui est composé d'un userform
- Listes des consommables : Il est composé de 5 onglets de classeur (Atelier P, Atelier S, etc.....)

Lorsque l'userform du fichier Bon de commande s'ouvre il me permet de choisir l'atelier concerné. Si je clique sur l'atelier P il m'ouvre uniquement l'onglet du classeur de l'atelier P et cache les autres.
Le but d'enregistrer le numéro de bon de commande dans un fichier texte est que si une personne de l'atelier P a passer une commande en cliquer sur le bouton Envoyer (par exemple 201812-004) et bien que lorsqu'une autre personne passera une commande quel soit de n'importe quel atelier ne puisse plus avoir ce numéro "004" mais qu'elle est le "005".

Je vous joints mes fichiers afin que vous puissiez mieux comprendre ce que je souhaite faire et éventuellement m'expliquer comment mieux intégrer l'année et le mois avec une remise à 0 du numéro tous les mois.

Je vous en remercie infiniment pour l'aide apporté

[Philippe Tulliez]

Bonjour,
Le classeur que j'ai mis en téléchargement pour illustrer la fonction Counter utilise les cellules du même classeur que l'endroit où se trouve la fonction mais ce n'est qu'un exemple.
La fonction attend un ou plusieurs arguments. Que ces arguments proviennent de cellules d'excel, du même classeur, d'un autre classeur, d'un fichier texte, d'un fichier INI importe peu.
A toi de faire ton choix.
La fonction Counter elle même peut se trouver dans un classeur de macro complémentaire (suffixé xlam)

[alpijo]

Je suis désolé, ton fichier est super bien fait mais je n'arrive pas à voir comment l'intégrer dans mon code :/

[Philippe Tulliez]

Bonjour,

je n'arrive pas à voir comment l'intégrer dans mon code

Déjà comme l'a très judicieusement fait remarquer Unparia, le code que tu as affiché est plein d'incohérences et ne peux que renvoyer des erreurs donc oublie d'intégrer le fonction dans l'état actuel des choses.

Je résume
La fonction Counter que j'ai écrite ne fait que renvoyer un numéro incrémenté en fonction d'un ou plusieurs arguments que tu dois lui passer. Un seul est obligatoire, c'est LastCount.
Cette fonction peut être utilisée depuis une procédure VBA ou comme fonction dans d'excel.

Code de cette fonction

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
Function Counter(LastCount As Long, _
                 Optional LastDate As Date, _
                 Optional PeriodOfChange As String = "C", _
                 Optional WorkDate As Date) As Long
  ' Author : Philippe Tulliez http://philippe.tulliez.be/
  ' Date : 2013-12-18 (first version 2002-05-28)
  ' Version: 3.1
  ' Fonction renvoyant un nombre entier en fonction des arguments suivant :
  ' ... Arguments ... (tous facultatifs sauf le 1er)
  ' LastCount (Long) ' Dernier numéro
  ' [LastDate] (Date) ' Dernière date d'incrémentation du compteur. (Défaut->Date du jour)
  ' [PeriodOfChange] (String) ' Périodicité de reinitialisation du compteur. (Continu par défaut)
  ' (Y) Annuel, (Q) Trimestriel, (M) Mensuel, (W) Hebdomadaire, (D) Jour
  ' [WorkDate] (Date) ' Date de travail. (Date du jour par défaut)
 
  Dim Period As String
  ' Check Arguments
  If LastDate = 0 And Len(PeriodOfChange) = 0 Then Counter = LastCount + 1: Exit Function
  If LastDate = 0 Then LastDate = Date
  If WorkDate = 0 Then WorkDate = Date
  ' Gestion des périodicités (Y)ear, (Q)uarter, (M)onth, (W)eek,(D)ay
  Period = LCase(Left(PeriodOfChange, 1))
  If InStr("yqmwd", Period) = 0 Then Period = "c"
  Select Case Period
    Case "y": Period = "yyyy"
    Case "w": Period = "ww"
  End Select
  Select Case Period
    Case "c": Counter = LastCount + 1
    Case Else: Counter = 1 + LastCount * Abs(DateDiff(Period, LastDate, WorkDate, vbMonday) = 0)
  End Select
End Function

Si l'on prend le cas le plus simple d'une incrémentation continue, on utilisera donc ce seul argument
Avec Excel, la fonction ci-dessous renverra 11

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=Counter(10)

et en VBA

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
Sub testCounter()
 MsgBox Counter(10)
End Sub

Utilisée ainsi la fonction n'a pas d'intérêt.
Il faut donc stocker le dernier numéro, soit dans une cellule, soit dans un fichier texte, soit dans un fichier INI
Cas simple dans une cellule d'excel que l'on va nommer pLastCount (le préfixe p me permettant de savoir que c'est un paramètre), on placera le dernier numéro utilisé

Avec Excel

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=Counter(pLastCount)

en VBA

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
Sub testCounter()
 MsgBox Counter(Range("pLastCount").Value)
End Sub

Evidemment pour que cela soit efficace, il faut ensuite sauver le nouveau numéro ainsi incrémenté et cela seul le VBA peut le faire.
Exemple où l'on sauve immédiatement le nouveau numéro après l'avoir lu

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
Sub testCounter()
 Dim NewNumber As Long
 With Range("pLastCount")
  NewNumber = Counter(.Value) ' Lecture
  .Value = NewNumber          ' Ecriture
 End With
End Sub

Maintenant, si l'on souhaite conserver le numéro dans un fichier texte nommé pour l'exemple Compteur.Txt on va créer deux procédures. L'une pour lire le dernier numéro utilisé (GetNumber) et l'autre pour écrire et donc sauver le nouveau numéro (PutNumber)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
Function GetNumber() As Long
  ' Renvoie le numéro contenu dans le fichier Compteur.txt
  Const FileName As String = "Compteur.txt"
  Dim FullName As String ' Nom complet Folder + Nom du fichier
  Dim Channel As Byte
  Dim Value As String
  '
  FullName = ThisWorkbook.Path & "\" & FileName
  Channel = FreeFile
  Open FullName For Input As #Channel
  Line Input #Channel, Value ' Lecture de la ligne
  GetNumber = Value
  Close #Channel
End Function

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
Sub PutNumber(NewValue As Long)
  ' Déclaration variables
  Const FileName As String = "Compteur.txt"
  Dim FullName As String ' Nom complet Folder + Nom du fichier
  Dim Channel As Byte
  Dim Value As Long
  '
  FullName = ThisWorkbook.Path & "\" & FileName
  Channel = FreeFile
  Open FullName For Output As #Channel
  Print #Channel, NewValue
  Close #Channel
End Sub

Exemple d'utilisation des deux procédures

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
Sub TestCounterWithText()
  Dim NewCounter As Long
  NewCounter = Counter(GetNumber) ' Lecture de l'ancien numéro
  MsgBox "New = " & NewCounter
  PutNumber NewCounter            ' Ecriture du nouveau numéro
End Sub

Reste maintenant, le formatage
On va pour cela écrire le numéro lu dans la cellule d'excel nommée pLastCount sans l'incrémenter
Le code

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
Sub TestGetNumber()
  ' Lecture de l'ancien numéro et écriture dans la cellule pLastCount
  Range("pLastCount").Value = GetNumber
End Sub

Dans une cellule quelconque, on va insérer la formule ci-dessous pour afficher le prochain numéro précédé du numéro de l'année et du mois soit par exemple 2018/12-16

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=TEXTE(AUJOURDHUI();"AAAA/MM") & "-" & Counter(pLastCount)

Il suffit ensuite une fois la validation du document de sauver le nouveau numéro dans le fichier texte en appelant une procédure

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
Sub TestPutNumber()
  PutNumber Counter(Range("pLastCount").Value)
End Sub

J'espère que ces explications "Pas à pas" te permettront de mettre en place ton code.