XL 2016 VBA - Trouver les feuilles d'un classeur fermé

[Dudu2]

Bonjour,

J'ai bien une fonction pour trouver les valeurs d'une feuille donnée d'un classeur fermé (fichier joint) mais je n'ai rien pour trouver les noms des feuilles de ce classeur fermé.

Merci par avance.

 

[dysorthographie]

Bonjour,

Base de données Discussion 'Module Requeteur Universel'

17 Septembre 2023

Bonjour,

Je me suis longuement posé la question de savoir si je devais publier cette source.

En effet de plus en plus nous trouvons sur les forum des adeptes de Power Query qui ne voient pas d'un bon œil l'utilisation d’ADO.

Personnellement je dispose d’Office 2007 je n’ai pas accès à Power Query.

Ayant trouvé, sur ce même forum, des utilisateurs intéressés par ma proposition m’ont convaincu de poster ma solution.

Ici l’idée est de proposer un requêteur universel, permettant d’exécuter des requêtes sur n’importe quel type de base de données.

Commençons par la connexion aux base de...

• dysorthographie
• Réponses: 0
• Forum: Fonctions personnalisées VBA

• 
• 

Tu devrais trouver Public Function ListeTables(Connexion As Variant) As String() qui retourne toutes les tables d'un xls fermé Mac s'abstenir.

 

[Dudu2]

Merci @dysorthographie.

Tu te ballades dans ces connexions aux bases de données mais perso je n'y capte pas grand chose.
Aussi ai-je tenté d'adapter la fonction ListeTables() un peu au hasard mais manifestement sans grand succès.
Toutes mes variantes se plantent.

Private Function ListeTables(ByVal Chemin As String, _
                             ByVal Fichier As String) As String()
    Dim Connexion As String
    Dim TBL() As String
    Dim t As Variant
    Dim i As Integer
   
    Connexion = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Chemin & "\" & Fichier & ";Extended Properties=""Excel 12.0;HDR=NO;IMEX=1"";"
   
    With CreateObject("ADODB.Catalog")
        .ActiveConnection = Connexion
       
        For Each t In .Tables
            ReDim Preserve TBL(i)
            TBL(i) = t.Name
            i = i + 1
        Next
    End With
   
    ListeTables = TBL
End Function

 

[nullosse]

Salut,
en utilisant la connexion déjà faite à essayer :

   Set RecordSet = Connexion.OpenSchema(20) ' adSchemaTables
    Do Until RecordSet.EOF
        If Right(Replace(RecordSet!TABLE_NAME, "'", ""), 1) = "$" Then
           Debug.Print Replace(Replace(RecordSet!TABLE_NAME, "$", ""), "'", "")
        End If
        RecordSet.MoveNext
    Loop

on regarde si le dernier caractère est $ car on sinon on se chope aussi les plages nommées , les tableaux.
on enlève les ' si il y en a (quand le nom des feuilles contient des blancs)

Nullosse

 

[dysorthographie]

Bonjour,
Dans le lien que j'ai fourni je donne des exemples de connections. Tu as la fonction GenereCSTRING qui te permet de créer une connexion sans rien y connaître.

ModuleRequeteurUniversel contient tout ce qui est utile pour jouer avec une base de données que ce soit SQL serveur, Oracle, MySQL, Excel, CSV etc.

GenereCSTRING te retourne le conectionString en fonction du type te base de données à traiter, dans notre cas xls. Notes que des paramètres optionnel permettent de choisir les informations en fonction du type de base. Serveur par exemple sera utile pour SQL serveur, Oracle, MySQL.

Sub test()
Dim CN As Object, Tables() As String
Set CN = CreateObject("Adodb.connection")
CN.Open GenereCSTRING(Xls, Base:=ThisWorkbook.FullName, Titre:=True)
Tables() = ListeTables(CN)
CN.Close
Set CN = Nothing
End Sub

Bien sur si tu voulais faire des choses plus pechu il te faudrait avoir des notions SQL mais la on en est louin.

Ceci dit une fois qu'on a goûté à SQL on peut plus sans passer.

Je sais pas si les adeptes de powerQery on l'a réponse

Notes que catalogue n'est pas dans adodb mais dans adox

Public Function ListeTables(Connexion As Variant) As String()
Dim TBL() As String, I As Integer
With CreateObject("ADOX.Catalog")
    .ActiveConnection = Connexion
    For Each T In .Tables
        ReDim Preserve TBL(I)
        TBL(I) = T.Name
        I = I + 1
    Next
End With
    ListeTables = TBL
End Function

 

[patricktoulon]

Bonjour
prêt à l'emploi

