Live-Forum - Die aktuellen Beiträge
Datum
Titel
26.04.2024 21:51:33
Idee für Grafik
26.04.2024 21:29:07
PQ zufällige Reihenfolge
26.04.2024 17:18:47
Große Datenmenge in komplexes Diagramm verpacken
26.04.2024 16:22:47
Aktualisierung Verknüpfung bei geschlossener Quelldatei
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
788to792
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
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
788to792
788to792
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Macro für automatische aktualisierung

Macro für automatische aktualisierung
12.08.2006 18:47:14
Peter Schilling
Hallo Excelfreunde
mein problem ist folgendes.
In Datei https://www.herber.de/bbs/user/35745.xls wird als Jahresübersicht aus den 12 Monaten jeweils für das letzte Quartal der Mittelwert aus den letzten 3 Monaten berechnet.
In Datei

Die Datei https://www.herber.de/bbs/user/35746.xls wurde aus Datenschutzgründen gelöscht

soll nun in B6, B7, B8 usw der Mittelwert des letzten Quartals automatisch beim öffnen der Datei aktualisiert werden.
Die Datein werden 01 bis 12 für die jeweiligen Monate genannt.
Öffne ich 01 (Januar) soll der Bezug R4 aus Datei Mittelwerte sein.
Öffne ich 02 (Februar) soll der Bezug S4 aus Datei Mittelwerte sein.
Öffne ich 03 (März) soll der Bezug T4 aus Datei Mittelwerte sein.
usw.
Wie kann ich dieses hinbekommen ohne 12 Dateien mit einem eigenen Bezug erstellen zu müssen.
Erklärung: Ich erstelle 1 Datei und ändere mit mehreren Macros
(Dank Macrorecorder) jeweils 16 in dieser Datei enthaltene Diagramme den Monat und noch einige andere Texte die ich für den jeweiligen Monat benötige und spiechere diese unter 01, 02, 03 usw ab.
Diese Dateien 01, 02, 03 sollen sich automatisch die Werte für das letzte Quartal beim öffnen besorgen.
Ich hoffe mich nicht zu kompliziert ausgedrückt zu haben.
Peter

16
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Kann niemand helfen
13.08.2006 09:52:49
Peter Schilling
Hallo Excelfreunde
kann mir niemand weiterhelfen.
Hier muss nicht mit einem Macro gearbeitet werden, für eine andere Lösung währe ich dankbar.
Es soll nur beim öffnen zB der Datei "05 (Mai)" der Bezug in die Mittelwertdatei nach (April, März, und Februar) also V4 (Mittelwert F4:H4) hergestellt werden.
Wird "05" in "06 oder "07" umbenannt, muss sich der Bezug automatisch auf W4 oder X4 ändern.
Gruß Peter
AW: Kann niemand helfen
13.08.2006 10:31:29
UDF
Hallo Peter,
ich denke in deiner Formulierung kann es leicht zu Missverständnissen kommen, da nicht klar erkennbar ist, was denn nun die Mittelwerte.xls ist. Ich denke es ist die erstbenannte https://www.herber.de/bbs/user/35745.xls, welche mittelwerte.xls heisst und die

Die Datei https://www.herber.de/bbs/user/35746.xls wurde aus Datenschutzgründen gelöscht

