Ouverture automatique de fichiers CSV

Version imprimable

Bonjour,

Je développe actuellement une application Excel permettant d'ouvrir en deux clics, un fichier CSV.
Par un UserForm, en deux clics, le fichier CSV est automatiquement converti (en gros, le traditionnel "DONNEES" > "Fichier Texte" > "Convertir") est codé dans ma macro et devient donc une action transparente pour l'utilisateur final.

La macro fonctionne bien, mais j'ai une erreur 1004 ("Microsoft Excel ne peut convertir qu'une colonne à la fois") lorsque j'ouvre un CSV avec séparateur virgule et je n'arrive pas à la résoudre !! Malgré cette erreur, le fichier CSV avec séparateur virgule s'ouvre correctement... Etrange donc. Le "Application.DisplayAlerts = False" ne neutralise pas le message d'erreur.

Voici le code qui me permet d'éxécuter l'ouverture du CSV
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 ' Bouton ouvrir fichier Private Sub Ouvrir_Click() Application.DisplayAlerts = False If (Me.Chemin = "Faux" Or Me.Chemin = "Chemin + nom + extension du fichier ouvert") Then 'Si aucun fichier n'est sélectionné alors MsgBox "Choisissez un fichier !" 'Message d'erreur précisant qu'il faut choisir un fichier Else 'Si tout ok, on lance la conversion du fichier csv Workbooks.OpenText Filename:=Me.Chemin, Origin:=xlWindows, _ StartRow:=1, DataType:=xlDelimited, Semicolon:=True, Comma:=True ActiveCell.CurrentRegion.TextToColumns Comma:=True Ouverture.Hide Workbooks("Outil_ouverture_V2.xlsm").Save 'Workbooks("Outil_ouverture_V2.xlsm").Close End If End Sub
Le problème survient à la ligne "ActiveCell.CurrentRegion.TextToColumns Comma:=True"

Pour information : le "Me.Chemin" me permet d'obtenir l'adresse complète du fichier à importer (exemple : "D:\User\Doc.csv") et est inscrit dans un TextBox de l'userform.
La macro bloque lorsqu'il n'y a aucun fichier sélectionné (donc, quand le texte "par défaut" est présent) et lorsque on annule la sélection du fichier (dans la fenêtre "ouvrir", en cliquant sur "annuler").

J'espère que ces explications sont assez claires pour vous...

Encore merci pour votre aide future !

09/09/2016, 17h27
Theze
Bonjour,

Pour le fichier, utilise la fonction Dir() :
Code:

1 2 If Dir(Me.Chemin) <> "" Then
Pour le reste, étant sur un Smartphone, je ne peux pas t'aider pour l'instant !
09/09/2016, 18h16
Pix_elle

Bonjour,

Merci pour ta réponse. Je vais tester ça dans la soirée !

Malgré tout, j'ai continué à me creuser la tête et en ajoutant "On error Resume Next" juste avant la ligne qui bloque et ça semble fonctionner maintenant ! Alors je sais pas si ce sera stable sur la durée, mais pour le moment ça tient.
09/09/2016, 18h20
joe.levrai

Bonjour,

en utilisant la propriété currentregion, tu récuppères la plage rectangulaire des données qui contient ta cellule active.
Ce qui pourrait expliquer que tu te retrouves avec plusieurs colonnes au lieu d'une seule

en isolant sur une seule colonne, ça raconte quoi ?

Code:

ActiveCell.CurrentRegion.Columns(1).TextToColumns Comma:=True
09/09/2016, 18h21
Marc-L

Bonjour.

Citation:

Envoyé par Pix_elle

en ajoutant "On error Resume Next"

Au risque de masquer d'autres problèmes et de perdre des données …

_________________________________________________________________________________________________________
Je suis Paris, Nice, Bruxelles, Charlie, …
09/09/2016, 18h39
Pix_elle

Citation:

Envoyé par joe.levrai

Bonjour,

en utilisant la propriété currentregion, tu récuppères la plage rectangulaire des données qui contient ta cellule active.
Ce qui pourrait expliquer que tu te retrouves avec plusieurs colonnes au lieu d'une seule

en isolant sur une seule colonne, ça raconte quoi ?

Code:

ActiveCell.CurrentRegion.Columns(1).TextToColumns Comma:=True

Et bien écoutes, ça semble fonctionner ! Je ferai faire des tests aux collègues lundi pour voir si ils me remontent un quelconque problème mais en tout cas, tu me sauves de la panade ! Merci beaucoup.

Citation:

Envoyé par Marc-L

Bonjour.

Au risque de masquer d'autres problèmes et de perdre des données …

_________________________________________________________________________________________________________
Je suis Paris, Nice, Bruxelles, Charlie, …

Oui, ça me semblait bien que ça pouvait poser problème. Merci en tout cas :)
12/09/2016, 17h35
Pix_elle

