Liaisons à vérifier par VBA

[chris]

Bonjour

Je dois tester à l'ouverture si tous les liens sont OK ou les modifier.

Il est facile de vérifier si le classeur lié existe à l'emplacement prévu mais y a t'il un moyen de connaître les onglets liés car il peut arriver que le classeur soit bien là mais un onglet lié introuvable...

Merci

Je ne mets pas d'exemple car c'est une question générale.

 

[Paritec]

Re : Liaisons à vérifier par VBA

Bonjour Chris le forum,
bon si j'ai compris, pour dresser une liste des liens de ton classeur, et les visualiser, bien sur après tu pourras, les comparer à tes différents onglets.
a+
Papou

sub test()
  dim L,i&
   L = ThisWorkbook.LinkSources
      for i = 1 to ubound(L)
           msgbox L(i)
      next i
end sub

 

[chris]

Re : Liaisons à vérifier par VBA

Bonjour

Merci Paritec.

J'utilise cela mais cela me donne le classeur lié mais pas quelles feuilles sont liées dans ce classeur, hélas.

 

[Paritec]

Re : Liaisons à vérifier par VBA

Re Chris le forum
oui mais si tu split ton lien complet, tu peux bien récupérer le nom des onglets concernés non?
le nom de l'onglet doit se retrouver entre crochet de mémo??
a+
Papou

 

[job75]

Re : Liaisons à vérifier par VBA

Bonjour chris, Papou,

Joli problème chris.

Cette solution est lourde car elle recherche 2 fois les formules de liaison dans toutes les feuilles.

Elle se sert du fait qu'en ouvrant les fichiers sources les noms des feuilles manquantes sont remplacés par #REF :

Sub Liens()
Dim w As Worksheet, c As Range, n&, a$(), L, i&, d As Object, wb As Workbook
'---liste des formules de liaison---
For Each w In ThisWorkbook.Worksheets
  For Each c In w.UsedRange
    If c.HasFormula Then
      If InStr(c.Formula, "[") Then
        n = n + 1
        ReDim Preserve a(1 To 2, 1 To n)
        a(1, n) = c.FormulaR1C1
      End If
    End If
  Next
Next
'---ouverture des fichiers sources---
Application.ScreenUpdating = False
L = ThisWorkbook.LinkSources
For i = 1 To UBound(L)
  On Error Resume Next
  Workbooks.Open(L(i))
  If Err Then MsgBox "'" & L(i) & "' introuvable..."
  On Error GoTo 0
Next
'---nouvelle liste des formules de liaison---
n = 0
For Each w In ThisWorkbook.Worksheets
  For Each c In w.UsedRange
    If c.HasFormula Then
      If InStr(c.Formula, "[") Then
        n = n + 1
        a(2, n) = c.FormulaR1C1
      End If
    End If
  Next
Next
'---restitution dans un nouveau document---
Set w = Workbooks.Add.Sheets(1)
w.[A:B].NumberFormat = "@" 'format Texte
w.[A1:B1].Font.Bold = True
w.[A1] = "Formule avant ouverture"
w.[B1] = "Formule après ouverture"
i = 2
Set d = CreateObject("Scripting.Dictionary")
For n = 1 To n
  If Not d.exists(a(1, n)) Then 'évite les doublons
    d(a(1, n)) = ""
    If InStr(a(2, n), "]#REF") Then
      w.Cells(i, 1) = a(1, n)
      w.Cells(i, 2) = a(2, n)
      i = i + 1
    End If
  End If
Next
w.Columns.AutoFit
'---fermeture des fichiers---
For Each wb In Workbooks
  If wb.Name <> w.Parent.Name And wb.Name <> ThisWorkbook.Name _
    Then wb.Close False
Next
ThisWorkbook.Close False 'on le ferme aussi...
End Sub

En comparant les 2 colonnes de la feuille de restitution il est facile de voir quelles sont les feuilles sources manquantes.

A+

 

[chris]

Re : Liaisons à vérifier par VBA

Bonjour

Re Chris le forum
oui mais si tu split ton lien complet, tu peux bien récupérer le nom des onglets concernés non?
le nom de l'onglet doit se retrouver entre crochet de mémo??
a+
Papou

Cela a été ma première idée mais non, hélas, les noms d'onglets ne figurent pas dans la liste des liaisons...

Job75 : merci je vais tester cela et te dirais.
J'espérais un solution plus frendly mais, sachant que, si MS sait établir les liaisons depuis ses toutes premières versions de Word, cela a toujours été une sinécure pour les gérer... En PAO il ya des utilitaires pour reconstruire les liaisons dont je rêve pour Office.

J'admets néanmoins que si les utilisateurs font n'importe quoi, il n'y a aucune solution miracle...

 

[job75]

Re : Liaisons à vérifier par VBA

Re,

Bien noter que pour que ma macro fonctionne il faut que dans les options on ait coché celle relative à la mise à jour des liaisons.

A+

 

[chris]

Re : Liaisons à vérifier par VBA

Re

Arf c'est justement le PB ! Le truc doit tourner en batch et l'objectif est de remplacer les liens vers des classeurs manquants par d'autres liens (les classeurs existent dans une autre arborescence).
Si tous les onglets existent cela marche mais si un onglet manque, la boîte de dialogue demandant de choisir une source s'ouvre et arrête le batch.

