VBA créer un fichier TXT avec position des données

**cheribibi33** · 31/10/2019, 11h12

Bonjour,

je dois créer un fichier txt (DOS/WINDOWS) depuis un fichier excel. C'est pour réaliser un fichier CFONB (transmission de données banque).

Dans mon exemple j'ai cette macro qui me crée le fichier.

Par contre mon problème est que je dois respecter des tailles et des positions de champs. C'est la que je bloque.

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
 
Sub SaveAsTXT()
Dim Range As Object, Line As Object, Cell As Object
Dim StrTemp As String
Dim Nomfichier As String
Dim chemin As String
Dim Separateur As String
 
    ActiveWorkbook.Save
    Sheets("DEPOT").Select
 
Nomfichier = InputBox("Veuillez entrer le nom de fichier pour la SVG" & Chr(13) & Chr(10) & "Ajouter N° Bordreau_AAMMJJ ", "Nom fichier ?")
chemin = ThisWorkbook.Path & "\FICHIERS\TXT_"
Separateur = ""
Set Range = ActiveSheet.UsedRange
Open chemin & Nomfichier & ".txt" For Output As #1
For Each Line In Range.Rows
StrTemp = " "
For Each Cell In Line.Cells
StrTemp = StrTemp & CStr(Cell.Text) & Separateur
Next
Print #1, StrTemp '= " "
Next
Close
End Sub

Ligne 1 reprend ces données : Enregistrement émetteur

N°	CHAMP	TAILLE	POSITION	DESCRIPTION
1	TYPE D'ENREGISTREMENT	9 (02)	1 à 2	Initialisé à 03
2	CODE OPERATION	9 (02)	3 à 4	Initialisé à 60
3	NUMEROTAGE	9 (08)	5 à 12	N° d'ordre de l'enr ( facultatif )
4	FILLER	9 (06)	13 à 18	Initialisé à blanc
5	FILLER	X (06)	19 à 24	Initialisé à blanc
6	DATE DE REMISE	9 (06)	25 à 30	Date de constitution du fichier
7	RAISON SOCIALE DU CEDANT	X (24)	31 à 54	Votre raison sociale
8	DOMICILIATION BANCAIRE	X (24)	55 à 78	Nom de votre banque
9	CODE ENTREE	9 (1)	79	Code du type de remise
10	CODE DAILLY	X (01)	80
11	MONNAIE DE LA REMISE	X (01)	81	E pour Euro
12	CODE BANQUE DU CEDANT	9 (05)	82 à 86
13	CODE GUICHET DU CEDANT	9 (05)	87 à 91	Code guichet du cédant
14	N° DE COMPTE DU CEDANT	X (11)	92 à 102	N° de compte du cédant
15	FILLER	X (16)	103 à 118	Initialisé à blanc
16	DATE DE VALEUR	9 (06)	119 à 124	Date de Valeur
17	FILLER	X (10)	125 à 134
18	IDENTIFIANT ADHERENT	X (10)	135 à 144	N° d’adh.t (sur 7) + N°contrat (sur 3)
19	FILLER	X (16)	145 à 160	Initialisé à blanc

Ligne 2 à X lignes selon le nombre de clients : ENREGISTREMENT DESTINATAIRE

