Requete en VBA avec numero d onglet au lieu du nom?

Bonjour,

je cherche à récupérer les infos d une colonne nommée dans plusieurs fichiers d un dossier. j'ai une macro qui me gère la liste de fichier à utiliser. Ensuite j'ai enregistré une macro pour créer la requête allant chercher le fichier, récupérer uniquement l’onglet que je souhaite, puis la colonne. Mon problème est que le nom de cet onglet a évolué (maintenant en incluant la date, mais pas dans les premiers fichiers), mais c'est toujours le même numéro d onglet de chaque ficher, le premier. je souhaite donc remplacer dans ma macro le nom de l'onglet par quelque chose comme "sheet(1)", mais je n'y arrive pas. d’ailleurs je ne comprend pas ce qu'Excel écrit avec l'enregistreur de macro. voila mon exemple ci-dessous, l'onglet en question s’appelle ici "suivi" :

Sub Macro2()

ActiveWorkbook.Queries.Add Name:="Suivi", Formula:= _
"let" & Chr(13) & "" & Chr(10) & " Source = Excel.Workbook(File.Contents(""C:\Users\gc\Desktop\source.xlsx""), null, true)," & Chr(13) & "" & Chr(10) & " Suivi_Sheet = Source{[Item=""Suivi"",Kind=""Sheet""]}[Data]," & Chr(13) & "" & Chr(10) & " #""En-têtes promus"" = Table.PromoteHeaders(Suivi_Sheet, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & " #""Type modifié"" = Table.TransformColumnTypes(#""En-têtes promus"",{{""etat "", type text}, {""dossier" & _
""", Int64.Type}, {""date"", type date}})," & Chr(13) & "" & Chr(10) & " #""Autres colonnes supprimées"" = Table.SelectColumns(#""Type modifié"",{""etat ""})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Autres colonnes supprimées"""
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=Suivi;Extended Properties=""""" _
, Destination:=Range("$A$1")).QueryTable
.CommandType = xlCmdSql
.CommandText = Array("SELECT * FROM [Suivi]")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = False
.ListObject.DisplayName = "Suivi"
.Refresh BackgroundQuery:=False
End With
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
End Sub

Bonjour chnoub,

Votre problème peut être résolu en remplaçant le nom de l'onglet par l'index de l'onglet dans votre requête Power Query. Dans votre cas, si vous voulez toujours sélectionner le premier onglet, vous pouvez le faire en utilisant l'index 0 (les index en Power Query commencent à 0).

Voici comment vous pourriez modifier votre macro

Sub Macro2()

ActiveWorkbook.Queries.Add Name:="Suivi", Formula:= _
"let" & Chr(13) & "" & Chr(10) & " Source = Excel.Workbook(File.Contents(""C:\Users\gc\Desktop\source.xlsx""), null, true)," & Chr(13) & "" & Chr(10) & " Suivi_Sheet = Source{0}[Data]," & Chr(13) & "" & Chr(10) & " #""En-têtes promus"" = Table.PromoteHeaders(Suivi_Sheet, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & " #""Type modifié"" = Table.TransformColumnTypes(#""En-têtes promus"",{{""etat "", type text}, {""dossier" & _
""", Int64.Type}, {""date"", type date}})," & Chr(13) & "" & Chr(10) & " #""Autres colonnes supprimées"" = Table.SelectColumns(#""Type modifié"",{""etat ""})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Autres colonnes supprimées"""
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=Suivi;Extended Properties=""""" _
, Destination:=Range("$A$1")).QueryTable
.CommandType = xlCmdSql
.CommandText = Array("SELECT * FROM [Suivi]")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = False
.ListObject.DisplayName = "Suivi"
.Refresh BackgroundQuery:=False
End With
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
End Sub

La modification dans cette ligne :

" Suivi_Sheet = Source{0}[Data],"

Cela signifie que vous voulez utiliser le premier onglet de la liste d'onglets dans le fichier Excel. J'espère que cela vous aidera.

Merci beaucoup !