Sub test()
    Dim listsheet, filepath$
    'adapter le chemin ici ou injecter un dialog getopenfilename
    filepath$ = "C:\Users\patricktoulon\Desktop\Nouveau Feuille de calcul Microsoft Excel.xlsx"
    listsheet = ListSheetOnClosedFile(filepath)
    MsgBox Join(listsheet, vbCrLf)
End Sub
Function ListSheetOnClosedFile(lPath As String)
    'patricktoulon collection Ado
    Dim Connection As Object, recordST As Object, tbl(), a&
    Set Connection = CreateObject("ADODB.Connection") ' Créer connexion ADO
    ' Ouvrir connexion (type de connection pour les version superieures a 2003/2007 et +)
    Connection.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & lPath & ";" & "Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
    Set recordST = Connection.OpenSchema(20) ' Récupérer la liste des feuilles ' 20 = adSchemaTables
    Do Until recordST.EOF
        a = a + 1
        ReDim Preserve tbl(1 To a)
        tbl(a) = Replace(recordST!TABLE_NAME, "$", "")
        recordST.MoveNext
    Loop
    recordST.Close: Connection.Close: Set recordST = Nothing: Set Connection = Nothing
    ListSheetOnClosedFile = tbl
End Function

 

[patricktoulon]

je corrige
le openshema 20 liste les plagenomées aussi ainsi que les ts
donc on conditionne sur le "$"

Sub test()
    Dim listsheet, filepath$
    'adapter le chemin ici ou injecter un dialog getopenfilename
    filepath$ = "C:\Users\patricktoulon\Desktop\Nouveau Feuille de calcul Microsoft Excel.xlsx"
    listsheet = ListSheetOnClosedFile(filepath)
    MsgBox Join(listsheet, vbCrLf)
End Sub
Function ListSheetOnClosedFile(lPath As String)
    'patricktoulon collection Ado
    Dim Connection As Object, recordST As Object, tbl(), a&
    Set Connection = CreateObject("ADODB.Connection") ' Créer connexion ADO
    ' Ouvrir connexion (type de connection pour les version superieures a 2003/2007 et +)
    Connection.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & lPath & ";" & "Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
    Set recordST = Connection.OpenSchema(20) ' Récupérer la liste des feuilles ' 20 = adSchemaTables
    Do Until recordST.EOF
        If recordST!TABLE_NAME Like "*$" Then
            a = a + 1
            ReDim Preserve tbl(1 To a)
            tbl(a) = Replace(recordST!TABLE_NAME, "$", "")
        End If
        recordST.MoveNext
    Loop
    recordST.Close: Connection.Close: Set recordST = Nothing: Set Connection = Nothing
    ListSheetOnClosedFile = tbl
End Function

 

[laurent950]

Bonjour Le Forum

sans créer une connexion ADO

Sub FeuillesParXML()
    Dim chemin As String, tempZip As String, tempFolder As String
    Dim xmlPath As String
    Dim xDoc As Object, noeud As Object
    
    chemin = "C:\Users\suiteduchemin\Downloads\LeFichierExcel.xlsx"
    tempZip = Environ("TEMP") & "\classeur.zip"
    tempFolder = Environ("TEMP") & "\classeur_xml"
    
    ' Supprimer anciens fichiers si existants
    If Dir(tempZip) <> "" Then Kill tempZip
    On Error Resume Next
    Kill tempFolder & "\xl\workbook.xml"
    On Error GoTo 0
    
    ' Copier et renommer en zip
    FileCopy chemin, tempZip
    
    ' Extraire le zip
    Shell "powershell -command ""Expand-Archive -Path '" & tempZip & "' -DestinationPath '" & tempFolder & "' -Force""", vbHide
    
    ' Attendre que le fichier workbook.xml existe
    xmlPath = tempFolder & "\xl\workbook.xml"
    Do While Dir(xmlPath) = ""
        DoEvents
    Loop
    
    ' Charger le XML
    Set xDoc = CreateObject("MSXML2.DOMDocument.6.0")
    xDoc.async = False
    xDoc.Load xmlPath
    
    ' Ajouter espace de noms pour les feuilles
    xDoc.SetProperty "SelectionNamespaces", "xmlns:ss='http://schemas.openxmlformats.org/spreadsheetml/2006/main'"
    
    ' Parcourir les feuilles
    For Each noeud In xDoc.SelectNodes("//ss:sheet")
        MsgBox noeud.Attributes.getNamedItem("name").Text
    Next
End Sub

 

[patricktoulon]

re
je corrige encore (décidément pas bien réveillé moi ce matin)
je rajoute une securité sur le ubound au cas ou la connexion aurait plantatatouillé

