Microsoft 365 Désactiver les liaison externe après mise à jour

[treza88]

Bonjour à tous,

J'ai deux fichiers avec des noms différents qui ont des liaisons externes identiques, donc quand un fichier est ouvert, le deuxième ne peut pas être actualisé en raison que le premier fichier est toujours connecté aux fichier externes.

Il y a t'il un moyen qu'une fois la mise a jour du fichier N°1 ont désactive toutes liaisons, pour libérer l'accès au deuxième fichier ?

J'espère que j'ai été assez clair sur mon problème, sinon n'hésitez pas à me demander.

Merci d'avance

 

[piga25]

Bonjour,
Peut être comme cela à mettre dans le module ThisWorkbook

Private Sub Workbook_Open()
    ' Mettre à jour et désactiver les liaisons externes
    Dim wb As Workbook
    Dim i As Integer
    Dim linkSources As Variant

    ' Mettre à jour toutes les liaisons dans le classeur actif
    Set wb = ThisWorkbook
    wb.UpdateLink Name:=wb.LinkSources(Type:=xlLinkTypeExcelLinks)

    ' Désactiver (supprimer) les liaisons externes
    linkSources = wb.LinkSources(Type:=xlLinkTypeExcelLinks)
    If Not IsEmpty(linkSources) Then
        For i = LBound(linkSources) To UBound(linkSources)
            wb.BreakLink Name:=linkSources(i), Type:=xlLinkTypeExcelLinks
        Next i
    End If
   
    ' Sauvegarder le classeur pour enregistrer les changements
    wb.Save

    MsgBox "Les liaisons externes ont été mises à jour et désactivées."
End Sub

 

[treza88]

Merci piga25,

Je pense que c'est une bonne base de départ.
Mais le "Breaklink" va supprimer les liaisons (si je ne me trompe pas) et je ne veux pas les supprimer, je veux les désactiver pour pouvoir les réactiver au moment ou j'en ai besoin.

Est ce que "WorkBook.SaveLinkValues = True" peut m'aider à m'en sortir, est ce qu'il sauve les liaisons que je pourrait retrouver après un "Breaklink ?

 

[piga25]

Bonjour,
Un autre essai:

Private Sub Workbook_Open()
    ' Mettre à jour et désactiver les liaisons externes
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim cell As Range
    Dim linkSources As Variant

    ' Mettre à jour toutes les liaisons dans le classeur actif
    Set wb = ThisWorkbook
    wb.UpdateLink Name:=wb.LinkSources(Type:=xlLinkTypeExcelLinks)

    ' Boucler à travers chaque feuille de calcul pour convertir les formules avec des liaisons externes en valeurs
    For Each ws In wb.Worksheets
        For Each cell In ws.UsedRange
            If cell.HasFormula Then
                ' Vérifier si la formule contient une liaison externe
                If InStr(1, cell.Formula, "[") > 0 Then
                    cell.Value = cell.Value ' Convertir la formule en valeur
                End If
            End If
        Next cell
    Next ws 

    ' Sauvegarder le classeur pour enregistrer les changements
    wb.Save

    MsgBox "Les liaisons externes ont été mises à jour et désactivées."
End Sub

 

[piga25]

Re
Un autre essai:

Workbook_Open()
    ' Mettre à jour et désactiver les liaisons externes
    Dim ws As Worksheet
    Set ws = ActiveSheet ' Remplacer ActiveSheet par le nom de votre feuille

    ' Parcourir toutes les cellules de la feuille
    For Each cell In ws.UsedRange
        If IsError(cell.Value) Then
            If Err.Number = 1004 Then ' Erreur spécifique aux liaisons externes
                ' Convertir la formule en valeur
                cell.Value = cell.Value
            End If
        End If
    Next cell

    MsgBox "Les liaisons externes ont été désactivées pour cette feuille."
End Sub

 

[crocrocro]

Bonjour à tous

Est ce que "WorkBook.SaveLinkValues = True" peut m'aider à m'en sortir, est ce qu'il sauve les liaisons que je pourrait retrouver après un "Breaklink ?

Non, il remplace juste les valeurs des liens par leur valeur (comme un Collage spécial Valeurs).
Et correspond donc (mais en 1 seule ligne) à la boucle de piga25.

 

[treza88]

Merci pica25 pour tes codes.
Merci crocrocro pour le complément d'information.
Désolé de n'avoir pas communiqué beaucoup jusque là, mais j'ai essayer de comprendre ce fichier que je n'ai pas créer.
Désolé aussi pour ton investissement pica25, car je me suis rendu compte que mes liaison externe ne sont pas dans les cellules, mais des liaison a d'autres fichier par le biais de Power Query.

Donc mon besoin a changer, mais c'est grâce a tes codes pica25 que je m'en suis rendu compte, il faut donc que je supprime l'actualisation des requêtes Power Query.
Manuellement apparemment ça se trouve ici, du moins j'espère que c'est ça :

il faut décocher cette case.
J'ai essayé avec l'enregistreur de macro en le décochant, valider et le décochant,puis valider , mais je n'ai rien de pertinent :

Sub Macro4()
'
' Macro4 Macro
'