Si déjà on pouvait éviter cette boîte de dialogue, on pourrait après la mise à jour des liens vers les bons classeurs utiliser ta méthode pour descendre au niveau des feuilles mais si on commence par là tout est en erreur dès l'ouverture...

Mais j'ai peut-être louper un truc...

 

[job75]

Re : Liaisons à vérifier par VBA

Re,

Regarde si ceci te convient mieux :

Sub Liens()
Dim lig&, d As Object, w As Worksheet, c As Range, t$
Application.ScreenUpdating = False
Application.DisplayAlerts = False
With Workbooks.Add.Sheets(1) 'nouveau document
  .[A:A].NumberFormat = "@" 'format Texte
  .[A1].Font.Bold = True
  .[A1] = "Formules (R1C1) de liaison donnant une valeur d'erreur"
  lig = 2
  Set d = CreateObject("Scripting.Dictionary")
  For Each w In ThisWorkbook.Worksheets
    For Each c In w.UsedRange
      If c.HasFormula Then
        If InStr(c.Formula, "[") Then
          t = c.FormulaR1C1
          If Not d.exists(t) Then 'évite les doublons
            d(t) = ""
            If IsError(ExecuteExcel4Macro(Mid(t, 2))) Then
              .Cells(lig, 1) = t
              lig = lig + 1
            End If
          End If
        End If
      End If
    Next
  Next
  .Columns(1).AutoFit
End With
End Sub

Aucun fichier n'est ouvert, aucune formule n'est modifiée.

La macro liste les formules de liaison donnant une valeur d'erreur.

Mais on ne peut pas savoir qui du classeur ou de la feuille est introuvable.

A+

 

[chris]

Re : Liaisons à vérifier par VBA

Bonjour

Merci : cela répond bien au problème.
Je viens de l'appliquer sur un autre cas.

Pour le cas d'origine avec jusqu'à 200 classeurs liés sur plus de 1000 fichiers, l'usine à gaz fumait déjà : si on ajoute la recherche feuille par feuille, ça va chauffer dur !
Mais cela est une autre histoire.

Merci encore de ta persévérance et du résultat concluant .

 

[job75]

Re : Liaisons à vérifier par VBA

Bonjour chris, le forum,

Je ne comprends pas ce que tu veux dire par 200 et 1000 fichiers.

En tout état de cause avec un tableau VBA ce sera bien plus rapide :

Sub Liens()
Dim lig&, d As Object, w As Worksheet, P As Range, tablo, t
Application.ScreenUpdating = False
Application.DisplayAlerts = False
With Workbooks.Add.Sheets(1) 'nouveau document
  .[A:A].NumberFormat = "@" 'format Texte
  .[A1].Font.Bold = True
  .[A1] = "Formules (R1C1) de liaison donnant une valeur d'erreur"
  lig = 2
  Set d = CreateObject("Scripting.Dictionary")
  For Each w In ThisWorkbook.Worksheets
    Set P = w.UsedRange
    tablo = Union(P, P(2)).FormulaR1C1 'au moins 2 éléments
    For Each t In tablo
      If Left(t, 1) = "=" Then
        If InStr(t, "[") Then
          If Not d.exists(t) Then 'évite les doublons
            d(t) = ""
            If IsError(ExecuteExcel4Macro(Mid(t, 2))) Then
              .Cells(lig, 1) = t
              lig = lig + 1
            End If
          End If
        End If
      End If
    Next
  Next
  .Columns(1).AutoFit
End With
End Sub

Bonne journée.

 

[job75]

Re : Liaisons à vérifier par VBA

Re,

Un complément important.

Avant de lancer la macro aller dans les Options avancées et décocher la case Enregistrer les valeurs des liaisons externes.

Fermer et rouvrir le fichier.

J'ai découvert ça parce qu'aujourd'hui ExecuteExcel4Macro ne renvoyait pas les valeurs d'erreur, alors qu'hier tout allait bien (?).

A+

 

[chris]

Re : Liaisons à vérifier par VBA

Bonjour

Je regarderai cette autre solution ce soir ou demain soir. Merci .

Pour répondre à ta question il y a plus de 1000 fichiers à nettoyer (recréer les liens qui ne pointent pas sur le bon dossier) et certains de ces 1000 fichiers peuvent être liés à 200 classeurs... Et cerise sur le gâteau parfois un des onglets pointés n'existe plus (ou a changé de nom) dans le classeur lié d'où mon appel au secours...

Selon mes tests d'hier soir, ExecuteExcel4Macro marchait seulement en mode FormulaR1C1 (pas en Formula tout court. J'ai utilisé les deux pour ajouter, en mode habituel, type "A2", une liste des cellules pointées) mais sur l'unique fichier testé, je n'ai pas rencontré de souci.

(en fait j'aide un ami pour cette usine à gaz mais ayant de mon côté quelques classeurs où je dois vérifier les liens avant de les détruire, le test de ton code nous sert à tous les deux).

 

[job75]

Re : Liaisons à vérifier par VBA

Re,

Pour répondre à ta question il y a plus de 1000 fichiers à nettoyer

Je ne vois pas d'autre solution que de traiter les fichiers un par un.

En y mettant la macro...

Faut du courage !

A+

 

[chris]

Re : Liaisons à vérifier par VBA

Re

J'ai mis le code (d'hier) un peu retouché dans le classeur indépendant qui boucle et ouvre tous les fichiers 1 par 1 pour y faire le nettoyage de printemps !