wird in 01.xls bis 12.xls umbenannt.
Dann wäre die Formel für z.B. B4
=BEREICH.VERSCHIEBEN([mittelwerte.xls]Tabelle1!$P4;0;TEIL(ZELLE("Dateiname";A1);FINDEN("[";ZELLE("Dateiname";A1))+1;2)+1)
Sofern die Mittelwerte.xls woanders steht muss das natürlich geändert werden.
Gruss
Markus (UDF)
Anzeige
AW: Kann niemand helfen
13.08.2006 11:15:05
Peter Schilling
Hallo Markus
ja die Datei 35745 ist die Mittelwertdatei.
Die Datei 35746 ist die jeweilige Datei 01, 02, 03 usw.
Die Datei 35745 erhält aus 35746 (01, 02, 03) usw die "Mittelwerte laufender Monat" aus Zelle B5 und trägt diese in zB in E4 bis P4 ein (35745).
In 35745 wird in R4 bis AC4 jeweils für die letzten 3 Monate der Mittelweret (aus E4 bis P4) errechnet.
Öffne ich nun Datei 35746 (als 08.xls) sollen in Tabelle 1 Zelle B5 die Werte aus Datei
35745 Zelle Y4 (Mittelwert aus I4, J4, K4)übernommen werden.
Öffne ich 04.xls soll der Bezug auf Datei 35745 Zelle U4 (mittelwert E4, F4, G4) verweisen.
Und dieser Verweis soll für jede Datei 01.xls bis 12.xls den Bezug aus 35745 Zellen R4 bis Ac4 beziehen.
Ich denke das ich mich zu umständlich ausdrücke.
Für ein Beispiel aus den beiden Dateien währe ich sehr dankbar.
Ich ändere in Datei 01.xls über ein macro alle Diagramme (16 Stück)und benenne sie dann 02.xls, 03.xls usw.
Dann muss der Bezug zur jeweiligen Spalte in der Mittelwertdatei R bis AC automatisch den jeweilgen Monat 1 bis 12 annehmen.
Anzeige
Tja, Sonntag
13.08.2006 11:32:08
UDF
Hallo Peter,
ich glaube, dass man vielleicht am Sonntag was anderes machen sollte.
Meine Formel war genau das, was du haben wollest. Eingebettet z.B. in die Datei "08.xls" gespeichert unter: https://www.herber.de/bbs/user/35752.xls
Gruss
Markus
AW: Tja, Sonntag
13.08.2006 11:47:11
Peter Schilling
hallo Markus
ich weiß zwar nicht wie, aber das funktioniert.
genial
[mittelwerte.xls]Tabelle1'!$P4;0;TEIL(ZELLE("Dateiname";A1);FINDEN("[";ZELLE("Dateiname";A1))+1;2)+1)
Mir ist eigendlich alles unklar aber egal, es soll nur funzen.
Ich danke dir Peter
AW: Tja, Sonntag
13.08.2006 12:09:44
UDF
Hallo Peter,
dann erkläre ich dir kurz mal etwas die Formel.
=BEREICH.VERSCHIEBEN('[mittelwerte.xls]Tabelle1'!$P4;0;TEIL(ZELLE("Dateiname";A1);FINDEN("[";ZELLE("Dateiname";A1))+1;2)+1)
Die Funktion BEREICH.VERSCHIEBEN erwartet als Parameter 1 die zu verschiebenen Zeilen, als Parameter 2 die Spalten. das heisst, die Formel=BEREICH.VERSCHIEBEN(A1;2;3) würde dir den Wert aus D3 liefern.
Das macht sich obige Formel zunutze. Welche Anzahl zu verschieben ist, hängt ja vom Dateinamen ab. Den vollständigen Namen liefert dir die Funktion ZELLE("Dateiname";A1). Statt den Bezug auf A1 kann auch jede andere Zelle angesprochen werden.
Der Dateiname steht dann etwa so da.
C:\Pfad\[08.xls]Tabelle1
Über TEIL() in Verbindung mit FINDEN() hole ich mir die beiden Zeichen hinter der öffnenden eckigen Klammer.
Da vorher auf die Zelle P4 positioniert wurde (Q4 ging wegen der verbundenen Zellen nicht) addiere ich noch eins drauf. Unabhängig davon muss ohnehin zumindest 0 aufaddiert werden, damit die Rückgabe von TEIL() in eine Zahl umgewandelt wird.
Siehste, ist doch garnicht so kompliziert. Vorausgesetzt man kennt die Formeln. ;-)
Gruss
Markus (UDF)
Anzeige
AW: Tja, Sonntag
13.08.2006 12:21:09
Peter Schilling
Hallo Markus
ich werde mir das mal in aller ruhe auf der Zunge zergehen lassen.
Ich kann mir zwar ein paar Bezüge erstellen und etwas mit dem Macrorekorder arbeiten, aber das wars auch schon.
Also in diesem Sinne einen schönen Sonntag noch
gruß Peter
AW: Tja, Sonntag
13.08.2006 12:34:41
Peter Schilling
Hallo Markus
nach eine Frage.
Wenn die datei Mittelwerte nicht geöffnet ist und ich dann 01.xls, 02.xls usw öffne
erhalte ich in den Zellen #WERTE.
Kann ich das auch noch ändern ohne die Datei Mittelwerte zu öffnen.
Peter der am Sonntag langeweile hat
AW: Tja, Sonntag
13.08.2006 14:09:28
UDF
Hallo Peter,
an der Stelle streubt sich Excel bei mir.
Kleines Workbook_Open()-Makro, einzugeben unter DieseArbeitsmappe schafft Abhilfe.

Private Sub Workbook_Open()
Dim wkb As Workbook
Dim blnOpen As Boolean
Application.ScreenUpdating = False
For Each wkb In Workbooks
If LCase(wkb.Name) = "mittelwerte.xls" Then
blnOpen = True
Exit For
End If
Next wkb
On Error GoTo Ende
If Not blnOpen Then
Workbooks.Open ThisWorkbook.Path & "\mittelwerte.xls", UpdateLinks:=False
ThisWorkbook.Sheets(1).Cells.Calculate
ActiveWorkbook.Close
End If
Ende:
On Error Resume Next
End Sub

Die Datei wird, falls nicht geöffnet zum Aktualisieren kurz geöffnet und wieder geschlossen.
Gruss
Markus
Anzeige
AW: Tja, Sonntag
13.08.2006 15:50:00
Peter Schilling
hallo
Ich habe dieses unter "dieseArbeitsmappe" eingegeben.
Es erscheint nun nach der Auswahl "aktualisieren Ja oder nein"
>Kompilierungsfehler im verborgenen Modul<
gruß Peter
AW: Tja, Sonntag
13.08.2006 16:36:32
UDF
Hallo Peter,
ich kann dir nur den Tipp geben, während der Entwicklung auf irgendwelche Schutz-Geschichten wie z.B. den VBA-Passwortschutz zu verzichten.
Dann ist das Modul nicht verborgen und du siehst zumindest die Zeile die den Fehler verursacht.
Gruss
Markus
AW: Tja, Sonntag
13.08.2006 17:22:45
Peter Schilling
Hallo
Es wird angeteigt das ein mehrdeutiger Name in dieser Arbeitsmappe vorliegt.
Hier "Privat Sub Workbook-open()"
ich hoffe du kannst helfen.
Der Code in diesr Arbeitsmappe sieht so aus
Option Explicit