N°	CHAMP	TAILLE	POSITION	DESCRIPTION
1	TYPE D'ENREGISTREMENT	9 (02)	1 à 2	Initialisé à 06
2	CODE OPERATION	9 (02)	3 à 4	Initialisé à 60
3	NUMEROTAGE	9 (08)	5 à 12	N° d'ordre de l'enr.(facultatif)
4	FILLER	X (06)	13 à 18	Initialisé à blanc
5	FILLER	X (02)	19 à 20	Initialisé à blanc
6	REFERENCE TIRE	X (10)	21 à 30	Votre n° de débiteur
7	RAISON SOCIALE DU TIRE	X (24)	31 à 54	Raison sociale du tiré
8	DOMICILIATION BANCAIRE	X (24)	55 à 78	Domiciliation bancaire du tiré
9	CODE ACCEPTATION	9 (01)	79	Code acceptation
10	FILLER	X (02)	80 à 81	Initialisé à blanc
11	CODE BANQUE DU TIRE	9 (05)	82 à 86	Code banque du tiré
12	CODE GUICHET DU TIRE	9 (05)	87 à 91	Code guichet du tiré
13	N° DE COMPTE DU TIRE	X (11)	92 à 102	N° de compte du tiré
14	MONTANT	9 (12)	103 à 114	Montant de l’effet
15	FILLER	X (04)	115 à 118	Initialisé à blanc
16	DATE D'ECHEANCE	9 (06)	119 à 124	Date d'échéance de l’effet
17	DATE DE CREATION	9 (06)	125 à 130	Date de création de l’effet
18	FILLER	X (20)	131 à 150	Initialisé à blanc
19	REFERENCE TIREUR	X (10)	151 à 160

Ligne X (dernière ligne) selon nombre de ligne Clients : ENREGISTREMENT TOTAL

N°	CHAMP	TAILLE	POSITION	DESCRIPTION
1	TYPE D'ENREGISTREMENT	9 (02)	1 à 2	Initialisé à 08
2	CODE OPERATION	9 (02)	3 à 4	Initialisé à 60
3	NUMEROTAGE	9 (08)	5 à 12	N° d'ordre de l'enre. ( facultatif )
4	FILLER	X (06)	13 à 18	Initialisé à blanc
5	FILLER	X (84)	19 à 102	Initialisé à blanc
6	MONTANT TOTAL	9 (12)	103 à 114	MT =Somme des enreg. de code 06
7	FILLER	X (46)	115 à 160	Initialisé à blanc

Merci par avance pour les réponses.
Cdt

**Menhir** · 31/10/2019, 11h44

Envoyé par cheribibi33

je dois respecter des tailles et des positions de champs.

Il faut que tu reprennes la partie centrale de la ligne StrTemp = StrTemp & CStr(Cell.Text) & Separateur.

Mais il faudrait savoir si ce qui est ajouter entre deux colonnes pour aligner les champ sont des espaces ou des tabulations.
Dans les exemples que tu donnes, ce sont des tabulations, ce qui est problématique puisque, du coup, l'espacement dépend de la valeur de tabulation par le logiciel destination.

**cheribibi33** · 31/10/2019, 12h08

Bonjour,
Merci pour votre réponse.
Les séparateurs pour moi doivent être des espaces entre les colonnes, ci-après la norme des fichiers à respecter :

http://php4php.free.fr/quickvirprel/..._AFB-CFONB.pdf

Cdt

**Thumb down** · 31/10/2019, 12h37

Bonjour,

Par contre mon problème est que je dois respecter des tailles et des positions de champs. C'est la que je bloque.

cela veut dire que les champs ont un ordre bien défini et sont de longueur fixe!

par exemple le premier champ s'appel type d'enregistrement et il à deux caractère qui occupe respectivement la position 1 & 2 le la ligne!
le deuxième champ s'appel code opérationnel il à deux caractère qui occupe les positions 3 & 4

Code :

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

ligne=Left( cstr(type_enregistrement ) & space(2),2) & Left( cstr(code_opérationnel) & space(2),2)

**cheribibi33** · 31/10/2019, 12h53

Bonjour,

C'est exactement cela, comment adapter votre code dans mon fichier, en sachant que le nombre de lignes est variables

Cdt
Patrick

**Thumb down** · 31/10/2019, 13h00

déjà l'ordre des colonnes du fichier respect le modèle des champs?

**cheribibi33** · 31/10/2019, 13h22

Bonjour,
Oui c'est le bon ordre
Cdt
Patrick

**Menhir** · 31/10/2019, 13h38

Envoyé par cheribibi33

Les séparateurs pour moi doivent être des espaces entre les colonnes

