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 NoChemin, LongNom As Integer, NomFich As String, NomficExt As String, NomfichSeul As String
NomFich = Application.GetOpenFilename _
(FileFilter:="Fichier Texte(*.csv),*.CSV", Title:="Sélectionner le fichier à importer")
If NomFich = Faux Then
MsgBox "Aucun fichier sélectionné."
Exit Sub
End If
NoChemin = Split(NomFich, "\")
NomFichExt = NoChemin(UBound(NoChemin))
MsgBox NomFichExt
LongNom = Len(NomFichExt)
MsgBox LongNom
LongNom = LongNom - 4
NomfichSeul = Left(NomFichExt, LongNom)
MsgBox NomfichSeul
NomFich = NomFich
MsgBox NomFich
ActiveWorkbook.Queries.Add Name:= _
"NomFichExt", Formula:= _
"let" & Chr(13) & "" & Chr(10) & " Source = Csv.Document(File.Contents(""NomFich""),[Delimiter="";"", Columns=6, Encoding=65001, 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 promus"",{{""Expression proches"", type" & _
" text}, {""Proximité"", type text}, {""Recherches Google"", Int64.Type}, {""Concurrence"", type number}, {""CPC Adwords"", type number}, {""Nb. de résultats"", Int64.Type}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Type modifié"""
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""NomFichExt""" _
, Destination:=Range("$A$1")).QueryTable
.CommandType = xlCmdSql
.CommandText = Array( _
"SELECT * FROM [NomFichExt]")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = _
"NomFichExt"
.Refresh BackgroundQuery:=False
End With
End Sub |
