Sub PREV()
ActiveSheet.UsedRange.Clear
'convertir et extraire .txt
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Users\MVVD2765\Desktop\EXCEL 2\ATTACHEMENTS\REALISER\GASE-SELE_59147212.TXT" _
, Destination:=Range("$A$1"))
.Name = "OEIE.AMS_900210.txt"
.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 = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileOtherDelimiter = "|"
.TextFileColumnDataTypes = Array(1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
.Delete 'On supprime la Querytable
End With
'supprimer colonnes
Columns("A:B").Delete Shift:=xlToLeft
'supprimer ligne au dessus de asp
Dim p As Range
On Error Resume Next
Set p = Columns(1).Cells.Find("asp")
Rows("1:" & p.Row - 1).EntireRow.Delete
'supprime tout en dessous de f/mat
On Error Resume Next
Range(Cells(Rows.Count, 1), Columns(1).Cells.Find("F/Mat").Offset(1)).EntireRow.Delete
' On supprime les espaces blancs dans la colonne A
Range("A:A").Replace What:=" ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
'supprime ligne vides
Range("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delet
End Sub