Code :

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

StrTemp = StrTemp & Cell.Text & Space(XX - Len(Cell.Text)) & Separateur

XX étant la dimension souhaitée pour le champ.

Ce code ne prend pas en compte le cas d'un contenu de cellule plus long que le champ destinataire et qu'il faudrait tronquer.

**Thumb down** · 31/10/2019, 13h38

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
ub SaveAsTXT()
Dim Range As Object, Line As Object, Cell As Object
Dim StrTemp As String
Dim Nomfichier As String
Dim chemin As String
ActiveWorkbook.Save
Sheets("DEPOT").Select
 
 
Nomfichier = InputBox("Veuillez entrer le nom de fichier pour la SVG" & Chr(13) & Chr(10) & "Ajouter N° Bordreau_AAMMJJ ", "Nom fichier ?")
chemin = ThisWorkbook.Path & "\FICHIERS\TXT_"
 
 
Open chemin & Nomfichier & ".txt" For Output As #1
    Set Range = ActiveSheet.UsedRange
    Dim Separateur
    Separateur = Array(2, 2, 8, 6, 6, 6, 24, 24, 1, 1, 1, 5, 5, 11, 16, 6, 10, 10, 16) 'taille des colonnes!
        For Each Line In Range.Rows
            StrTemp = " "
        For i = 0 To UBound(Separateur)
        StrTemp = StrTemp & Left(CStr(Line.Cells(1, 1).Offset(, i).Text) & Space(Separateur(i)), Separateur(i))
        Next
    Print #1, StrTemp
    Next
Close
End Sub

**cheribibi33** · 31/10/2019, 15h19

Bonjour Thumb down,

Je viens de tester votre VBA, cela fonctionne, mais j'ai 2 problèmes : (fichier joint)
1° cela me crée un espace en trop entre les colonnes, exemple espace au début de ligne
2° en colonne 6 dernière ligne, le montant total est tronqué, en effet sur la dernière ligne j'ai ceci d'ajouté :
Taille 06

Ligne X (dernière ligne) selon nombre de ligne Clients : ENREGISTREMENT TOTAL

N°	CHAMP	TAILLE	POSITION	DESCRIPTION
1	TYPE D'ENREGISTREMENT	9 (02)	1 à 2	Initialisé à 08
2	CODE OPERATION	9 (02)	3 à 4	Initialisé à 60
3	NUMEROTAGE	9 (08)	5 à 12	N° d'ordre de l'enre. ( facultatif )
4	FILLER	X (06)	13 à 18	Initialisé à blanc
5	FILLER	X (84)	19 à 102	Initialisé à blanc
6	MONTANT TOTAL	9 (12)	103 à 114	MT =Somme des enreg. de code 06
7	FILLER	X (46)	115 à 160	Initialisé à blanc

Cdt
Patrick

**Menhir** · 31/10/2019, 15h56

Envoyé par cheribibi33

Je viens de tester votre VBA, cela fonctionne, mais j'ai 2 problèmes : (fichier joint)
1° cela me crée un espace en trop entre les colonnes, exemple espace au début de ligne
2° en colonne 6 dernière ligne, le montant total est tronqué, en effet sur la dernière ligne j'ai ceci d'ajouté :
Taille 06

Sans rire, tu as l'intention d'un peu participer à la résolution de ton problème ou tu comptes attendre que ça te tombe tout chaud dans le bec...

Nom : Becquée.png
Affichages : 1579
Taille : 35,0 Ko

**cheribibi33** · 31/10/2019, 16h02

Envoyé par Menhir

Sans rire, tu as l'intention d'un peu participer à la résolution de ton problème ou tu comptes attendre que ça te tombe tout chaud dans le bec...

J'ai répondu à votre message pour vous informer, j'ai solutionné l'espace en trop, et je regarde pour la dernière ligne colonne 6

Cdt

**Thumb down** · 31/10/2019, 16h12

