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
| Sub Macro12()
' Macro12 Macro
nf = Application.GetOpenFilename
If nf = False Then
MsgBox ("Opération annulée")
Exit Sub
End If
Debug.Print nf
a = Split(Mid(nf, InStrRev(nf, "\") + 1), ".")(0)
Debug.Print a
nom = a & ".csv"
Debug.Print nom
ActiveWorkbook.Queries.Add Name:=a, _
Formula:= _
"let" & Chr(13) & "" & Chr(10) & " Source = Csv.Document(File.Contents = nf,[Delimiter="";"", Columns=14, Encoding=1252, QuoteStyle=QuoteStyle.None])," & Chr(13) & "" & Chr(10) & " #""En-têtes promus"" = Table.PromoteHeaders(Source, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & " #""Type modifié"" = Table.TransformColumnTypes(#""En-têtes p" & _
"romus"",{{""IDENTIFIANT"", type number}, {""BATCH"", type text}, {""TRAITE_LE"", type date}, {""REFERENCE_ANOMALIE"", type text}, {""CODE_GRAVITE"", type text}, {""LIBELLE_ANOMALIE"", type text}, {""E.R."", type text}, {""CONTRAT_INDIV"", type text}, {""CENTRE_GESTION"", type text}, {""GROUPE_GESTION"", type text}, {""CONTRAT_COLL"", type text}, {""GRP.ASS."", type " & _
"text}, {""NOUVEAU_MESSAGE"", type text}, {"""", type text}})," & Chr(13) & "" & Chr(10) & " #""Lignes filtrées"" = Table.SelectRows(#""Type modifié"", each Text.Contains([BATCH], ""AC007"") or Text.Contains([BATCH], ""AC008""))" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Lignes filtrées"""
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=a;Extended Properties=""""" _
, Destination:=Range("$A$1")).QueryTable
.CommandType = xlCmdSql
.CommandText = Array("SELECT * FROM [a]")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = a
.Refresh BackgroundQuery:=False
End With
End Sub |
Partager