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.

 

[Dudu2]

Le code résultat du mix @nullosse + @patricktoulon fonctionne très bien !

Par contre je ne comprends pas pourquoi il faut placer le Workbook.xml dans xmlFolder & " xl/workbook.xml" ?
Dans xmlFolder ça suffit pas ?

 

[patricktoulon]

re
@Dudu2
par ce que la commande "tar" dans windows contrairement a linux ne gère pas le strip-components
on ne peut donc pas allez chaercher
dossier/sousdossier/sousousdossier/fichier.xml
pour le transférer dans un dossier
dans windows on transfère la totalité de l'arborescence


ligne pour windows

cmd = "tar -xf """ & xlsxPath & """ -C " & tempFolder & " xl/workbook.xml"

ligne pour linux

cmd = "tar -xf """ & xlsxPath & """ -C """ & tempFolder & """ xl/workbook.xml --strip-components=1"

les bons points de cette commande malgré l'absence de gestion du strip-components:

1. même avec cette contrainte il semblerait que ce soit la plus rapide
2. on extrait directement du xlsx sans conversion "zip"

voila
patrick

 

[Dudu2]

Ok merci pour ton explication. Même si les Strip-Components me sont inconnus.
Ce Strip me tease un max !

 

[patricktoulon]

c'est une commande dans la commande "tar" qui permet de prendre l’tellement du bout de l'arborescence
en l'occurence ici je demande le fichier.xlsx / xl / workbook.xml
cette sous commande permet normalement de n'extraire que "workbook.xml" vers le dossier en paramètres dans la commande
je suis en train de tester une redirection sait on jamais avec un peu de chance
en tout cas elle est super rapide (benchmark: 22µf ) c'est même un peu plus rapide que l’écriture avec open fichier for output As Numberfile

même la version de laurent fait pas un pli à coté

 

[patricktoulon]

Bon ben j'avais raison la redirection est possible je faisais juste une petite erreur d'espace
j'ai du faire appel a deux IA pour trouver mon erreur 🙃 🙃 🙃 🤪 🤣
explication

shemas de la ligne de commande
cmd = "cmd /c tar -xOf """ & chemin_du_fichier_xlsx & """ Arboresence_dans_le_zip > """ & chemin_complet_de_destination & """"

cmd /c tar -xOf "c:\mondossier\fichier.xlsx" xl/workbook.xml > "C:\mondossier\workbook.xml"

1° cmd /c
cmd = lance l’interpréteur de commandes Windows.
/c = exécute la commande qui suit puis ferme.
Obligatoire ici, car les redirections (>, <, |) ne sont comprises que par cmd, pas par WScript.Shell.Run.

2 "tar"
C’est l’outil d’archivage. Sous Windows 10/11, c’est en réalité bsdtar fourni avec le système.

3 "xOf"
Ce sont les options de tar :

• -x → extract (extraire).
• -O (ou --to-stdout) → n’écrit pas dans un fichier/dossier, mais envoie le contenu sur la sortie standard (l’écran, ou une redirection).
• -f → indique le nom de l’archive qui suit (ici ton .xlsx).

- Donc -xOf "fichier.xlsx" signifie :
- Extraire depuis fichier.xlsx, mais envoie le résultat vers la sortie standard

4 fichier.xlsx
C’est le chemin de ton classeur (qui est en réalité un .zip).
Attention : un .xlsx est une archive ZIP, et l’arborescence interne est respectée (xl/workbook.xml, xl/worksheets/sheet1.xml, etc.).

5 xl/workbook.xml
C’est le chemin interne du fichier à extraire, tel qu’il est dans l’archive ZIP.
xl/ est le dossier interne,
workbook.xml est le fichier de définition des feuilles/classeurs.

6 > "workbook.xml"
C’est la redirection de la sortie standard(stdout) vers un fichier.
crée/écrase le fichier de sortie.

du coup la fonction ça donne ceci

Sub testv5()
    xlsxPath = "C:\Users\patricktoulon\Desktop\Classeur1.xlsx"
    MsgBox Join(ListfeuilleXml(xlsxPath), vbCrLf)
End Sub


Function ListfeuilleXml(xlsxPath)
    Dim tempFolder As String, xmlPath As String
    Dim xmlContent As String
    Dim cmd As String, objShell As Object
    
    ' Chemin de ton fichier Excel fermé
    ' Dossier temporaire
    tempFolder = ThisWorkbook.Path
    
    xmlPath = tempFolder & "\workbook.xml"
    ' --- Étape 1 : Extraction de workbook.xml avec tar ---
    
    ' Extraire directement le contenu du fichier sans créer l'arborescence
    cmd = "cmd /c tar -xOf """ & xlsxPath & """ xl/workbook.xml > """ & xmlPath & """"
        
    Set objShell = CreateObject("WScript.Shell")
    result = objShell.Run(cmd, 0, True)
    If result <> 0 Then ListfeuilleXml = Array(): Exit Function
    DoEvents
    X = FreeFile
    Open xmlPath For Binary Access Read As #X: xmlContent = String(LOF(X), " "): Get #X, , xmlContent: Close #X
    
    t = Split(xmlContent, "<sheet name=""") 'on coupe le texte par les ouvertures de balise "sheet"
    ReDim tx(1 To UBound(t) + 1) 'on dimensionne un tableau de même taille que le split EN BASE 1!!!!!
    For I = 1 To UBound(t)
        tx(I) = Split(t(I), """")(0) 'on prends que la partie qui nous intéresse donc ni plus ni moins que l'attribut name
    Next
    ListfeuilleXml = tx 'le return c'est le tableau tx
    Kill xmlPath 'on supprime le xml temporaire qui a été extrait
    
End Function

et la pour le coup c'est hyper rapide

j'invite @nulloss a se raviser pour la ligne de commande histoire de ne pas etre obligé de delete des folder et killer des fichier le kill du xml suffit à la fin c'est même mieux que ma première version avec tar

Patrick

 

[nullosse]

En "plus propre" que les Splits pour extraire les noms des feuilles du xml en utilisant les expressions régulières :

Sub ExtraireNomFeuille(xmlstring)
    Dim regEx As Object, matches As Object, match As Variant, sheetNames As String
    ' Créer une instance de l'objet RegExp
    Set regEx = CreateObject("VBScript.RegExp")
    ' Définir l'expression régulière pour capturer tous les noms de feuille
    regEx.IgnoreCase = True: regEx.Global = True
    regEx.Pattern = "sheet name=""([^""]+)"""
    ' Trouver toutes les correspondances
    Set matches = regEx.Execute(xmlstring)
    ' Afficher les résultats dans une boîte de message ou dans la fenêtre Immediate
    For Each match In matches
        sheetNames = sheetNames & match.SubMatches(0) & vbCrLf
    Next match
    ' Afficher les noms des feuilles extraits
    If Len(sheetNames) > 0 Then
        MsgBox "Noms des feuilles : " & vbCrLf & sheetNames
    Else
        MsgBox "Aucun nom de feuille trouvé."
    End If
    Set regEx = Nothing
End Sub

je ne me ravise pas , j'évolue , j'ai seulement lancer l'idée du tar et j'adopte le dernier code de patricktoulon pour le tar

 

[patricktoulon]

re oui le regex aussi
mais c'est un lourdaud faut pas oublier et ce n'est pas plus propre c'estune méthode différente avec un object supplémentaire
et vu quand même que la vitesse d’exécution est quand même un critère important

et suites a tous ces test je crois qu'on peut laisser tomber la ligne de commande sur powershell qui est très longue en terme de temps d’exécution
ci joint mes 3 choix de méthodes les plus rapides +la dernier de powershell qu'on laissera tomber sans regret vous en conviendrez

avec ça @Dudu2 a le choix

patrick

 

[Dudu2]

C'est bien, vous avez bien travaillé

.
Je pense qu'avec les 3 méthodes opérationnelles on peut considérer que le sujet est répondu.
Perso je les ai conservées sous forme de fichiers texte.

Edit: Fichier mis à jour suite aux problèmes de jeu de caractère UTF8. 1 seule solution retenue.

 

[nullosse]

ci joint mes 3 choix de méthodes les plus rapides +la dernier de powershell qu'on laissera tomber sans regret vous en conviendrez

A noter qu'avec la deuxième méthode ( tar + parse par Split) il y a un souci quand les noms des feuilles ont des accents. Le xml est en utf8 . Il n'y a pas le problème avec Ado car celui-ci traite l'utf8 et pas non plus avec la troisième méthode car on charge le xml et c'est le parseur de MSXML2.DOMDocument qui se charge de traiter l'utf8

 

[Dudu2]

En effet, vérifié, bien vu !
Et si les feuilles ont des noms avec accents, ça retourne les doubles caractères de l'UTF8.
Que faut-il faire alors ? Créer un classeur temporaire copie de l'original et sans accents dans le nom ? Et mémoriser les noms originaux des feuilles ?

 

[nullosse]

Que faut-il faire alors ? Créer un classeur temporaire copie de l'original et sans accents dans le nom ? Et mémoriser les noms originaux des feuilles ?

Utiliser un convertisseur utf8 vers ansi mais la méthode 3 de patricktoulon me semble pas mal car il n'y a pas ce problème. J'ai des soucis avec certains classeurs j'ai des lignes blanches dans le nom des feuilles, cela fonctionne mieux avec ceci pour le parse de la 3 eme methode :

   xDoc.Load fichierxml
    ' Ajouter espace de noms pour les feuilles
    xDoc.SetProperty "SelectionNamespaces", "xmlns:ss='http://schemas.openxmlformats.org/spreadsheetml/2006/main'"
    ' Compter les feuilles pour redimensionner le tableau
    Dim nbFeuilles As Integer
    nbFeuilles = xDoc.SelectNodes("//ss:sheets/ss:sheet").Length
    ReDim TbL(1 To nbFeuilles)
    X = 1
    ' Parcourir les noeuds <sheet>
    For Each noeud In xDoc.SelectNodes("//ss:sheets/ss:sheet")
        TbL(X) = noeud.Attributes.getNamedItem("name").Text
        X = X + 1
    Next

On doit pouvoir dans cette méthode remplacer la première partie par un tar

 

[patricktoulon]

re;
pas de soucis on l'ouvre tout simplement en utf_8

'patricktoulon

Sub testv5()
    xlsxPath = "C:\Users\patricktoulon\Desktop\Classeur1.xlsx"
    MsgBox Join(ListfeuilleXml(xlsxPath), vbCrLf)
End Sub


Function ListfeuilleXml(xlsxPath)
    Dim tempFolder As String, xmlPath As String
    Dim xmlcontent As String
    Dim cmd As String, objShell As Object
   
    ' Chemin de ton fichier Excel fermé
    ' Dossier temporaire
    tempFolder = ThisWorkbook.Path
   
    xmlPath = tempFolder & "\workbook.xml"
    ' --- Étape 1 : Extraction de workbook.xml avec tar ---
   
    ' Extraire directement le contenu du fichier sans créer l'arborescence
    cmd = "cmd /c tar -xOf """ & xlsxPath & """ xl/workbook.xml > """ & xmlPath & """"
       
    Set objShell = CreateObject("WScript.Shell")
    result = objShell.Run(cmd, 0, True)
    If result <> 0 Then ListfeuilleXml = Array(): Exit Function
    DoEvents
    'X = FreeFile
    'Open xmlPath For Binary Access Read As #X: xmlcontent = String(LOF(X), " "): Get #X, , xmlcontent: Close #X
   Dim stream as object 
    Set stream = CreateObject("ADODB.Stream")
        With stream
        .Type = 2 ' adTypeText
        .Charset = "utf-8"
        .Open
        .LoadFromFile xmlPath
        xmlcontent = .ReadText
        .Close
    End With
   
    t = Split(xmlcontent, "<sheet name=""") 'on coupe le texte par les ouvertures de balise "sheet"
    ReDim tx(1 To UBound(t) + 1) 'on dimentionne un tablkeau de même taile que le split EN BASE 1!!!!!
    For I = 1 To UBound(t)
        tx(I) = Split(t(I), """")(0) 'on prends que la partie qui nous interesse donc ni plus ni moins que l'attribut name
    Next
    ListfeuilleXml = tx 'le return c'est le tableau tx
    Kill xmlPath 'on supprime le xml temporaire qui a été extrait
   
End Function

 

[patricktoulon]

et si vous y tenez

1. version Ado
2. version Tar +split text Utf_8
3. version Tar+parser domdocument
4. version shell.automation+parser dans domdocument
5. version brouillon de powershell

edit :j'oubliais
la version 3 TarV2 avec le domdocument garantie l'ordre exact !!,le code du parser c'est le même que la version shell.automation
Patrick

 

[Dudu2]

Ok, bravo ! Perso je sais pas du tout faire ça.
Donc j'ai mis ton post comme solution mais d'un point de vue pratique je ne retiens toujours que les 3 solutions testées / vérifiées du Post 83.

 

[patricktoulon]

j'ai vu que tu a modifié le #83 avec le stream en utf8
apres ce sont des codes assez simples
c'est les bases pour le xml ou html d'ailleurs
sauf que la effectivement avec les parseurs on travaille en Xpath ( xDoc.SelectNodes("//ss:sheet") )
on pourrait travailler en dom pur aussi (getelementsbytagname("sheet")) en modifiant le shemas
c'est du pareil au même ,perso j'ai une préférence pour le dom pur

a part peut être la ligne de commande que j'ai détaillé plus haut le reste tu sais faire tu a deja utilisé

étonnamment au test benchmark c'est la version 2 Tar v1 qui, est plus rapide que tout les autres d'ailleurs les modules sont classés dans l'ordre de rapidité sauf pour la version ADO
en vrai de la plus rapide à la plus lente
version v2 tar v1
version 3 tar v2
version 1 Ado
version 4 shel.automation

cette discussion nous montre bien que parfois sortir des sentier battus a du bon

je rends hommage à @laurent950 quand même qui est le premier a s’être lancé dans cette direction XML

quand on me parle du Dom/VBA moi je réponds toujours présent 😉 🤪 🤪
y a que la vaisselle que j'ai pas réussi à faire avec