Bonjour à tous,

Voici un petit retour : La macro fonctionne hyper bien ! Mais mes collègues m'ont fait la remarque que lors de l'export, tous les accents sautaient et laissaient leur place à des caractères bizarres.

Aussi, je me suis rendue compte que j'avais omis de préciser dans mon code que je voulais l'import en encodage UTF-8. Comment et où dois-je placer cet attribut ?

Merci de votre aide !
Bonne soirée !
12/09/2016, 18h44
Marc-L

Export ?! Mais initialement il s'agissait d'un import ! …

Bref s'il s'agit encore d'un fichier mal exporté, mieux vaut éviter de le bidouiller dans Excel
et demander à la source de bien effectuer son boulot !

Différentes solutions même avec Excel mais tenants & aboutissants sont vraiment obligatoires !
Sinon c'est « Pas de bras, pas de chocolat ! »
12/09/2016, 18h49
Pix_elle

Désolée, c'est bien de l'import que je voulais parler (le message est rectifié).

Selon toi, il faudrait que je m'assure en amont que la sortie en CSV se réalise correctement (c'est à dire, sans caractères étranges et tout ça ?) Je jetterai un œil demain du coup. Merci pour la piste ;)
12/09/2016, 18h55
Marc-L

Non, ce n'est pas gênant car Excel dispose déjà en interne de différentes méthodes pour lire les vieux fichiers au format DOS !
Origin à modifier dans la ligne de code n°7 car tel quel c'est pour Windows ‼
Mais c'est mieux si l'application générant l'export peut le faire directement au format Windows …

Et si la méthode OpenText - tout comme Open - est bien paramétrée (actuellement dans le code il y a deux séparateurs différents !)
aucun besoin d'une conversion supplémentaire car cette méthode effectue déjà une conversion ‼
Pourtant ce n'est vraiment pas compliqué, tout est déjà dans l'aide VBA interne !

Sinon, si le fichier csv n'est pas confidentiel, le joindre (si csv ne passe pas sur ce forum alors le renommer en .csv.txt)
en expliquant le but dans Excel (à part l'ouverture du fichier) en vu d'utiliser la méthode la plus adaptée …

Bonjour,

Alors, j'ai travaillé un peu dessus aujourd'hui et :

- Concernant le "Origin = XlWindows" : j'ai compris qu'il s'agissait de l'origine du fichier. Et qu'on pouvait choisir un nombre entier pour UTF8 (65001). Du coup, je me suis empressée d'aller modifier ça dans mon code.
Code:

1 2 3 Workbooks.OpenText Filename:=Me.Chemin, Origin:=65001, _ StartRow:=1, DataType:=xlDelimited, Semicolon:=True, Comma:=True ActiveCell.CurrentRegion.Columns(1).TextToColumns Comma:=True
Mais... Les accents ne viennent pas et cela ralenti énormément mon système.
Après coup, je me suis assurée que le fichier CSV d'origine possède bien les accents et c'est le cas.

- Pour ce qui est du OpenText... Au tout début de l'écriture de ma macro, j'avais pensé à faire un SI afin de dire : "Si tu trouves un point-virgule, alors tu fais comme ça, sinon tu fais comme ci". J'ai abandonné cette idée quand j'ai découvert le code actuel.

Malheureusement, les fichiers CSV sont confidentiels et je ne peux les transmettre..

14/09/2016, 11h56
Marc-L

Si le fichier a pour séparateur seulement la virgule, pourquoi alors dans ton code tu en utilises deux ?‼ (cf ligne 2)
Avec la méthode Opentext bien paramétrée (cf aide VBA interne), pas besoin de la ligne 3 …

Bonjour,

En ajoutant "semicolon=true comma=true", je voulais gérer le fait que le séparateur soit une virgule OU un point-virgule. En gros, faire un code unique pour les deux possibilités. Mais visiblement, à trop vouloir réduire la taille du code, ça coince au bout d'un moment...

J'ai reconstruis mon code sur ma première idée donc... Qui était la bonne (??) Ou il consistait de regarder si le fichier importé possédait des points-virgule ou des virgules. C'est un peu artisanal, et je pense que le code peut être optimisé (notamment par la lecture du fichier sans l'ouvrir et le refermer aussitôt :mouarf: parce que je trouve que ça ralenti pas mal l'éxécution de la macro) la priorité étant d'importer un CSV en UTF8. Et attention les yeux... Ca y est enfin !!!

J'ai remis tout mon cheminement en question pour au final utiliser l'enregistreur de macro et en m'inspirant fortement de ce sujet (ici)

En plus de ça, je demande également la conversion de toutes les cellules en texte.

Encore merci de m'avoir aidé et aiguillé !

Ci-dessous, le code de ma macro

Code:

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
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
    ' Bouton ouvrir fichier
    Private Sub Ouvrir_Click()
    Application.DisplayAlerts = False   'Eviter les messages d'erreur
 
    Dim Classeur1 As String             'Variable texte définie
    Classeur1 = ActiveWorkbook.Name     'L'outil prend comme nom "Classeur1"
    If (Dir(Me.Chemin) = "") Then       'Si aucun fichier n'est sélectionné alors
    MsgBox "Choisissez un fichier !"    'Message d'erreur précisant qu'il faut choisir un fichier
    Ouverture.Show
    Else
 
'Ouverture du fichier CVS pour vérifier quel séparateur est présent : point-virgule ou virgule ?
    Workbooks.OpenText Filename:=Me.Chemin
    Dim Classeur2 As String
    Classeur2 = ActiveWorkbook.Name
 
 
       'Si en A1, il y a un point-virgule
       If InStr(Range("A1"), ";") Then
       Workbooks(Classeur2).Close
 
    Workbooks.Add
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;" & Me.Chemin _
        , Destination:=Range("$A$1"))
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 65001
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = True
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
 
 
 
       'Si en A1, il y a une virgule
       ElseIf InStr(Range("A1"), ",") Then
       Workbooks(Classeur2).Close
 
       Workbooks.Add
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;" & Me.Chemin _
        , Destination:=Range("$A$1"))
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 65001
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
 
 
       End If
 
    ' Passage de toutes les données au format texte
    Dim maPlage As Range
    Dim DernLigne As Long, DernColonne As Integer
    'dernière ligne colonne A
    DernLigne = Range("A" & Rows.Count).End(xlUp).Row
    'dernière colonne ligne 1
    DernColonne = Cells(1, Cells.Columns.Count).End(xlToLeft).Column
    Set maPlage = Range(Cells(1, 1), Cells(DernLigne, DernColonne))
    maPlage.Select
    Selection.NumberFormat = "@"
    ' Fin de passage de toutes les données au format texte
 
    End If
    End
 
    Workbooks(Classeur1).Save
    End Sub

