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,
Il faut noter q'un certain nombre de caractères pouvant être utilisé pour nommer les champs peuvent être des caractères ayant une signification du point de vue syntaxique en SQL.

Notez qu'il ne s'agit pas exclusivement d'ACCESS mais SQL en général . Je ne rentrerai pas sur les particularités des différents types de base de données (ACCESS, SQL SERVEUR, ORACLE Etc.)

Le point par exemple représente une déclinaison base.table.champs , table.champs.

On devra utiliser un caractère de substitution normé en fonction du driver utiliser.
En général table.champ#1 # en substitution du point.

Désolé pour ceux qui m'ont reproché d'utiliser chatgpt si je lui est demander la liste des caractères d'échappement.

---

Liste des caractères de substitution / échappement en pratique​

Remplace un caractère problématique	Caractères utilisés comme substitution
. (point)	#, _, $
espace	_
- (tiret)	_
+	_
/	_
\	_
!	_, parfois laissé tel quel
?	_
@	_, AT
&	_, AND
autres caractères spéciaux (%, *, (, ))	_ ou supprimés

​

 

[patricktoulon]

re
es tu sur de bien pointer le début au bon moment et de bien pointer les fins aussi

dans le module 1 je lance testallversionbenchmark et
ça va ouvrir le dialogue de selection de fichier
le fichier sera le meme pour les 4 fonction qui vont être executées à la suite
je pointe le départ avant chaque appel
je pointe la fin après le return
le msgbox et je ne sais quoi d'autre ,c'est après on le compte pas
ici on pointe le moteur par le reste
clique ok a chaque msgbox jusqu'a la fin
et regarde dans la console

ci joint mon classeur de tests

Patrick
envoie ton rapport de la console que je verifie les tics

 

[patricktoulon]

