Problème d'espace non conservé dans une méthode text to colonns

**rom94000** · 16/04/2018, 17h41

Bonjour à toutes et à tous,

Je suis débutant en VBA Excel et suis confronté à une problématique et me permets de soliciter votre aide.

L’objectif de mon code est de :
1. Récupérer une ligne d’un fichier plat (les espaces étant important)
2. Passer la ligne dans Excel (découpage de la ligne par colonne)

C’est sur cette étape que je rencontre un problème, les données sont présentes dans les colonnes, mais les espaces n'y sont plus présent.
Je me retrouve avec ‘Valeur’ au lieu de ‘Valeur ‘

Du coup, lorsque je fais une concaténation après modification la ligne créée n'est plus correctement formée.

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
Function LireFlux(CellFlux As String)
'
' LireFlux Macro
' Decoupage de la ligne, permet d avoir une valeur par cellule
 
'
    Range(CellFlux).Select
    Selection.TextToColumns Destination:=Range(CellFlux), DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, 2), Array(1, 2), Array(10, 2), Array(11, 2), Array(26, 2), _
        Array(29, 2), Array(38, 2), Array(43, 2), Array(52, 2), Array(58, 2), Array(59, 2), Array( _
        65, 2), Array(66, 2), Array(69, 2), Array(75, 2), Array(80, 2), Array(82, 2), Array(83, 2), _
        Array(88, 2), Array(90, 2), Array(97, 2), Array(104, 2), Array(107, 2), Array(114, 2), _
        Array(121, 2), Array(128, 2), Array(138, 2), Array(146, 2), Array(149, 2), Array(174, 2), _
        Array(189, 2), Array(192, 2), Array(195, 2), Array(211, 2), Array(215, 2), Array(223, 2), _
        Array(231, 2), Array(233, 2), Array(236, 2), Array(240, 2), Array(242, 2), Array(248, 2), _
        Array(258, 2), Array(264, 2), Array(268, 2), Array(270, 2), Array(285, 2), Array(291, 2), _
        Array(305, 2), Array(307, 2), Array(309, 2), Array(311, 2), Array(320, 2), Array(321, 2), _
        Array(353, 2), Array(354, 2), Array(355, 2), Array(356, 2)), TrailingMinusNumbers:= _
        True
End Function

J'ai également pensé à réinsérer les espaces après concatenation, mais la solution me parait peu viable, certains champ pouvant être variable, les espaces le sont également.

Connaissez-vous un moyen de conserver les espaces dans colonnes ?

Cordialement,

Romain

**Jean-Philippe André** · 16/04/2018, 20h20

Salut,

lorsque tu fais ton text to columns, le code que tu utilises passe par
DataType:=xlFixedWidth

C'Est donc du texte à longueur fixe..., Est-ce bien le cas ?

Aurais-tu un exemple de ligne ?

**rom94000** · 21/04/2018, 18h24

Bonjour Jean-Philippe,

Merci pour ta réponse.
Je vais regarder de plus près ce DataType

Il s'agit de texte fixe(code postal) et de texte variable (prénom)sur une plage d'index définie [1,10].

Exemple:

1romain 94000
2elisa 78000

Actuellement, j'essaie de redimensionner mes cellules après avoir fait le découpage.

Cordialement,

rOmain

**unparia** · 21/04/2018, 18h53

Bonjour
J'ignore la raison de cet aller (éclatement en deux cellules) puis ce retour (concaténation des deux cellules).
Je ne vois par ailleurs pas ce qui empêche une concaténation en ajoutant un espace si seconde cellule non vide.
On en est où ?
Tu as une première colonne contenant un ou deux mots séparés par un espace et tu veux l'éclater sur deux colonnes en conservant l'espace devant la seconde ?
Un truc :
1) remplacement de " " par "/ "
2) texttocolumns sur séparateur "/"
de cette manière :
"toto titi" deviendra "toto/ titi" que l'éclatement transformera en "toto" sur la colonne de gauche et " titi" sur celle de droite

**rom94000** · 21/04/2018, 21h32

Bonjour unparia,

L'objectif marco est de pouvoir:
1. passer les lignes (365 caractères) issues d'un fichier plat et les ventiller dans Excel par type de valeur "mon, prénom,..." (pour repérer plus vite les données et vérifier s'il y a des erreurs).
2. Constituer un jeux de données avec pour base les lignes ventillées (modifier certaines données) et reconstituer le fichier plas pour ensuite réaliser un traitement via Batch.

Merci pour ton astuce, je vais essayer.

rOmain

**patricktoulon** · 22/04/2018, 05h54

re
salut
plutot que de chercher a formater tes cellules pourquoi ne t'en occuperais tu pas lors de la reconcatenation ?apres avoir fait tes modifs
un simple join par lignes avec pour separateur " "( un espace )

voir un simple replace vbtab sur la recupération de la plage complete dans le clipboard

demo dans un module standard

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
'pour window
Public Property Get presse_papier_Window() As String
presse_papier_Window = Replace(CreateObject("htmlfile").parentwindow.clipboardData.GetData("TEXT"), vbTab, " ")
End Property
'ou pour Mac et window
Public Property Get presse_papier_Mac_window_String()'necessite l'activation de la reference microsoft forms x.x object librarary
      With New DataObject: .GetFromClipboard: presse_papier_Mac_window_String = Replace(.GetText(1), vbTab, " "): End With
