none
ActiveWorkbook.Queries RRS feed

  • Discussion générale

  • 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é

    jeudi 14 février 2019 07:49