Générer Lien hypertexte en VBA

Bonjour à tous,

J'ai besoin d'un coup de pouce ou que l'on me mette sur la voie pour répondre à une problématique de création de liens hypertext.
Jusqu'à présent, dans un fichier Excel, je générai des liens hypertext via la macro ci-après :

Sub Générer_Liens_Hypertext()
Dim chemin$
Dim derniereLigne As Long
chemin = ThisWorkbook.Path & "\"
derniereLigne = Range("B" & Rows.Count).End(xlUp).Row

For i = 3 To derniereLigne

If Cells(i, 2) = "" Then
Else
ActiveSheet.Hyperlinks.Add Anchor:=ActiveSheet.Cells(i, 2), Address:= _
chemin & Cells(i, 2) & "_CLIE_B7590" & ".pdf"
End If

Next i

End Sub

Sauf qu'aujourd'hui mes collègues ont revu l'organisation du classement de leurs fichiers et le chemin n'est plus du tout le même et varie selon d'autres critères non renseignés dans la feuille excel.
Aujourd'hui je connais une partie du chemin T:\0\Email\2021\Inovalys\2021_Rapports_Analyses\XXXXX\Cells(i, 2) où XXXXX est variable - existe t-il une possibilité de rechercher dans un dossier composé de sous-dossiers, le dossier nommé par le résultat Cells(i, 2) pou déterminer le chemin complet ?

Merci de votre aide.

Bonne journée

Bonjour

Oubliez le lien hypertexte. Si vous perdez ou changez la référence du répertoire, patatra vous êtes bon pour tout refaire. Cela m'est arrivé il y a une quinzaine d'années pour le boulot et ce, dans le même contexte que vous. D'où j'évite absolument de les utiliser sauf si le lien est utilisé pour accéder à une référence dans le même fichier.

Une solution alternative est d'utiliser un code qui par double click dans la cellule va vous rediriger vers le répertoire exact. Il faut juste connaitre le répertoire de sauvegarde et le nom du document attribué

Dans votre chemin, à quoi correspond le XXXXX
Quel est le nom de la feuille qui correspond à cells(i,2)
est-ce que le nom du document est mentionné dans le fichier

Est-il possible de voir comment est structuré votre fichier ?

Cordialement

Bonjour Dan,

Merci de votre approche. Effectivement la génération de liens hypertexte nécessite de ne pas changer sans cesse de chemin - pour le moment le chemin change tout les ans --> modification uniquement de l'année mais je pourrai rentrer l'année comme variable dans le code de la macro. Par contre c'est certain, si la racine change, il faut changer le code et régénérer tous les liens en traitant l'information en masse dans tous les onglets.

La partie XXXXX du chemin est aléatoire et définie par chaque personnel... Je ne la maîtrise pas.

Le nom qui correspond à la cells (i,2) est un numéro saisi par un salarié dès lors qu'il doit remplir le tableau. Cette codification est donc renseignée dans le fichier excel et correspond à une partie du nom du fichier pdf à aller chercher.

En P.J un fichier exemple - simplifié à une feuille de saisie mais qui représente la structure globale.

Merci de votre aide.

Cordialement,

Bonjour

1. Dans votre code à quoi correspond Thisworkbook.path ? à ceci --> T:\0\Email\2021\ ?

En d'autres termes, dans quel répertoire se trouve le fichier de suivi que vous m'avez envoyé

2. La partie XXXXX du chemin est aléatoire et définie par chaque personnel... Je ne la maîtrise pas.

est-ce cette partie est mentionnée dans le fichier ? Car le cas échéant, c'est impossible de faire quelque chose, que ce soit via code ou via lien hypertexte

Cordialement

Bonjour Dan,

Thisworkbook.path représentait l'ancien chemin car avant les fichiers .pdf étaient sauvegardés dans le même chemin que le classeur excel et ce n'est plus le cas aujourd'hui... et ce fort dommage

Dans le fichier Excel transmis le code présent est l'ancien que je cherche donc à adapter à la situation d'aujourd'hui...

Non la partie XXXXX n'est pas mentionnée et elle est variable c'est bien mon problème.