pourquoi la Tar V1 (Tar+split est la plus rapide ?
et bien c'est assez simple à comprendre
il n'y a que 2 etape bloquantes
le result sur le shell (Attendu)(c'est le plus long mais est visiblement très rapide)
le code entier xml du readtext du stream(attendu)(c'est très rapide )
et en dernier la boucle pour stoker le split valides (finalement la plus longue partie c'est bien celle ci même si elle est tres rapide )

avec Ado
ouverture du moteur Ace (benchmark distance moyenne)
connection avec shema table (benchmark distance moyenne)
lecture OBLIGATOIRE !!!!!! jusqua la fin EOF(donc la on lit tout sheet table de donnée etc... et tout autres choses encore pas découvertes)
là parcontre on est dans une distance(aléatoire en fonction du nombre de table (benchmark distance Longue pour les deux raisons )
donc par definition
la Ado ne peut être plus rapide que la

d'ailleurs dans les raport que j'ai publié précédamment quand on regarde les Tics qui ont été comptés sur le pointage de fin on comprends tout de suite

et de toute manière l'ecart entre les deux est tellement grand( à l'echelle des deux bien sur on parle là de milli seconde et mico seconde ) qu'il n'y a pas ambiguïté

concernant les caractères particuliers
sachant que un caractère de remplacement peut en remplacer plusieurs autres je ne saurais que trop conseiller d'éviter les caractères particulier autres que les accentués

 

[Dudu2]

De toutes façons les versions ADO ne gèrent pas tous les caractères acceptés en nom de feuille me semble-t-il.

 

[nullosse]

ci joint mon classeur de tests

bon j'ai compris pourquoi tu avais un temps supersonique pour le test v2 tar voici ton code :

Sub testv2()
    Dim bm As New cBenchmark
    Debug.Print "Test de : ListfeuilleXmlTarV1 avec Tar+ split "
    bm.TrackByName "debut ListfeuilleXmlTarV1"
    xlsxPath = fichier
    bm.TrackByName "fin ListfeuilleXmlTarV1"
    MsgBox Join(ListfeuilleXmlTarV1(xlsxPath), vbCrLf)
End Sub

En fait le temps que tu mesures c'est le temps du xlsxPath = Fichier
le TrackByName ne mesure pas ce qu'il y a après mais ce qu'il y a avant.
En fait il faut lancer le bench avec un bm.Start et mettre le TrackByName juste après ce que l'on veut mesurer.
Voici comment je mesure :

Sub testv2()
    Dim xlsxPath$, bm As New cBenchmark, outStr$
    bm.Start
    xlsxPath = FicTest
    outStr = Join(ListfeuilleXmlTarV1(xlsxPath), vbCrLf)
    bm.TrackByName "v2"
    MsgBox outStr
End Sub

 

[patricktoulon]

@Dudu2 c'est un des soucis effectivement
d’où mes deux propositions tar+split ou tar+dom document parsing
l'une allant très vite et l'autre un peu moins mais garantie l'ordre exacte des feuilles

en tout cas pour moi c'est plié la tar +split reste la meilleure solution dans une certaine mesure
et le fait que j'ai réussi a extraire uniquement le xml dans la ligne de commande avec la redirection du stdout ou"O" vers fichier
a grandement accéléré le reste du code puisque plus de subfolder et tout le toutim

la oui tu peux dire que l'on a bien travaillé
testé
éprouvé
argumenté
validé
merci Laurent pour l'idée du Tar
merci moi pour être têtu comme un manche de pioche et ne rien lâcher 😉 🤣 🤣 🤪

ps: je suis en train de travailler sur le stdout >clip pour me passer du fichier
affaire à suivre je ne garantie rien
je me suis replongé dans mes vieux travers de ligne de commande 🤪🤪

 

[Dudu2]

Je ne sais pas ce que donne le PowerQuery car je n'y connais rien et je ne sais pas le mettre en place.

@patricktoulon,
Bon on ne vas garder que cette version donc. Peut-être bientôt modifié sur le redirection du stdout.

 

[nullosse]

De toutes façons les versions ADO ne gèrent pas tous les caractères acceptés en nom de feuille me semble-t-il.

Salut Dudu2.
En fait dans les noms d'onglets on met surtout des caractères alphanumériques, le seul caractère qui peut poser problème c'est le . (qui est alors remplacé par ado par un #).

 

[nullosse]

Je ne sais pas ce que donne le PowerQuery car je n'y connais rien et je ne sais pas le mettre en place.

En pièce jointe , un classeur comportant la requête PQ de Cousinhub.
Il suffit de mettre dans la feuille 1 le Chemin du classeur à tester et d'appuyer sur le bouton pour avoir le résultat et le temps d'exécution.
La première fois c'est toujours très long car il faut que le moteur PowerQuery se charge.
Puis après c'est de plus en plus rapide jusqu'à arriver à un temps mini. Il y a peut-être un effet de cache et Cousinhub en sait peut-être plus à ce sujet.

 

[nullosse]

@Dudu2
la oui tu peux dire que l'on a bien travaillé
argumenté

là il va falloir que tu te remettes en cause

 

[patricktoulon]

là il va falloir que tu te remettes en cause

non je pense pas
mes rapports sont clairs et argumentés avec benchmark
par contre je n'ai pas encore vu les tiens (entre nous)

et pour le fun allez c'est parti la même sans fichier intermédiaires on passe par le clip
le dataobject en latebinding(toute versions excel)pas de ref à activer

'
'patricktoulon
'version sans fichier intermédiaire
Sub testv6()
    'Dim bm As New cBenchmark
    'Debug.Print "Test de : ListfeuilleXmlTarV1 avec Tar+ split "
    'bm.TrackByName "debut ListfeuilleXmlTarV3"
    xlsxPath = "C:\Users\patricktoulon\Desktop\Classeur1.xlsx"
    'bm.TrackByName "fin ListfeuilleXmlTarV3"
    MsgBox Join(ListfeuilleXmlTarV3(xlsxPath), vbCrLf)
End Sub


Function ListfeuilleXmlTarV3(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
    
    
    ' Extraire directement le contenu du fichier sans créer l'arborescence et DIRECTEMENT DANS LE CLIP!!!!!
    cmd = "cmd /c tar -xOf """ & xlsxPath & """ xl/workbook.xml | clip"
    
    Set objShell = CreateObject("WScript.Shell")
    result = objShell.Run(cmd, 0, True) 'visiblement pas d'erreur mais ça bloque ici
    If result <> 0 Then ListfeuilleXmlTarV3 = Array(): Exit Function
    DoEvents
    
    'récupération du code XML dans le clipboard
    With CreateObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}") 'LATEBINDING
        .GetFromClipboard
        xmlcontent = .GetText
    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
    ListfeuilleXmlTarV3 = tx 'le return c'est le tableau tx
    
End Function

c'est le même temps (à une ou deux µs près) que le même avec le fichier intermédiaire (donc au final de ce coté on gagne rien ou presque)
sauf que l'on a pas de problème en cas d'autorisation utilisateur sur un dossier ou fichier
(ca par contre c'est une contrainte en moins)et pas des moindres

voila
Patrick
je pose le benchmark au cas ou il faudrait que je me remette en cause

🤣 🤣

Test de : ListfeuilleXmlTarV3 avec Tar+ split
IDnr  Name                       Count  Sum of tics  Percentage  Time sum
0     debut ListfeuilleXmlTarV3      1          106      82,81%     11 us
1     fin ListfeuilleXmlTarV3        1           22      17,19%   2200 ns
      TOTAL                          2          128     100,00%     13 us

Total time recorded:             13 us

 

[nullosse]

Bon je n'interviens plus ici car patricktoulon veut toujours avoir raison même quand il a tort. Quand on utilise mal quelque chose ( ici cBenchmark) on fait profil bas.

 

[Dudu2]

@nullosse

En fait dans les noms d'onglets on met surtout des caractères alphanumériques

C'est vrai mais pour le principe le code doit couvrir tous les cas au risque un jour de tomber sur un bug cause pas le bon nom de feuille.

Merci pour le classeur avec PQ. Je suis infoutu de trouver où est stockée la requête ! Ces ListObjects sont invisibles

Edit: ok, faut aller dans les données...

La 1ère exécution est très longue sur mon petit laptop. Par contre les caractères spéciaux et l'ordre des feuilles sont gérés.

 

[patricktoulon]

Bon je n'interviens plus ici car patricktoulon veut toujours avoir raison même quand il a tort. Quand on utilise mal quelque chose ( ici cBenchmark) on fait profil bas.

Lol
je tamponne le debut avant l'appel
je tamponne la fin apres le return

Il n'y a pas de mauvaise utilisation je pense que c'est toi qui n'a pas compris comment il fonctionne

d'ailleurs cette classe on la décortiqué il y a un moment de ca si tu cherche bien la discussion tu devrais trouver des new qui vont t'intéresser
je crois même que @Dudu2 faisait partie de la discussion

il y a effectivement un effet de bord minime en fonction de l'ordre des tests(ou des fonctions testées) mais ca reste un indicateur bien pratique
si on veux améliorer les performances d'un code

a cet instant précis j’attends toujours tes rapports

Bon je n'interviens plus ici car patricktoulon veut toujours avoir raison même quand il a tort.

au final tu a un peu raison mais pas pour les raisons que tu crois
quand on fourni pas les preuves A+B comme je le fait moi alors oui on intervient plus
d'ailleurs ce genre de remarque est digne d'un blaireau des montagnes russes quand il n'a plus d'arguments pour se défendre
d'ailleurs il n'est ici nullement question de se défendre mais d'échanger (tu vois le concept?)
alors que tout ce passe bien depuis le début
que l'on est dans une entente cordiale et amicale

et ton comportement est d'autant plus ridicule (mais pas bien étonnant en fait) que même si les tests étaient mal faits
comme je teste toute les fonctions de la même manière!!!
donc si tu avais raison (ce qui est loin d'être le cas) ton raisonnement serait vérifié et avéré
la classe compte les tics count de la même manière pour toutes

donc si tu sais mieux que les autres va y explique je suis toute ouïe
d'autres que moi qui ont aussi décortiqué cette classe vont être très attentifs à tes dires (et on est quelques uns)

en attendant voici le fichier récap avec la new version en clipboard en plus + les tests benchmark
et c'est sans conteste que la version tar+split et tar+split intra clipboard sont les plus rapides

 

[patricktoulon]

@Dudu2
0.64 seconde pour powerquery, on est au dessus de la plus lente shell.automation qui fait 5xx ms
c'est donc la plus lente de toute