[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
--
"One of the best ways to boost your Excel efficiency is also one of the best ways to shoot yourself in the foot."
(Susan Harkins auf www.techrepublic.com/blog/10things/10-mistakes-to-avoid-when-working-with-multiple-worksheets/1961 )

[Claus Busch]

Hallo Jörg,

Am Tue, 22 Apr 2014 23:51:43 +0200 schrieb 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

> 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
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

[Jörg Eisenträger]

On Wed, 23 Apr 2014 10:07:37 +0200, Claus Busch
<claus...@t-online.de> wrote:

>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.


>>Meldung über vorhandene Verknüpfungen, die "nicht aktualisiert werden können":

>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.