Sans rire, tu as l'intention d'un peu participer à la résolution de ton problème ou tu comptes attendre que ça te tombe tout chaud dans le bec...

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
Sub SaveAsTXT()
Dim Range As Object, Line As Object, Cell As Object
Dim StrTemp As String, Nomfichier As String, chemin As String
Dim L As Long, C As Long, Pos As Integer
ActiveWorkbook.Save
Sheets("DEPOT").Select
 
 
Nomfichier = InputBox("Veuillez entrer le nom de fichier pour la SVG" & Chr(13) & Chr(10) & "Ajouter N° Bordreau_AAMMJJ ", "Nom fichier ?")
chemin = ThisWorkbook.Path & "\TXT_"
 
 
Open chemin & Nomfichier & ".txt" For Output As #1
    Set Range = ActiveSheet.UsedRange
    Dim Separateur
    Separateur = Array(Array(2, 2, 8, 6, 6, 6, 24, 24, 1, 1, 1, 5, 5, 11, 16, 6, 10, 10, 16), Array(2, 2, 8, 6, 2, 10, 24, 24, 1, 2, 5, 5, 11, 12, 4, 6, 6, 20, 10), Array(2, 2, 8, 6, 84, 12, 46))
        For L = 1 To Range.Rows.Count
        If L > 1 Then Pos = 1
        If L = Range.Rows.Count Then Pos = 2
            StrTemp = ""
        For C = 0 To UBound(Separateur(Pose))
            StrTemp = StrTemp & Left(CStr(Range(L, 1).Offset(, C).Text) & Space(Separateur(Pose)(C)), Separateur(Pose)(C))
        Next
    Print #1, StrTemp
    Next
Close
End Sub

Nom : Sans titre.png
Affichages : 1569
Taille : 3,0 Ko

**unparia** · 31/10/2019, 17h00

Bonjour

Je choisirais personnellement de travailler avec une structure.

Un exemple de ce genre : -->>

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
Private Type article
    ID As String * 10
    nom As String * 15
    prenom As String * 12
    profession As String * 20
    saut As String * 2
End Type
Private Sub CommandButton1_Click()
  Dim art As article, k As Long
  Open "d:\toto.txt" For Random As #1 Len = Len(art)
  For k = 1 To 5
    art.ID = "1234" & k
    art.nom = "Son_nom" & k
    art.prenom = "Son_prénom" & k
    art.saut = vbCrLf
    Put #1, k, art
  Next
  Close #1
End Sub

à "bûcher". Adapter la structure et appliquer la boucle for à des cellules de la feuille

**Thumb down** · 31/10/2019, 17h11

Bonsoir unparia,
Là tu construis un fichier à accès direct ! Je suis pas certain qu'il souhaite implementer sa propre SGBD!

**unparia** · 31/10/2019, 17h16

Bonjour Thumb down

Le résultat n'en est pas moins un fichier texte présenté en colonnes (n'est-ce pas ce qu'il souhaite ?)
On l'utilise ensuite comme on le souhaite.

**Thumb down** · 31/10/2019, 17h23

Si tu genaire un fichier avec des enregistrements structurés, tu embarque les caractères de separation des champs.

Ce n'est pas un fichier à lecture séquentielle mais fichier à accès direct. Il ne peut donc ce lire qu'au travers d'un random et la même structure !

**unparia** · 31/10/2019, 17h50

Ce n'est pas un fichier à lecture séquentielle mais fichier à accès direct. Il ne peut donc ce lire qu'au travers d'un random et la même structure !

Il peut le lire comme il peut lire le fichier TXT_CFONB_TXT.txt.
Dans un cas comme dans l'autre et d'après ce que je constate : nous avons un fichier texte dont les données sont en colonnes et séparées par des espaces, sur des lignes (une pour chaque enregistrement)
Je ne vois donc pas ce que tu entends par là

La structure finale étant la même, s'il peut lire l'un avec une méthode de lecture, il peut lire l'autre avec la même méthode de lecture.