Live-Forum - Die aktuellen Beiträge
Datum
Titel
29.03.2024 13:14:12
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1288to1292
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Werte eines Tabellenblattes festschreiben.

Werte eines Tabellenblattes festschreiben.
18.12.2012 15:27:26
Golem
Hallo Excel-Fachleute.
Ich habe ein Tabellenblatt, in dem über Verknüpfungen zu 48 anderen Tabellenblätter mehrere Berechnungswerte eingetragen werden ( ein zentrales Verprobungsblatt für eine Jahresabrechnung ).
In diesem Verprobungsblatt können nach Fertigstellung noch über einen gewissen Zeitraum Veränderungen an Einzelwerten vorgenommen werden. Aber nach Überprüfung und Genehmigung dieser Jahresabrechnung
sollen alle Werte dieses Verprobungsblattes festgeschrieben werden.
Danach wird dieses Verprobungsblatt mit seinen festgeschriebenen Werten aber auch in anderen
Dateien weiter verwendet.
Bisher habe ich dieses Festschreiben über Spaltenweise kopieren und Werte einfügen händisch gemacht.
Besteht die Möglichkeit, mit einen Code oder Makro durch betätigen eines Buttons, alle Werte
dieses Verprobungsblattes festzuschreiben? Danach soll mit Text angezeigt werden, dass die Werte
festgeschrieben sind.
Danach kann ich dieses Tabellenblatt in andere Dateien hinein kopieren.
Solange aber noch die Verknüpfung zu den anderen Tabellenblätter besteht, soll über ein weiteres Betätigen
eines Buttons die Festschreibung wieder rückgängig gemacht werden können. Auch mit Textanzeige,
dass die Werte nicht festgeschrieben sind.
Für evtl. Tipps oder Anregungen schon mal besten Dank!
Golem

9
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Werte eines Tabellenblattes festschreiben.
18.12.2012 17:10:31
guentherh
Hallo Golem,
so kriegst Du die Werte ins Blatt:

Sub LinksBrechen()
Dim Links
Links = ActiveWorkbook.LinkSources(xlExcelLinks)
If Not IsEmpty(Links) Then
For i = 1 To UBound(Links)
Debug.Print "Link " & i & ":" & Chr(13) & Links(i)
ActiveWorkbook.BreakLink Links(i), xlLinkTypeExcelLinks
Next i
End If
End Sub

evtl. gewünschten oder vorhandenen Zellschutz habe ich nicht berücksichtigt.
beste Grüße
Günther

AW: Werte eines Tabellenblattes festschreiben.
18.12.2012 19:23:02
Golem
Hallo Günther,
einen schönen Dank für Deine Mitteilung.
Bei der Ausführen erscheint eine Fehlermeldung.
Fehler beim Kompilieren: Variable nicht definiert!
Variable "i".
Option Explicit
Sub LinksBrechen()
Dim Links
Links = ActiveWorkbook.LinkSources(xlExcelLinks)
If Not IsEmpty(Links) Then
For i = 1 To UBound(Links)
Debug.Print "Link " & i & ":" & Chr(13) & Links(i)
ActiveWorkbook.BreakLink Links(i), xlLinkTypeExcelLinks
Next i
End If
End Sub

Ein Zellschutz ist nicht vorhanden und ist auch noch nicht geplant.
Ich möchte den Code mit einen ToggleButton ausführen und dabei den Text "Werte
festgeschrieben" bzw. "Werte nicht festgeschrieben" anzeigen lassen.
Können mit dem Code auch die Verknüpfungen wieder hergestellt werde?
Schöne Grüße
Golem

Anzeige
AW: Werte eines Tabellenblattes festschreiben.
18.12.2012 20:34:54
Golem
Hallo Günther,
durch Deinen Hinweis auf "BreakLink" habe ich durch googeln folgenden Code gefunden:
Private Sub CommandButton1_Click()
ActiveSheet.Copy
'Open copy of only this sheet
Dim astrLinks As Variant
'Define variable as an Excel link type.
astrLinks = ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks)
ActiveWorkbook.BreakLink _
Name:=astrLinks(1), _
Type:=xlLinkTypeExcelLinks
'delete first extern link and sets values
End Sub

