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
|
Sub importation_texte_delimité()
'Définition des champs délimités et format
With Sheets("LISTE LONGUEUR ET CHAMPS")
.Range("b2").FormulaR1C1 = "=R[-1]C[-1]&"",""&RC[-1]"
.Range("b3:b" & .Range("A" & Rows.Count).End(xlUp).Row).FormulaR1C1 = "=R[-1]C&"",""&RC[-1]"
.Range("d1:d" & .Range("A" & Rows.Count).End(xlUp).Row) = "xlTextFormat"
.Range("e2").FormulaR1C1 = "=R[-1]C[-1]&"",""&RC[-1]"
.Range("e3:e" & .Range("A" & Rows.Count).End(xlUp).Row).FormulaR1C1 = "=R[-1]C&"",""&RC[-1]"
TailleColonne = .Range("b" & .Range("A" & Rows.Count).End(xlUp).Row)
FormatColonne = .Range("e" & .Range("A" & Rows.Count).End(xlUp).Row)
test = Right(TailleColonne, Len(TailleColonne) - 1)
test2 = Left(TailleColonne, Len(TailleColonne) - 1)
laChaine = Replace(TailleColonne, """", "")
laChaine = Replace(TailleColonne, Chr(160), "")
End With
'Récupération fichier
Filt = "Fichier Txt (*.txt),*.txt,"
Title = "Selectionnez un Fichier Txt a Importer : "
Filename = Application.GetOpenFilename(FileFilter:=Filt, Title:=Title)
If Filename = False Then
MsgBox "Aucun fichier choisi"
Exit Sub
End If
ActiveWorkbook.Worksheets.Add
'Traitement du fichier
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & Filename, Destination:=Range("A1"))
.Name = "fichier"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 1252
.TextFileStartRow = 1
.TextFileParseType = xlFixedWidth
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(FormatColonne) 'type de données
.TextFileFixedColumnWidths = Array(TailleColonne) 'largeurs des colonnes
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
'Purge données obsolètes
With Sheets("LISTE LONGUEUR ET CHAMPS")
.Range("a1:e" & .Range("A" & Rows.Count).End(xlUp).Row).ClearContents
End With
End Sub |
Partager