Beaucoup de fichiers, et donc d'onglets VBA changer le nom

[Carnage029]

Bonjour à tous...

J'ai actuellement un panel de fichier excel (plus de 20) que je dois mettre à jour tout les trimestres. Cependant la format de ces fichiers n'est pas très optimal, en effet le nom des onglets finit toujours par une date...

Par exemple je vais avoir des onglets :
- ZC 31_03_13
- Simulation 31_03_13
- ...

Vu qu'il n'est pas possible de trouver un point commun aux "préfixes" du nom de l'onglet (peut comporter des chiffres, des caractères...) je souhaitait savoir deux choses.

La première, en VBA quelle est la fonction qui permet de trouver une string bien particulière dans le nom d'un onglet et de la remplacer. (Je ferais une boucle pour chaque onglet après)

La deuxième, vu que j'ai énormément de fichiers, écrire le code dans chaque workbook me semble pas très optimisé, n'existe t'il pas un endroit pour "mes macros favorites" permettant d’appeler cette procédure dans n'importe quel excel depuis mon poste, et donc ne pas avoir a renommer mes .xlsx en.xlsm



Merci beaucoup à vous

 

[Orodreth]

Re : Beaucoup de fichiers, et donc d'onglets VBA changer le nom

Bonjour,

Pour ta première question, la fonction de remplacement est Replace.
Elle sous-entend que tu connaisses ta chaine, ou que tu sois capable de la retrouver.
Si ta chaine répond à une logique particulière, je te conseille de regarder du côté des expressions régulières (un petit bijou ce truc):
- Simple Regular Expression Tutorial for Excel VBA | Macrostash
- https://www.excel-downloads.com/threads/vba-expressions-regulieres-patterns-pour-regexp.152183/
- Tutoriel Access © cafeine - Les Expressions Rationnelles appliquées en VBA Access (oui, c'est pour Access, mais VBA est le même)

Concernant ta deuxième question, je ne suis pas sûr que ça fonctionne exactement de la même manière, n'ayant pas eu l'occasion d'essayer en 2007/2010, mais tu peux normalement manipuler les Workbooks depuis VBA.

Donc tu peux tout à fait manipuler tes classeurs "Data" sans avoir de code dedans, et un classeur "Pilote" qui gère l'aspect applicatif.

Petit conseil pour ce principe: utilise des variables objets "Workbook"

Ca donne un truc du genre:

    Dim WBK_Appli as Workbook
    Set WBK_Appli = thisWorkbook 'ton seul classeur ouvert sur le moment

    Dim WBK_Data as Workbook
    Set WBK_Data = Application.OpenFileName(......)

De là, tu manipules les classeurs via leurs variables de référence, et tu oublies pas de fermer chaque classeur Data quand tu as fini de t'en servir, avant de passer au suivant.

Sinon, tu peux aussi enregistrer tes macros dans un modèle, mais je ne suis pas un fan de cette solution :/ (ça implique notamment que tous tes classeurs ont accès au macro, même quand tu ouvres juste un nouveau classeur pour faire une petite somme rapide).

Cordialement,

 

[Carnage029]

Re : Beaucoup de fichiers, et donc d'onglets VBA changer le nom

Merci de ton message ! Je vais passer un peu de temps à lire les docs que tu m'a donné...

J'ai opté pour la méthode fichier "pilote" comme ça tout le monde peut y accéder sur le réseau.
En fait la seule restriction et que tout les fichiers ouverts sur le poste de l'utilisateur doivent être traités,

je publie la macro que j'ai codée, si jamais vous voyez des erreurs grossières n'hésitez pas J'avoue que depuis 1h qu'elle est en place je n'ai eu aucun retour sur des bugs :

Sub changeonglet()

Dim ancien As String
Dim nouveau As String

ancien = [E5]
nouveau = [E7]


For Each wb In Workbooks
    wb.Activate
    For Each ws In Worksheets
        If InStr(ws.Name, ancien) > 0 Then
            ws.Name = Replace(ws.Name, ancien, nouveau)
        End If
    Next
Next



End Sub

 

[Orodreth]

Re : Beaucoup de fichiers, et donc d'onglets VBA changer le nom

Re,

Pas d'erreur en soi dans ton code, il marche, avec la contrainte que tous tes classeurs soient ouverts AVANT le traitement.
Ca peut être long et contraignant.

Sinon, une petite optimisation, histoire d'accélérer ton traitement: tu n'as pas besoin de rendre actif un classeur.

En reprenant ton code, ça donne ça:

Sub changeonglet()

Application.ScreenUpdating = false

Dim ancien As String
Dim nouveau As String

ancien = [E5]
nouveau = [E7]


For Each wb In Workbooks
    For Each ws In WB.Worksheets
        If InStr(ws.Name, ancien) > 0 Then
            ws.Name = Replace(ws.Name, ancien, nouveau)
        End If
    Next
Next

Application.ScreenUpdating = True

End Sub

Ca devrait accélérer pas mal tes traitements

Cordialement,

 

[Carnage029]

Re : Beaucoup de fichiers, et donc d'onglets VBA changer le nom

Merci pour cette astuce !

Je t'avoue que les besoins sont assez limitées (moins de 200 onglets à traiter) donc pour l'instant mes tests étaient plutôt dans le scope de la seconde que de la minute d’exécution.

Petite question technique, VBA fait la différence entre wb et WB ? Je croyais que ce n'était pas possible

 

[Orodreth]

Re : Beaucoup de fichiers, et donc d'onglets VBA changer le nom

Re,

Non, il ne fait pas la différence de casse. Il y a peut-être une option pour ça mais j'en doute, et de toutes façons, mieux vaut utiliser des noms de variables différents pour s'y retrouver.

200 onglets à traiter, ça prend un peu de temps. Certes, tu changes que le nom, mais bon, si tu commences à taper dans les cellules, tu vas vite voir les différences de vitesse

 

[Carnage029]

Re : Beaucoup de fichiers, et donc d'onglets VBA changer le nom

oui c'est sur... quand j'étais en stage, j'avais crée une usine a gaz qui allait chercher sur oracle et tout le tintouin, pas très optimisée, mais elle automatisait un processus (jusqu’à l'impression et l'envoi de mails) il fallait bien 10 minutes d'exécution

 

[Orodreth]

Re : Beaucoup de fichiers, et donc d'onglets VBA changer le nom

Re,

Si tu veux mon avis, et fort de mon expérience du moment, j'ai tendance à préférer gérer le stockage sur une vraie base de données (comme Oracle donc) plutôt que sur Excel

Alors oui, c'est plus long, mais par contre, c'est hyper stable

 

[Carnage029]

Re : Beaucoup de fichiers, et donc d'onglets VBA changer le nom

oui à mon avis access est pas mal pour les moyenne BDD, le seul problème c'est que les gens sont fortement tributaires des moyens mis en place pour accéder aux données... dans une entreprise de 10 personnes c'est un peu compliqué à mettre en place, les gens préfèrent les bidouillages plutôt que l'intégrité des données

 

[Orodreth]

Re : Beaucoup de fichiers, et donc d'onglets VBA changer le nom

SQL Server Express en DB plutôt qu'Access.

J'ai mis à plat le moteur SQL d'Access avec une requête simple ...

Mais c'est pas le sujet

 

[Carnage029]

Re : Beaucoup de fichiers, et donc d'onglets VBA changer le nom

Je t'avoue que les requêtes et moi ça fait 2 (moles) donc bon... faudrait que j'arrive a trouver un livre audio du genre "apprenez les requêtes en dormant"