Une solution pourrait être de créer un code qui permettrait quand nous cliquons sur la cellule cells (i;2) de lancer automatiquement une recherche du fichier pdf car nous connaissons en partie son nom Cells(i, 2) & "_CLIE_B7590" & ".pdf" ou une recherche du sous dossier nommé par la valeur de Cells(i, 2) dans la partie du chemin connu T:\0\Email\2021\Inovalys\2021_Rapports_Analyses\

Est-il possible de générer un code qui permette dès lors que l'on double clique sur les cellules cells(i,2) de lancer puis d'afficher le résultat de la recherche - si la recherche n'est pas concluante, on génère une MsgBox "le fichier n'existe pas" ?

Qu'en pensez-vous ?

Cordialement,

1. Une fois le fichier trouvé vous voulez juste accéder au répertoire où le fichier est sauvegardé ?

de lancer automatiquement une recherche du fichier pdf car nous connaissons en partie son nom Cells(i, 2) & "_CLIE_B7590"

2. Il y a toujours le mot CLIE_B7950 ?? Exemple dans votre fichier le nom serait ceci ?--> X127880_CLIE_B7590"

Bonjour Dan,

Le mieux serait d'arriver au fichier sauvegardé... Oui il y a toujours une parie du fichier nommé CLIE_B7950.

Bonne journée

Cordialement,

Bonjour

Merci de la réponse.

Pouvez-vous essayer ceci :

- Click droite sur l'onglet Trame
- Choisissez l'option "visualiser le code"
- Dans la fenêtre coller le code ci-dessous

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("B:B")) Is Nothing Then
    Annee = Year(Range("C" & Target.Row))
    NomFichier = Target & "_CLIE_B7590.pdf"
    Call Trouvefichier
End If
Cancel = True
End Sub

- Restez sur cette fenêtre et cliquez dans le menu de l'editeur VBA sur Insertion
- Choisissez "Module"
- Dans la fenêtre coller le code ci-dessous

Public NomFichier As String
Public Annee As Long

Sub Trouvefichier()
Dim Chemin As String
Dim Sousdossier As Object, Dossier As Object
Dim Fichier

Chemin = "T:\0\Email\" & Annee & "\Inovalys\" & Annee & "_Rapports_Analyses\"

Set Dossier = CreateObject("Scripting.FileSystemObject").GetFolder(Chemin)
For Each Sousdossier In Dossier.SubFolders
    For Each Fichier In Sousdossier.Files
        If NomFichier = Fichier.Name Then
            Shell Environ("WINDIR") & "\explorer.exe " & Sousdossier, vbNormalFocus
            Exit Sub
        End If
    Next Fichier
Next Sousdossier
End Sub

- Enregistrer votre fichier

Une fois le double click effectué en colonne B de la feuille Trame, le code devrait vous amener dans le répertoire où se trouve le fichier.
Cela suppose que le répertoire correspond à l'année (par exemple 2021 ou suivant) est bien créé
En fonction de la ligne Chemin (qui définit le répertoire), le code va chercher le fichier dans le sous répertoire xxxxx créé.

Faites un essai

Cordialement

Bonjour Dan,

Merci d'avoir pris le temps de développer ces codes. Je les ai insérer comme tu l'as décris.

Dès lors que je double clique sur la cellule de la colonne B - une fenêtre s'ouvre Run-time error '76' - Path not found. En cliquant sur Debug - la ligne Set Dossier = CreateObject("Scripting.FileSystemObject").GetFolder(Chemin) est surlignée en couleur jaune / Dans le bas de la fenêtre le chemin trouvé est bien le bon - il est défini comme string /par contre la valeur de Sousdossier est Nothing

Le sous-dossier à atteindre est T:\0\Email\2021\Inovalys\2021_Rapports_Analyses\XXXXX\Cells(i, 2) et dans ce sous-dossier le fichier pdf à atteindre est nommé Cells(i, 2)YYY_CLIE_B7590_ZZZ.pdf ou YYY et ZZZ sont aléatoires mais dans tous les cas il n'y a que ce fichier unique dans T:\0\Email\2021\Inovalys\2021_Rapports_Analyses\XXXXX\Cells(i, 2)

Pour info, la version excel utilisée est 2003.

Bonne journée

Cordialement

Re

Ok avec excel 2003, il faut faire autrement. Pas facile car je n'ai pas cette version

Vous n'aviez pas expliqué les extensions YYYY et ZZZ. cela correspond à quoi ?