End Property
Sub test1()
  ' texttocolumn par les espaces uniquement
   With Range("A1:A10")
    .TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, Comma:=False, Space:=True, Other:=False
     End With
End Sub
Sub test2()
ActiveSheet.Cells(1).CurrentRegion.Copy
MsgBox presse_papier_Window
End Sub

**rom94000** · 22/04/2018, 12h04

Bonjour,

Merci pour ta réponse Patrick.

Dans mon second message l'exemple que je donne présente la même problématique. Lorsque je l'ai tapé l'exemple dans le message, j'ai saisie romain suivi de 4 espaces et elisa suivi de 5 espaces. les code postaux se retrouvent donc au même niveau. Les lignes doivent être formatées de la même façon. Mon code postal doit obligatoirement se trouver sur le 11 éme caractères de la ligne.

J'ai pensé à fixer une taille directement en passant par une variable

dim prenom As String *10

mais je ne vois pas comment l'appliquer à l'ensemble de même cellules prenom.

Je pense en tout effectivement faire se traitement après modification des données juste avant la concaténation, comme tu me l'as conseillé

Cordialement,

rOmain

**patricktoulon** · 22/04/2018, 12h22

re
autrement dit tu veux dans ton fichier texte final
lres tiret representent des espace vides

romain---- silvie---- 83200
jean ------ renoirt - 75000
henry----- sarte---- 84560

**rom94000** · 22/04/2018, 14h16

Bonjour Patrick,

Oui, c'est bien cela.

Cordialement,

rOmain

**rom94000** · 22/04/2018, 14h58

J'ai trouvé "une piste", mais elle est assez bancale

Code :

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

1
2
3
4
5
6
7
8
' Boucle sur la cellule a traiter
    For premiere_Ligne = 7 To derniere_Ligne
        CellFluxPrenom = "E" + CStr(premiere_Ligne)
        'Debug.Print CellFluxPrenom
        'Debug.Print premiere_Ligne
        Range(CellFluxPrenom) = Range(CellFluxPrenom).Value & "  "
 
    Next premiere_Ligne

Cette solution serait envisageable si la valeur de la cellule était fixe.
je vais creuser en ce sens et essayer de faire une function qui m'ajoute le nombre d'espace manquant en fonction du nombre de caractères saisies.

Cordialement,

rOmain

**rom94000** · 22/04/2018, 15h40

Bonjour,

Je pense avoir la solution même si le traitement va être un peu fastidueux.

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
33
34
35
36
Sub dimensionnementCellule()
 
   Dim premiere_cellule As Integer
   Dim derniere_cellule As Integer
   Dim nb_colonne As Integer
   Dim donnee As String * 4
   Dim cellule As String
 
   premiere_cellule = 7
   derniere_cellule = 0
 
' Comptage des lignes 'Feuille FLUX' moins les deux lignes de nommage des colonnes
 
    With Worksheets("FLUX")
    nb_colonne = Range("B:B").Cells.SpecialCells(xlCellTypeConstants).Count - 2
    'Debug.Print nb_colonne 
 
 
' Affichage du numero de la derniere colonne 'Feuille FLUX'
    derniere_cellule = Columns(2).Find("*", , , , xlByColumns, xlPrevious).Row
    'Debug.Print derniere_cellule 
    End With
 
' Boucle sur les colonnes a traiter
    For premiere_cellule = 7 To derniere_cellule
        cellule = "E" + CStr(premiere_cellule)
        'Debug.Print Cellule
        'Debug.Print premiere_colonne
        donnee= Range(cellule).Value
        Range(cellule).Value = donnee
        If Range(cellule).Value = Null Then
            Exit For
        End If
    Next premiere_cellule
 
End Sub

je vais devoir faire le traitement pour l'ensemble des cellules et après viendra la concatenation.

Cordialement,

rOmain

**patricktoulon** · 22/04/2018, 17h23

re
il te faut:

determiner la plus grande chaine de caracteres dans les cellules
créer un string d'espace avec ce nombre + le nombre d'espaces que tu veux
utilisation de mid(l'application et non la fonction ) sur ce string pour remplacer le meme nombre d'espace que le len de la chaine de la cellule et cela dans une boucle sur les cells
créer le texte dans cette boucle

conclusion

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
Sub test3()
    Dim X As String
    Set plage = Range("A1").CurrentRegion
    'nombre de caracteres le plus long dans les cellules de la plage on utilise une cellule libre en l(occurence la derniere a droite ligne 1
    With Cells(1, Columns.Count): .FormulaArray = "=MAX(LEN(" & plage.Address & "))": X = .Value: .Value = "": End With
    ro = plage.Row    'on demarre de la premiere ligne  de la plage pour les saut de lignes
    For Each cel In plage.Cells
        If cel.Row <> ro Then texte = texte & vbCrLf: ro = cel.Row
        Mask = Application.Rept(" ", X + 4)    'on créé un mask d'espace au nombre le plus long +4
        Mid(Mask, 1, Len(cel.Text)) = cel.Text
        texte = texte & Mask
    Next
    Debug.Print texte
End Sub