Private Sub Workbook_Open()
Dim wkb As Workbook
Dim blnOpen As Boolean
Application.ScreenUpdating = False
For Each wkb In Workbooks
If LCase(wkb.Name) = "Mittelwerte.xls" Then
blnOpen = True
Exit For
End If
Next wkb
On Error GoTo Ende
If Not blnOpen Then
Workbooks.Open ThisWorkbook.Path & "\Mittelwerte.xls", UpdateLinks:=False
ThisWorkbook.Sheets(1).Cells.Calculate
ActiveWorkbook.Close
End If
Ende:
On Error Resume Next
End Sub



Private Sub Workbook_Open()
On Error GoTo Fehler
Dim arr As Variant
Dim arr1 As Variant
Application.OnKey "+^q", "Makroein"   'Zuweisung um Makros einzuschalten, defenition im Modul 1
Application.OnKey "+^y", "Makroaus"   'Zuweisung um Makros auszuschalten, defenition im Modul 1
Call Makroaus 'Makros ausschalten bei öffnen der Datei
Select Case ThisWorkbook.Name
Case Is = "01.xls"
arr = Workbooks("Mittelwerte.xls").Worksheets("Tabelle1").Range("R4:R14")
arr1 = Workbooks("Mittelwerte.xls").Worksheets("Tabelle1").Range("R20:R30")
Case Is = "02.xls"
arr = Workbooks("Mittelwerte.xls").Worksheets("Tabelle1").Range("S4:S14")
arr1 = Workbooks("Mittelwerte.xls").Worksheets("Tabelle1").Range("S20:S30")
Anzeige
AW: Tja, Sonntag
13.08.2006 17:37:18
P@ulchen
Hallo Peter,
zwei Sub´s mit gleichem Namen- das geht nicht.
Mußt Du aus beiden eine machen (ungetestet):

Private Sub Workbook_Open()
Dim wkb As Workbook
Dim blnOpen As Boolean
Dim arr As Variant
Dim arr1 As Variant
Application.ScreenUpdating = False
For Each wkb In Workbooks
   If LCase(wkb.Name) = "Mittelwerte.xls" Then
      blnOpen = True
      Exit For
   End If
Next wkb
On Error GoTo Ende
   If Not blnOpen Then
      Workbooks.Open ThisWorkbook.Path & "\Mittelwerte.xls", UpdateLinks:=False
      ThisWorkbook.Sheets(1).Cells.Calculate
      ActiveWorkbook.Close
   End If
Ende:
   On Error Resume Next
Application.OnKey "+^q", "Makroein"   'Zuweisung um Makros einzuschalten, defenition im Modul 1
Application.OnKey "+^y", "Makroaus"   'Zuweisung um Makros auszuschalten, defenition im Modul 1
Call Makroaus 'Makros ausschalten bei öffnen der Datei
Select Case ThisWorkbook.Name
   Case Is = "01.xls"
       arr = Workbooks("Mittelwerte.xls").Worksheets("Tabelle1").Range("R4:R14")
       arr1 = Workbooks("Mittelwerte.xls").Worksheets("Tabelle1").Range("R20:R30")
   Case Is = "02.xls"
       arr = Workbooks("Mittelwerte.xls").Worksheets("Tabelle1").Range("S4:S14")
       arr1 = Workbooks("Mittelwerte.xls").Worksheets("Tabelle1").Range("S20:S30")
.
.
.
.
.
End Sub

Code eingefügt mit Syntaxhighlighter 4.0


Gruß aus Leipzig
P@ulchen
www.excel-werkstatt.de
Anzeige
AW: Tja, Sonntag
13.08.2006 17:48:55
Peter Schilling
hallo
Ok ich habe einfach mal Workbook1_Open eingegeben. Es geht.
Ich erhalte jetzt die Meldung "Werte in Mittelwerte" abspeichern "Ja oder nein"
Klar will ich das.
Geht das auch ohne das die Meldung erscheint und bestätigt werden muss.
Gruß Peter
AW: Tja, Sonntag
13.08.2006 17:55:19
P@ulchen
Hallo Peter,
Ändere die Zeile:
ActiveWorkbook.Close
in:
ActiveWorkbook.Close savechanges:=True
Gruß aus Leipzig
P@ulchen
www.excel-werkstatt.de
Anzeige
AW: Tja, Sonntag
13.08.2006 17:59:42
Peter Schilling
Hallo Excelfreunde
Einfach genial
Peter desen sonntag gerettet ist

Beliebteste Forumthreads (12 Monate)

Anzeige
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige