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
| Sub Test()
Dim Chemin As String
'Répertoire contenant les fichiers
Chemin = Application.GetOpenFilename("Text Files (*.txt), *.txt")
Dim Cellule As Range
Set Cellule = Application.InputBox("Sélectionnez La cellule à partir de laquelle insérer les données", "Sélection de cellules", Type:=8)
If Cellule.Count > 1 Then MsgBox "Une seule cellule!", vbCritical: Exit Sub
'ici d'autres tests si vous le souhaitez
ImportText Chemin, Cellule
End Sub
Sub ImportText(NomFichier As Variant, Cible As Range)
'Sources :
'http://excel.developpez.com/telecharger/detail/id/2413/Regrouper-le-contenu-de-plusieurs-fichiers-txt-dans-une-feuille-de-calcul
Dim QT As QueryTable
Dim Tb(), dl As Long
ThisWorkbook.Worksheets.Add
ActiveSheet.Name = "Intermediaire"
Set QT = ActiveSheet.QueryTables.Add(Connection:="TEXT;" & NomFichier, Destination:=Sheets("Intermediaire").Range("A1"))
With QT
'D?finit les s?parateur de colonnes dans le fichier txt
.TextFileOtherDelimiter = ";"
.TextFileSemicolonDelimiter = True
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.Refresh
End With
'Sources :
'http://www.developpez.net/forums/d140322/logiciels/microsoft-office/excel/macros-vba-excel/vba-e-querytables-boucle/
With Worksheets("Intermediaire")
For Each QT In .QueryTables
QT.Delete
Next QT
dl = .Range("A" & Rows.Count).End(xlUp).Row
Tb = .Range("A1:D" & dl).Value
For i = LBound(Tb, 1) To UBound(Tb, 1)
For j = LBound(Tb, 2) To UBound(Tb, 2)
If IsNumeric(Replace(Tb(i, j), ".", ",")) Then Tb(i, j) = CDbl(Replace(Tb(i, j), ".", ","))
'Tb(i, j) = Replace(Tb(i, j), ".", ",")
Next j
Next i
Application.DisplayAlerts = False
.Delete
Application.DisplayAlerts = True
End With
Cible.Resize(UBound(Tb, 1), UBound(Tb, 2)) = Tb
End Sub |
Partager