15/09/2016, 12h50
Marc-L

Oui pour importer directement les données dans une feuille l'objet QueryTable est bien pratique !

A titre d'information, j'ai retrouvé entre temps sur un autre forum une de mes interventions
indiquant qu'il fallait parfois renommer un fichier csv en .txt pour que l'ouverture avec OpenText s'effectue bien,
tu es peut-être dans ce cas-là …

Astuces pour QueryTable dans cette discussion …

Je remets ce sujet au gout du jour : j'ai eu des retours de mon applications et j'ai une petite erreur lors de l'ouverture du CSV, au niveau du format des cellules.

Dans mon code actuel, que vous trouverez ci-dessous, je transformais les cellules en texte après l'import du CSV. Du coup, tous les zéros non significatifs passaient à la trappe et impossible de les remettre.

Code:

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
37
38
39
  Workbooks.Add
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;" & Classeur2 _
        , Destination:=Range("$A$1"))
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 65001
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = True
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
 
    ' Passage de toutes les données au format texte
    Dim maPlage As Range
    Dim DernLigne As Long, DernColonne As Integer
    'dernière ligne colonne A
    DernLigne = Range("A" & Rows.Count).End(xlUp).Row
    'dernière colonne ligne 1
    DernColonne = Cells(1, Cells.Columns.Count).End(xlToLeft).Column
    Set maPlage = Range(Cells(1, 1), Cells(DernLigne, DernColonne))
    maPlage.Select
    Selection.NumberFormat = "@"
    ' Fin de passage de toutes les données au format texte

Je sais qu'avec .TextFileColumnDataTypes, on peut paramétrer le format des cellules... Mais mon outil est construit de façon à ouvrir des CSV sans modèle précis (allant de 2 à X colonnes). Avec l'enregistreur de macro, je suis arrivée à cela :

Code:

1
2
3
        .TextFileColumnDataTypes = Array(2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, _
        2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2 _
        , 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2)

Aussi, par quel moyen puis-je "remplir" ce .TextFileColumnDataTypes de façon à dire "Passer les cellules non vides en format texte"

Merci par avance !
Bonne journée

28/09/2016, 15h49
Marc-L

Comme l'indique l'aide VBA et juste par son nom cette propriété s'applique à l'intégralité des colonnes
et non pas cellule par cellule …

Soit définir une plage de colonnes assez large au format texte avant l'importation sans définir TextFileColumnDataTypes
soit oublier QueryTable vu la caractéristique aléatoire côté source en lisant classiquement le fichier
via Open, OpenText ou encore Line Input # afin de détecter le format nécessaire colonne par colonne …
28/09/2016, 16h19
Pix_elle

Ah d'accord. C'est dommage pour le coup !

J'ai donc pris l'option n°1 : définir une plage relativement grande (290 colonnes).. Qui pourra être augmenté au besoin.

Merci pour ta réponse !