edit : essayez en remplaçant la sub Trouvefichier par ce code ci

Sub Trouvefichier2003()
Dim Chemin As String

Dim Sousdossier As Object, Dossier As Object
Dim Fichier

Chemin = "T:\0\Email\" & Annee & "\Inovalys\" & Annee & "_Rapports_Analyses\"

With Application.FileSearch
    .NewSearch
    .LookIn = Chemin
    .SearchSubFolders = True
    .Filename = NomFichier
    .MatchTextExactly = False
    .FileType = msoFileTypeAllFiles

    If .Execute() > 0 Then Shell Environ("WINDIR") & "\explorer.exe " & NomFichier, vbNormalFocus
End With

End Sub

Bonjour Dan,

Merci pour ce nouveau code. Je l’ai inséré et exécuté, il ne semble pas fonctionner. Cela génère un erreur sur la fonction .NewSearch

« Vous n'aviez pas expliqué les extensions YYYY et ZZZ. cela correspond à quoi »

Ce sont des portions de nommage qui correspondent aux dates/heures de génération du fichier.

Aussi j’ai essayé d’exécuter le premier code sous un Excel 2019 – n’ayant pas de disque T sur le PC concerné, j’ai créé le chemin sous c:/ que j’ai adapté dans la macro – et l’erreur suivante est générée :

Encore merci pour le temps passé.

Bonne journée

Cordialement,

Bonjour

A la vue de de votre image, votre code n'est pas copié comme je vous l'ai donné.
Vous devez respecter le positionnement de toutes les lignes. Là je vois Newsearch derrière Filesearch .... et les points devant chaque instruction

Aussi j’ai essayé d’exécuter le premier code sous un Excel 2019 – n’ayant pas de disque T sur le PC concerné, j’ai créé le chemin sous c:/ que j’ai adapté dans la macro – et l’erreur suivante est générée :

Ce n'est pas le premier code que vous me montrer mais le dernier. Inutile d'essayer le dernier code (celui avec Filesearch) avec excel 2019. Je vous ai donné celui là parce que vous m'avez écrit que vous n'avez que excel 2003.

Bonjour Dan,

Oui sur mon PC pro Excel est sur une version 2003 - j'ai voulu tester la macro sur un PC perso qui tourne sous excel 2019 et vous avez raison j'ai fait une erreur de copier-coller et d'exécution du macro.

Bref, je testerai la version 2003 demain sur mon PC pro.

Avec un excel 2019, la macro fonctionne dès lors qu'elle va chercher dans le chemin C:\0\Email\2021\Inovalys\2021_Rapports_Analyses\cells (i,2) mais en réalité mon chemin est C:\0\Email\2021\Inovalys\2021_Rapports_Analyses\XXXXX\cells (i,2)

où XXXXX est une valeur que je ne maîtrise pas.

Aussi est-il possible de rajouter un MsgBox si le fichier n'existe pas ?

Bonne journée

Bonjour

Avec un excel 2019, la macro fonctionne dès lors qu'elle va chercher dans le chemin C:\0\Email\2021\Inovalys\2021_Rapports_Analyses\cells (i,2) mais en réalité mon chemin est C:\0\Email\2021\Inovalys\2021_Rapports_Analyses\XXXXX\cells (i,2)

où XXXXX est une valeur que je ne maîtrise pas.

Aussi est-il possible de rajouter un MsgBox si le fichier n'existe pas ?

MsgBox "fichier non trouvé !"

Bonjour Dan,

Je viens d'injecter la macro dans le ficher Excel qui tourne sous le Excel 2003 :

Sub Trouvefichier2003()
Dim Chemin As String

Dim Sousdossier As Object, Dossier As Object
Dim Fichier

Chemin = "T:\0\Email\" & Annee & "\Inovalys\" & Annee & "_Rapports_Analyses\"

With Application.FileSearch
    .NewSearch
    .LookIn = Chemin
    .SearchSubFolders = True
    .Filename = NomFichier
    .MatchTextExactly = False
    .FileType = msoFileTypeAllFiles

    If .Execute() > 0 Then Shell Environ("WINDIR") & "\explorer.exe " & NomFichier, vbNormalFocus
End With

End Sub