Option Explicit
Sub test()
    Dim listsheet, filepath$
    'adapter le chemin ici ou injecter un dialog getopenfilename
    filepath$ = "C:\Users\patricktoulon\Desktop\Nouveau Feuille de calcul Microsoft Excel.xlsx"
    listsheet = ListSheetOnClosedFile(filepath)
    If UBound(listsheet) > 0 Then
        MsgBox Join(listsheet, vbCrLf)
    Else
        MsgBox "La connection au classeur n'a pas pu lister les feuille du classeur"
    End If
End Sub
Function ListSheetOnClosedFile(lPath As String)
    'patricktoulon collection Ado
    Dim Connection As Object, recordST As Object, tbl(), a&
    Set Connection = CreateObject("ADODB.Connection") ' Créer connexion ADO
    ' Ouvrir connexion (type de connection pour les version superieures a 2003/2007 et +)
    Connection.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & lPath & ";" & "Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
    Set recordST = Connection.OpenSchema(20) ' Récupérer la liste des feuilles ' 20 = adSchemaTables
    Do Until recordST.EOF
        If recordST!TABLE_NAME Like "*$" Then
            a = a + 1
            ReDim Preserve tbl(1 To a)
            tbl(a) = Replace(recordST!TABLE_NAME, "$", "")
        End If
        recordST.MoveNext
    Loop
    recordST.Close: Connection.Close: Set recordST = Nothing: Set Connection = Nothing
    If a = 0 Then
        ListSheetOnClosedFile = Array() ' on met un tableau vide pour eviter l'erreur dans la sub appelante
    Else
        ListSheetOnClosedFile = tbl
    End If
    ListSheetOnClosedFile = tbl
End Function

pour le coup là tu a une fonction complète et valide

explication
mais comment ça marche dis donc: 😉 🙃

ado ne connais pas la notion de feuille plage,etc il lit les classeurs une base de données tabulaire
toutes les feuilles du classeur pour lui est vue comme une table.
toutes les plage nommée dans le classeur est aussi vue comme une table.
donc quand tu fais un OpenSchema(adSchemaTables),
ADO retourne la liste de toutes les tables qu’il connait , donc les feuilles et les plages nommées.et le TS
voilà
Patrick

 

[Dudu2]

Bonjour @dysorthographie,

Merci pour ton exemple qui m'a éclairé sur la méthode.
J'avais bien vu la fonction GenereCSTRING mais je n'en avais pris que la partie XLS.
Je récupère des noms de feuilles terminés par "$".

Bien sur si tu voulais faire des choses plus pechu il te faudrait avoir des notions SQL mais la on en est louin.
Ceci dit une fois qu'on a goûté à SQL on peut plus sans passer.

Je ne suis pas complètement étranger au SQL, même si ma formation sur le sujet est très très lointaine.
Ce que je ne vois pas trop, à part extraire des "records" sous éventuelles conditions d'une feuille Excel (ce qui est déjà pas mal !) c'est à quoi d'autre on peut l'utiliser pour Excel.

 

[patricktoulon]

@laurent950
à ben celle là j'aurais pas osé
là tu m’épate Laurent😛🤣👍

 

[Dudu2]

Bonjour @patricktoulon
Merci pour ton code qui fonctionne parfaitement.

 

[Dudu2]

Bonjour @laurent950,
Merci pour cette méthode vraiment originale mais parfaitement fonctionnelle.

 

[Staple1600]

Bonjour le fil

Je sais pas si les adeptes de powerQery on l'a réponse

Une alternative avec PowerQuery

let
    Source = Excel.Workbook(File.Contents("C:\Chemin\Vers\Fichier.xlsx"), null, true),
    FeuillesSeulement = Table.SelectRows(Source, each [Kind] = "Sheet"),
    NomsDesFeuilles = Table.SelectColumns(FeuillesSeulement, {"Name"})
in
    NomsDesFeuilles

PS: adapter le chemin vers le fichier dans le code M

 

[dysorthographie]

Bonjour @dysorthographie,

Merci pour ton exemple ....
Ce que je ne vois pas trop, à part extraire des "records" sous éventuelles conditions d'une feuille Excel (ce qui est déjà pas mal !) c'est à quoi d'autre on peut l'utiliser pour Excel.

En fait tu peux lire des données dans excel avec jointure entre feuilles, ajouter des données dans une feuille, modifier des données dans un feuille.

Tu ne peux pas ajouterde feuilles n'y en supprimer, pas supprimer des lignes dans une feuille car bien que tu puisses utiliser ado pour manipuler excel comme une base de données sa structure ne reste pas moins déferlante a une base de données SGBD.

Il y a des requêtes de sélection qui retourne un recordset et et des requêtes d'action comme insert, update.

Notes également que bien qu'un recordset soit le retour d'une sélection tu peux utiliser selon la configuration de celui-ci sa méthode addnew pour ajouter une ligne dans ta feuille excel et sa méthode update pour modifier une ligne existante.