[XL2010, VBA] Externe Verknüpfungen löschen, wenn Blattschutz aktiv

[Jörg Eisenträger]

Hallo NG,

ich möchte gleich nach dem Öffnen einer Arbeitsmappe per VBA in
Workbook_Open() alle externen Excel-Verknüpfungen aktualisieren und dann
löschen (in Werte umwandeln). Das klappt auch mit

Me.UpdateLink Name:=Me.LinkSources

Dim vAllLinks As Variant, ii As Integer
On Error Resume Next
vAllLinks = Me.LinkSources(xlExcelLinks)
If Not IsEmpty(vAllLinks) Then
For ii = UBound(vAllLinks) To 1 Step -1
wbE.BreakLink vAllLinks(ii), xlExcelLinks
Next ii
End If


Allerdings geht das wohl nicht, wenn ein Blatt geschützt ist. Deshalb
hebe ich vorher in einer Schleife bei allen Blättern den Blattschutz
auf.

Jetzt kann es aber sein, dass in die Mappe einzelne Sheets hineinkopiert
wurden mit einem Blattschutz, dessen Passwort ich nicht kenne. Wie kann
ich auf solchen Sheets entweder
a) die Verknüpfungen trotz des fremden Blattschutzes löschen oder
b) diese Sheets überspringen ohne dass eine Meldung zur Passworteingabe
oder eine sonstige Meldung erscheint?


Zweite Frage:

Bereits vor dem Ausführen von Workbook_Open() kommt oft die Meldung über
vorhandene Verknüpfungen, die "nicht aktualisiert werden können" (die
Quelle liegt auf einem Netztlaufwerk). Bei Verknüpfung bearbeiten geht
es dann.

Was muss ich im Template der Arbeitsmappe einstellen, damit die User
nicht vor Workbook_Open() durch die Meldung belästigt werden?


Gruß
Jörg

[Claus Busch]

Hallo Jörg,

Post by Jörg Eisenträger
Jetzt kann es aber sein, dass in die Mappe einzelne Sheets hineinkopiert
wurden mit einem Blattschutz, dessen Passwort ich nicht kenne. Wie kann
ich auf solchen Sheets entweder
a) die Verknüpfungen trotz des fremden Blattschutzes löschen oder
b) diese Sheets überspringen ohne dass eine Meldung zur Passworteingabe
oder eine sonstige Meldung erscheint?

ich würde dann nicht die Verknüpfung löschen, sondern die Formel in
Werte wandeln. Das kannst du dann in einer Schleife über die Blätter
machen und die Blätter mit dem unbekannten Passwort aussparen:

Sub Test()
Dim i As Long
Dim rngC As Range

Application.ScreenUpdating = False

For i = 1 To Sheets.Count
With Sheets(i)
On Error Resume Next
.Unprotect "1234"
If .ProtectContents = True Then GoTo SKIP
For Each rngC In .UsedRange.SpecialCells(xlCellTypeFormulas)
If InStr(rngC.Formula, "[") Then
rngC.Value = rngC.Value
End If
Next
.Protect "1234"
End With
SKIP:
Next
Application.ScreenUpdating = True
End Sub

Post by Jörg Eisenträger
Was muss ich im Template der Arbeitsmappe einstellen, damit die User
nicht vor Workbook_Open() durch die Meldung belästigt werden?

probiere mal bei Verknüpfung bearbeiten => Eingabeaufforderung beim
Start die Meldung auszuschalten.
Falls das nicht funktioniert, schalte in deinem Code zuerst die
DisplayAlerts aus und am Ende wieder ein:
Application.DisplayAlerts = False


Mit freundlichen Grüßen
Claus

[Jörg Eisenträger]

On Wed, 23 Apr 2014 10:07:37 +0200, Claus Busch

Post by Claus Busch
ich würde dann nicht die Verknüpfung löschen, sondern die Formel in
Werte wandeln.

Deine Idee des blattweise Umwandelns von Formeln mit dem Kriterium "["
(externe Links) ist wirklich gut. Danke.
Mich hatte schon immer gestört, dass die external LinkSources zum
Workbook-Objekt gehören und nicht zum Sheet.

Ich habe das Kriterium für einen externen Excel-Link sicherheitshalber
noch weiter eingeschränkt:

If InStr(rngC.Formula, "[") And _
InStr(rngC.Formula, "]") And _
InStr(LCase(rngC.Formula), ".xl") And _
InStr(rngC.Formula, "'!") Then ...

Es könnte ja sein, dass die eckige Klammer mal in einer Stringformel
vorkommt, die nicht gelöscht werden soll.

Post by Claus Busch
probiere mal bei Verknüpfung bearbeiten => Eingabeaufforderung beim
Start die Meldung auszuschalten. Falls das nicht funktioniert, schalte in deinem
Code zuerst die DisplayAlerts aus

Die Eingabeaufforderung war schon ausgeschaltet und die kommt schon,
bevor Workbook_Open() überhaupt losläuft. Das kann man sehen, wenn man
die Makrosicherheit so einstellt, dass man vorher gefragt wird. Dann
kommt die Verknüpfungsmeldung schon, bevor man die Makroausführung
erlaubt hat.


Gruß
Jörg