Elle n'ouvre aucune fenêtre - j'ai donc exécuté pas à pas - elle s'exécute jusqu'à If .Ececute()>0 mais le résultat ne semble jamais supérieur à 0 et donc aucune fenêtre ne s'ouvre. J'ai l'impression que le code ne permet pas de rechercher dans l'ensemble des sous-dossiers et des sous-dossiers du chemin ?

Aussi est -il nécessaire de conserver :

Dim Sousdossier As Object, Dossier As Object
Dim Fichier

car ces variables ne sont pas utilisées dans ce code.

Bonne soirée.

Bonjour

M_H2O a écrit :

Aussi est -il nécessaire de conserver :

Dim Sousdossier As Object, Dossier As Object
Dim Fichier

car ces variables ne sont pas utilisées dans ce code.

Non désolé, bien vu ! c'est dû au fait que j'avais utilisé l'ancien code que j'ai modifié. Vous pouvez supprimer ces déclarations

Par contre au dessus de Sub trouvefichier2003() et comme dans le code précédent, vous devez avoir les deux déclarations

Public NomFichier As String
Public Annee As Long

Edit :

Je ne vous l'ai pas dit mais sous excel 2003 dans la feuille Trame, vous devez aussi changez cette ligne Call Trouvefichier par Call TrouveFichier2003
Vous devez exécuter le code depuis le double click et non pas commencer avec la macro Trouvefichier
L'option Subfolders étant à valeur True, permet de chercher dans le sous répertoire du répertoire défini dans la variable Chemin.

Bonsoir Dan,

J'avais préalablement désactivé les variables non utilisées en mettant un ' devant - j'avais bien évidement modifié Call Trouvefichier par Call TrouveFichier2003 et conservé les autres variables avant d'exécuter la macro - comme hier, après avoir double cliqué sur la cellule il ne se passe rien.

Ensuite j'exécute le code pas à pas et je vois que le double clic a fonctionné car certaines variables se sont incrémentés Annee --> 2021, le chemin est bon, la variable NomFichier correspond au résultat du double clic - j'ai remarqué que sur la ligne.FileType = msoFileTypeAllFiles j'ai vu que .FileType avait une valeur à 1 et que msoFileTypeAllFiles une valeur à 2.

La condition If .Execute() > 0 ne semble jamais rempli car la macro passe à End Sub.

Merci de me tenir informé de ce que je dois mettre en œuvre pour parvenir au résultat escompté.

Bonne soirée.

Bonjour

J'ai heureusement mon ami cfn3cfn qui possède Excel 2003. Grace à lui nous avons pu faire un test hier sur le sujet et assez curieusement, le code Trouvefichier fonctionne avec cette version.

Pour le tes nous avons placé le fichier dans un répertoire et créé un sous répertoire dans lequel nous avons mis un fichier PDF nommé xl121880_CLIE_B7590

La condition If .Execute() > 0 ne semble jamais rempli car la macro passe à End Sub.

Probablement qu'il ne trouve pas le fichier. Si vous ajoutez cette ligne en dessous --> If .execute = 0 then msgbox "fichier non trouvé !" Cela vous renvoie quoi ?

Pour le problème .Filetype, essayez en désactivant cette ligne

Autre chose, dans les compléments VBA (Outils -> Reference), vous n'auriez pas le complément Microsoft scripting runtime qui est actif ?

Bonjour Dan,

Merci de votre réponse - je regarde cela dès lundi matin et je reviens vers vous.

Bon week-end.

Cordialement,

Bonsoir Dan,

J'ai fait quelques vérifications sur le nommage des fichiers et maintenant, quand le fichier existe, avec la ligne .Filetype activée comme désactivée, --> ouverure du dossier Mes Documents... alors que le fichier n'est pas du tout dans ce dossier. Si le dossier n'existe pas, il ne se passe rien et quand je rentre la ligne --> If .execute = 0 then msgbox "fichier non trouvé !" si le fichier n'existe pas le msgbox s'ouvre bien.

Autre chose, dans les compléments VBA (Outils -> Reference), vous n'auriez pas le complément Microsoft scripting runtime qui est actif ?

Non il n'est pas actif - je l'ai activé cela ne change rien puis je l'ai donc redésactivé...

A votre disposition pour toute manipulation ou complément d'information.

Bonne fin de journée.

Cordialement,