Bonjour à tous,
J'utilise le code :
Sub QueryComptaHH()
ActiveWorkbook.Queries.Add Name:="Banque19", Formula:= _
"let" & Chr(13) & "" & Chr(10) & " Source = Excel.Workbook(File.Contents(""C:\MyMenu\Compta\CtaHH2019.xlsm""), null, true)," & Chr(13) & "" & Chr(10) & " Banque19_Sheet = Source{[Item=""Banque19"",Kind=""Sheet""]}[Data]," & Chr(13) & "" & Chr(10) & " #""Type modifié"" = Table.TransformColumnTypes(Banque19_Sheet,{{""Column1"", type any}, {""Column2"", type any}, {""Column3"", type any}, {""Column4"", type text}, {""Column5"", type any}, {""C" & _
"olumn6"", type number}, {""Column7"", type any}, {""Column8"", type text}, {""Column9"", type number}, {""Column10"", type number}, {""Column11"", type number}, {""Column12"", type any}, {""Column13"", type any}, {""Column14"", type any}, {""Column15"", type any}, {""Column16"", type any}, {""Column17"", type any}, {""Column18"", type any}, {""Column19"", type text}" & _
", {""Column20"", type text}, {""Column21"", type text}, {""Column22"", type number}})," & Chr(13) & "" & Chr(10) & " #""Autres colonnes supprimées"" = Table.SelectColumns(#""Type modifié"",{""Column1"", ""Column2"", ""Column3"", ""Column4"", ""Column5"", ""Column6""})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Autres colonnes supprimées"""
ActiveWorkbook.Sheets.Add After:=Worksheets("END")
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=Banque19;Extended Properties=""""", Destination:=Range("$A$1")).QueryTable
.CommandType = xlCmdSql
.CommandText = Array("SELECT * FROM [Banque19]")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "Banque19_2"
.Refresh BackgroundQuery:=False
'Name the worksheet shtcount%+1
ActiveWorkbook.ActiveSheet.Name = .ListObject.DisplayName
'save the name
str1$ = .ListObject.DisplayName
End With
srcsheet$ = ActiveSheet.Name
'
'FillSheetFinances
'nopflag% = 1
End Sub
je voudrais pouvoir introduire la source par un inputbox ...
Dim request$
request$ = InputBox("Fully pathed query source...", "Init query...", "") pour arriver à
ActiveWorkbook.Queries.Add Name:="Banque19", Formula:= _
"let" & Chr(13) & "" & Chr(10) & " Source = Excel.Workbook(File.Contents( request$ , null, true)," & Chr(13) & "" & Chr(10) & " Banque19_Sheet = Source{[Item=""Banque19"",Kind=""Sheet""]}[Data]," & Chr(13) & "" & Chr(10) & " #""Type modifié"" = Table.TransformColumnTypes(Banque19_Sheet,{{""Column1"", type any}, {""Column2"", type any}, {""Column3"", type any}, {""Column4"", type text}, {""Column5"", type any}, {""C" & _
"olumn6"", type number}, {""Column7"", type any}, {""Column8"", type text}, {""Column9"", type number}, {""Column10"", type number}, {""Column11"", type number}, {""Column12"", type any}, {""Column13"", type any}, {""Column14"", type any}, {""Column15"", type any}, {""Column16"", type any}, {""Column17"", type any}, {""Column18"", type any}, {""Column19"", type text}" & _
", {""Column20"", type text}, {""Column21"", type text}, {""Column22"", type number}})," & Chr(13) & "" & Chr(10) & " #""Autres colonnes supprimées"" = Table.SelectColumns(#""Type modifié"",{""Column1"", ""Column2"", ""Column3"", ""Column4"", ""Column5"", ""Column6""})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Autres colonnes supprimées"""
|
mais cela ne marche pas !!!
Merci d'avance pour votre aide,
Hervé