'
    With ActiveWorkbook.Connections("Requête - RESULTATS").OLEDBConnection
        .BackgroundQuery = False
        .CommandText = Array("SELECT * FROM [RESULTATS]")
        .CommandType = xlCmdSql
        .Connection = _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=RESULTATS;Extended Properties="""""
        .RefreshOnFileOpen = False
        .SavePassword = False
        .SourceConnectionFile = ""
        .SourceDataFile = ""
        .ServerCredentialsMethod = xlCredentialsMethodIntegrated
        .AlwaysUseConnectionFile = False
    End With
    With ActiveWorkbook.Connections("Requête - RESULTATS")
        .Name = "Requête - RESULTATS"
        .Description = "Connexion à la requête « RESULTATS » dans le classeur."
    End With
    With ActiveWorkbook.Connections("Requête - RESULTATS").OLEDBConnection
        .BackgroundQuery = False
        .CommandText = Array("SELECT * FROM [RESULTATS]")
        .CommandType = xlCmdSql
        .Connection = _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=RESULTATS;Extended Properties="""""
        .RefreshOnFileOpen = False
        .SavePassword = False
        .SourceConnectionFile = ""
        .SourceDataFile = ""
        .ServerCredentialsMethod = xlCredentialsMethodIntegrated
        .AlwaysUseConnectionFile = False
    End With
    With ActiveWorkbook.Connections("Requête - RESULTATS")
        .Name = "Requête - RESULTATS"
        .Description = "Connexion à la requête « RESULTATS » dans le classeur."
    End With
End Sub

Merci pica25 en tout du coup de main jusque là.

 

[piga25]

Bonjour,
alors comme cela:

Private Sub Workbook_Open()
     'Mise à jour et désactivation des connexions Power Query
    Dim ws As Worksheet
    Dim conn As WorkbookConnection
    Dim pq As Object

    ' Mise à jour des requêtes Power Query
    For Each ws In ThisWorkbook.Worksheets
        For Each conn In ThisWorkbook.Connections
            If conn.Type = xlConnectionTypeODBC Or conn.Type = xlConnectionTypeOLEDB Then
                ' Mise à jour de la connexion
                conn.Refresh
            End If
        Next conn
    Next ws

    ' Désactiver les connexions Power Query
    For Each conn In ThisWorkbook.Connections
        If conn.Type = xlConnectionTypeODBC Or conn.Type = xlConnectionTypeOLEDB Then
            ' Accéder à l'objet Power Query
            Set pq = conn.OLEDBConnection

            ' Désactiver la mise à jour en arrière-plan
            pq.BackgroundQuery = False

            ' Désactiver la mise à jour automatique
            pq.RefreshOnFileOpen = False
        End If
    Next conn

    MsgBox "Mise à jour et désactivation des connexions Power Query terminées."
End Sub

 

[treza88]

Merci piga25,

Je suis en train de faire des tests avec ton code.
Mais en cherchant dans la doc je suis tombé (sans me faire mal) sur cette page :

Propriété QueryTable.MaintainConnection (Excel)

Rubrique de référence sur Office VBA

learn.microsoft.com

Peux tu me dire si c'est bien pour désactiver la connexion Power Query ?

et si ce code te semble correct ou pas:

Sub connexion()
Dim ws As Worksheet
Dim qt As Object
For Each ws In ThisWorkbook.Worksheets
    For Each qt In ws.QueryTables
        qt.MaintainConnection = False
    Next qt
Next ws
End Sub

Et éventuellement comment je pourrais tester pour savoir s'il a fonctionné ou pas ?

 

[treza88]

Bonjour,
Petit retour sur ton code piga25.

Je l'ai positionner dans "Workbook_Open()" et j'ai une erreur 1004 sur"pq.RefreshOnFileOpen = False" au démarrage, même en ayant modifier un peu ton code :

For Each conn In ThisWorkbook.Connections
        If conn.Type = xlConnectionTypeODBC Or conn.Type = xlConnectionTypeOLEDB Then
            ' Accéder à l'objet Power Query
            Set pq = conn.OLEDBConnection

            ' Désactiver la mise à jour en arrière-plan
            If pq.BackgroundQuery = True Then pq.BackgroundQuery = False

            ' Désactiver la mise à jour automatique
            If pq.RefreshOnFileOpen = True Then pq.RefreshOnFileOpen = False
        End If
    Next conn

Pourtant avec la condition je devrais éviter l'erreur si il est déjà à "False" ?

Et sur :

qt.MaintainConnection = False

Quelqu'un aurait des infos par rapport à mon post#9 ?

 

[treza88]

Bonjour,

Toujours dans ma quête de trouver une solution pour désactiver la connexion a un fichier utilisé par Power Query et qui mets ce message d'erreur quand on veut le fichier externe utiliser.
Quand j'ouvre le fichier externe et que je veux l'enregistrer, j'ai ce message :

A priori, j'ai ce message quand j'utilise :

ActiveWorkbook.RefreshAll

et pas quand j'utilise :

For Each conn In ActiveWorkbook.Connections
            If conn.Type = xlConnectionTypeODBC Or conn.Type = xlConnectionTypeOLEDB Then
                ' Mise à jour de la connexion  'Stop
                conn.refresh
            End If
        Next conn

Et les deux en ayant réglé au préalable les paramètres suivant :

Dim conn As WorkbookConnection
Dim pq As Object
    ThisWorkbook.Unprotect
    For Each conn In ThisWorkbook.Connections
        If conn.Type = xlConnectionTypeODBC Or conn.Type = xlConnectionTypeOLEDB Then
            ' Accéder à l'objet Power Query
            Set pq = conn.OLEDBConnection


            ' Désactiver la mise à jour en arrière-plan
            If pq.BackgroundQuery = True Then pq.BackgroundQuery = False


            ' Désactiver la mise à jour automatique
            If pq.RefreshOnFileOpen = True Then pq.RefreshOnFileOpen = False
            
            ' Désactiver la connection
            If pq.MaintainConnection = True Then pq.MaintainConnection = False
            
        End If
    Next conn
    ThisWorkbook.Protect

Quelqu'un pourrait il me dire quelle peut être la différence entre les deux ?