Ich kopiere das Tabellenblatt "Verprobung", schreibe danach den Code rein und führe ihn mit den Button aus.
Alle Verknüpfungen sind nicht mehr vorhanden, sondern nur die reinen Werte. Prima!
Wenn ich danach den Code nochmals ausführe, dann kommt der Fehler "Laufzeitfehler 13" und dieser Bereich
wird farbig hinterlegt:
ActiveWorkbook.BreakLink _
Name:=astrLinks(1), _
Type:=xlLinkTypeExcelLinks
Könnte ich mit leben, aber nicht so schön.
Ob mit einen ToggleButton die Verknüpfungen ein- oder ausgeschaltet werden kann, mag ich nicht
beurteilen ( die Kenntnisse fehlen ).
Evtl. eine Möglichkeit, dass mit den Button ein Code ausgeführt wird, um das Blatt "Verprobung" zu kopieren und
gleichzeitig werden die Verknüpfungen gelöscht
Schöne Grüße
Golem

Anzeige
AW: Werte eines Tabellenblattes festschreiben.
18.12.2012 21:33:45
Golem
Hallo Günther,
durch weiteres googeln bin ich auf folgenden Code gestoßen:
Private Sub CommandButton1_Click()
Dim strQuelldateiPfadName As String
Dim strQuelldateiName As String
Dim wksArbeitsblatt As Worksheet
strQuelldateiPfadName = ActiveWorkbook.Path + "\" + ActiveWorkbook.Name
strQuelldateiName = ActiveWorkbook.Name
Application.ScreenUpdating = False
Sheets(Array("Verprobung")).Copy
For Each wksArbeitsblatt In ActiveWorkbook.Sheets
wksArbeitsblatt.Unprotect
Next wksArbeitsblatt
ActiveWorkbook.BreakLink Name:=strQuelldateiPfadName, Type:=xlExcelLinks
ActiveWorkbook.Colors = Workbooks(strQuelldateiName).Colors
For Each wksArbeitsblatt In ActiveWorkbook.Sheets
wksArbeitsblatt.Protect Password:="xxx", DrawingObjects:=True, Contents:=True,  _
Scenarios:=True
wksArbeitsblatt.EnableSelection = xlUnlockedCells
Next wksArbeitsblatt
Application.ScreenUpdating = True
Application.Dialogs(xlDialogSaveAs).Show
End Sub
Damit kann ich aus meiner Datei das Tabellenblatt "Verprobung" per Knopfdruck heraus kopieren
und gleichzeitig werden alle Verknüpfungen gelöscht.
So könnte ich mit leben!
Nur wird dabei in einigen Zellen die bedingte Formatierung geändert und zwar so "(€50,00)" ( €#.##0,00_);[Rot](€#.##0,00)€#.##0,00_);[Rot](€#.##0,00) ).
Schöne Grüße
Golem

Anzeige
AW: Werte eines Tabellenblattes festschreiben.
19.12.2012 09:09:48
guentherh
Hallo Golem,
habe die neueste Nachricht zuerst beantwortet, darum war dieser Beitrag von Dir noch nicht berücksichtigt.
Dieser Code macht nahezu das gleiche wie meiner, nur dass dieser auf genau eine verknüpfte Datei festgelegt ist, und bei 0 verknüpften Dateien einen Fehler produziert, bei mehr als 1 verknüpften Dateien nur eine der Vernüpfungen löst.
Deinen Threads entnehme ich, dass Dir richtig geholfen wäre, wenn du den Code komplett mit Einblenden des Textes bekommen würdest. richtig?

AW: Werte eines Tabellenblattes festschreiben.
19.12.2012 15:25:46
Golem
Hallo Günther.
Meine Vorstellung ist, einen Gesamt-Code auf dem Tabellenblatt "Verprobung" mit einen Button zu haben.
Dieses Tabellenblatt ist in der Datei "Abrechnung 2012" vorhanden und diese Datei kann jedes Jahr kopiert und weiter verwendet werden.
Wenn die "Abrechnung 2012" mit allen Prüfungen abgeschlossen ist, so kopiere ich aus dieser Datei "Abrechnung 2012" das
Tabellenblatt "Verprobung" und setze es in andere Dateien zur weiteren Verwendung ein.
Dieser Gesamt-Code, auf dem Tabellenblatt "Verprobung", kopiert dieses Tabellenblatt in drei Schritten:
1. Nach Betätigung eines Buttons, auf dem Tabellenblatt "Verprobung", wird das Tabellenblatt als einzelne Datei heraus kopiert.
2. Dabei werden alle Verknüpfungen zu anderen Zellen, aus dieser Datei "Abrechnung 2012", aufgehoben.
3. Der Code auf diesem Tabellenblatt wird danach nicht weiter benötigt und kann gelöscht werden.
Für den Teil der Verknüpfungen aufheben, kann Dein Code verwendet werden ( nach Deiner Beschreibung ist dieser Code
für meinen Anwendungsfall bestens geeignet ).
Eine Textanzeige benötige ich nicht mehr.
Schöne Grüße
Golem

Anzeige
AW: Werte eines Tabellenblattes festschreiben.
20.12.2012 10:11:46
guentherh
Hallo Golem,
hier die Anpassung auf das Blatt Verprobung und eine kleine Abfrage zum Speichern.
BTW: ich persönlich würde die drei Klicks im Jahr manuell durchführen. Die Programmpflege dürfte aufwändiger als die Aktion sein.
Als Einstieg in Makros ist das natürlich super geeignet.

Sub GH_Einfrieren()
Dim Links
Dim i
Dim FName
Dim testsh As Worksheet
If Not ThisWorkbook.Saved Then
If (MsgBox("Diese Datei ist noch nicht gespeichert!" & Chr(10) & "Soll der Vorgang  _
fortgesetzt werden?", vbYesNo)) = vbNo Then
Exit Sub
End If
End If
Sheets("Verprobung").Copy
Links = ActiveWorkbook.LinkSources(xlExcelLinks)
If Not IsEmpty(Links) Then
For i = 1 To UBound(Links)
Debug.Print "Link " & i & ":" & Chr(13) & Links(i)
ActiveWorkbook.BreakLink Links(i), xlLinkTypeExcelLinks
Next i
End If
'' Einfügen eines Textes mit aktuellem Datum
'Range("D1") = "Werte sind festgeschrieben zum " & Format(Date, "dd.mm.yyyy") & "!!"
'' löschen Button und Aufruf von Speichern-unter
'' Button muss "CommandButton1" heissen und Code kann nicht nicht direkt gelöscht werden
'' besser gleich manuell durchführen
'ActiveSheet.Shapes("CommandButton1").Delete
'Do
'    FName = Application.GetSaveAsFilename
'Loop Until FName  False
'ActiveWorkbook.SaveAs FName
End Sub
Ein schönes Fest und ein hervorragendes neues Jahr wünscht Dir
Günther

Anzeige
AW: Werte eines Tabellenblattes festschreiben.
20.12.2012 14:39:46
Golem
Hallo Günther,
einen schönen Dank für Deine Bemühungen. Du hast mir sehr geholfen.
Auch Dir wünsche ich frohe Weihnachtsfeiertage und alles Gute für das Jahr 2013 ( wichtig ist, viel Gesundheit ).
Schöne Grüße
Golem

AW: Werte eines Tabellenblattes festschreiben.
19.12.2012 08:36:53
guentherh
Hallo Golem,
Die Zeile
Dim i
hinzufügen.
Die Verknüpfungen können nicht wieder hergestellt werden!!
Das ist definitiv eine Einbahnstraße.
Die letzte Version mit den Verknüpfungen solltest Du also unbedingt aufheben, um unvorhergesehene Aktualisierungen nachschieben zu können.
beste Grüße,
Günther

313 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige

Links zu Excel-Dialogen

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige