Arbeitsblattname als Variable

Informationen und Beispiele zu den hier genannten Dialog-Elementen:
MsgBox
Bild

Betrifft: Arbeitsblattname als Variable
von: Gerd
Geschrieben am: 13.09.2015 11:18:51

Ich habe: zwei Arbeitsmappen ("Bericht" und "Beamer") mit je einem Arbeitsblatt ("Spiel_X" und "Anzeige"). Das Arbeitsblatt "Spiel_X" ist eine schlichte Tabelle mit sich ändernden Zelleninhalten. Das Arbeitsblatt "Anzeige" ist optisch aufgehübscht und wird per Beamer angezeigt. Beim Öffnen ist die Arbeitsmappe "Bericht" jeweils eine Andere mit Arbeitsblättern wie z.B. "Spiel_1"; "Spiel_2" usw. und die Arbeitsmappe "Beamer" immer die Gleiche. Diverse Zellinhalte von "Spiel_X" werden bisher per Verknüpfung (='[Bericht.xls]Spiel_X'!$A$17) in "Anzeige" übernommen und bei Änderung automatisch in "Anzeige" aktualisiert.
Ich möchte: da sich bei jedem neuen Start der Arbeitsblattname "Spiel_X" ändert, diesen in eine Variable einlesen, die ich dann im Arbeitsblatt "Anzeige" wieder in einer Verknüpfung zu den variierenden Zellinhalten von "Spiel_X" nutzen kann.
Danke und Gruß
Gerd

Bild

Betrifft: AW: Arbeitsblattname als Variable
von: Sepp
Geschrieben am: 13.09.2015 14:17:48
Hallo Gerd,
das geht am einfachsten per > Daten > Verknüpfungen bearbeiten > Quelle ändern

Gruß Sepp


Bild

Betrifft: AW: Arbeitsblattname als Variable
von: Gerd
Geschrieben am: 13.09.2015 14:45:04
Hallo Sepp,
die Quelle ändern allein machts nicht, da in den neuen Quellen der Arbeitsblattname variiert (darauf habe ich keinen Einfluss, da extern vorgegeben), sodass er mühsam in all den existierenden Verknüpfungen auch noch geändert werden muss. Das habe ich bisher durch Suchen/Ersetzen in einem VBA-Programm realisiert, das bei jedem neuen Arbeitsblattnamen entsprechend angepasst werden muss.
Deshalb die Idee, die Änderungen per Variable im VBA-Programm zu erleichtern.
Gruß Gerd

Bild

Betrifft: AW: Arbeitsblattname als Variable
von: Sepp
Geschrieben am: 13.09.2015 15:05:47
Hallo Gerd,
beim ändern der Quelle wirst du auch nach dem Tabellenblatt gefragt!

Gruß Sepp


Bild

Betrifft: AW: Arbeitsblattname als Variable
von: Gerd
Geschrieben am: 13.09.2015 15:27:37
Hallo Sepp,
beim Ändern der Quelle erscheint sofort die Fehlermeldung
Userbild
ohne nach dem Tabellenblatt zu fragen, da in den diversen Verknüpfungen z.B. steht:
='[Datei_01.xls]Spiel_01'!$A17.
Ändere ich die Quelle auf Datei_02.xls mit Arbeitsblattnamen Spiel_02, so entsteht folgerichtig die o.g. Meldung.
Gruß Gerd

Bild

Betrifft: nicht nachvollziehbar! o.T.
von: Sepp
Geschrieben am: 13.09.2015 15:34:56

Gruß Sepp


Bild

Betrifft: AW: Arbeitsblattname als Variable
von: Peter Kloßek
Geschrieben am: 13.09.2015 19:17:33
Hallo Gert,
ich habe leider keine Lösung gefunden, eine Variante in eine Formel einzubauen. Man kann aber Standardformeln (Spiel_x) einsetzen und die dann durch Ändern in Beziehung zu dem aktiven Arbeitsblatt bringen. Es werden dazu in der Arbeitsmappe "Bericht" (hier unter c1) je Arbeitsblatt freie Zellen benötigt, sowie in der Arbeitsmappe "Beamer" noch zusätzlich c2. In c2 ist die Standardformel enthalten, die durch den Makro in alle relevanten Zellen vom "Beamer" hineinkopiert werden. Anschließend werden die Ausdrücke "Spiel_x" durch den richtigen Blattnamen ersetzt.
Hier die Datei für "Bericht" - bitte unter diesem Namen speichern:
https://www.herber.de/bbs/user/100156.xlsm
...und hier die Datei "Beamer" - bitte ebenfalls unter diesem Namen speichern.
https://www.herber.de/bbs/user/100157.xlsm
Wie ich feststelle, schleppt Excel meinen user in den Formeln mit, bitte lösche die entsprechenden Ausdrücke unter c2 einfach raus.
In der Datei "Bericht" das gewünschte Arbeitsblatt aktivieren und dann in der Datei "Beamer" den Makro1 ausführen. Bitte mal ausprobieren, FeedBack wäre schön.
Mit freundlichem Gruß
Peter Kloßek

Bild

Betrifft: AW: Arbeitsblattname als Variable
von: Gerd
Geschrieben am: 13.09.2015 20:30:52
Hallo Peter,
danke für deine Lösung, die funktioniert wie beschrieben. Aber so richtig glücklich bin ich damit nicht.
Kann man den Blattnamen nicht irgendwie in die Funktion INDIREKT(Bezug;A1) oder eine geeignetere bringen. Das würde mir besser gefallen.
Ich habe es schon mit mit den Blattnamenvariablen "Quelle" und "Ziel" wie folgt probiert:
For i = 0 To 4
Ziel.Cells(17 + i, 1).Value = Quelle.Cells(17 + i, 1).Value 'Namen
Next i
funktioniert zwar, aber Änderungen in der Quelldatei werden nicht automatisch in die Zieldatei übernommen.
Siehst du da einen besseren Ansatz?
Gruß Gerd

Bild

Betrifft: AW: Arbeitsblattname als Variable
von: Peter Kloßek
Geschrieben am: 13.09.2015 21:20:53
Hallo Gert,
wenn in der Datei "Beamer" viele Bezüge vorhanden sind, könnte man ein Standard-Blatt mit "Spiel_x" in den Formeln erstellen, dieses per Makro kopieren und die Kopie mit "Anzeige" umbenennen. Der Austausch der Bezüge auf das zutreffende Blatt muss dann über sämtliche Zellen erfolgen:


    Windows("Bericht.xlsm").Activate
    Range("c1").Select
    ActiveCell = Blattname
    Range("c1").Cut
    Windows("Beamer.xlsm").Activate
    Range("Anzeige!c1").Select
    ActiveSheet.Paste
    Blattbez = Range("c1")
    Cells.Replace What:="Spiel_x", Replacement:=(Blattbez), LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Das Kopieren und Umbenennen des Standard-Arbeitsblattes (mit einem anderen Namen als "Ansicht") kannst Du einfach aufzeichnen. Das Ergebnis der Aufzeichnung an den Anfang stellen.
M.f.G. Kloßek

Bild

Betrifft: AW: Arbeitsblattname als Variable
von: fcs
Geschrieben am: 13.09.2015 21:26:30
Hallo Gerd,
wenn die Arbeitsmappen "Bericht" (Variabel) und "Beamer" (immer die gleiche) immer gleichzeitig geöffnet sind, dannkannst du im Blatt "Anzeige" von "Beamer" auch mit der Funktion INDIREKT arbeiten, um die Daten aus einem Blatt von "Bericht" auszulesen.

=INDIREKT("'[" & $B$4 & "]" & $B$5 &"'!"& ZELLE("adresse";A3);WAHR)
Dann müssten "nur" der Dateiname und der Blattname aus "Bericht" in jeweils eine Zelle (hier B4 und B5) eingetragen werden.
Ein Makro zur Übertragung des Datei- und Blattnamens kann dann wie folgt aussehen.
Soll das Makro in der Datei "Beamer" integriert werden. Dann wird komplizierter. Hier muss man dann ggf. ein Auswahlmenü für die Datei und das Blatt einbauen.
Gruß
Franz
'Makro in der persönlichen Makroarbeitsmappe oder der "Bericht"-Datei
Sub Beamer_Anzeige_aktualisieren()
'
' Anzeige in Arbeitsmappe "Beamer" aktualisieren
    Dim strDatei As String, strBlatt As String, strBeamer As String
'
    On Error GoTo Fehler
    
    strBeamer = "Beamer.xlsx" 'Name der Datei mit Blatt "Anzeige"
    strDatei = ActiveWorkbook.Name
    strBlatt = ActiveSheet.Name 'oder auch = ActiveWorkbook.Sheets(1).Name
    
    If strDatei <> strBeamer Then
        With Workbooks(strBeamer)
            With .Worksheets("Anzeige")
                .Range("B4") = strDatei
                .Range("B5") = strBlatt
            End With
            .Activate
        End With
    Else
        MsgBox "Makro bitte nicht starten, wenn Datei """ _
            & strBeamer & """ die aktive Datei ist"
    End If
Fehler:
    With Err
        Select Case .Number
            Case 0 'alles OK
            Case 9 '
                MsgBox "Fehler: " & .Number & vbLf & .Description & vbLf & vbLf _
                    & "Datei """ & strBeamer & """ ist vermutlich nicht geöffnet" _
                    & " oder Blatt ""Anzeige"" ist nicht vorhanden"
            Case Else
                MsgBox "Fehler: " & .Number & vbLf & .Description
        End Select
    End With
End Sub


Bild

Betrifft: AW: Arbeitsblattname als Variable
von: Gerd
Geschrieben am: 14.09.2015 10:48:13
Hallo Peter,
das Einlesen des Blattnamens funktioniert. Den Rest vom Makro finde ich nicht so gut. Das habe ich mir anders vorgestellt - trotzdem Dank.
Hallo Franz,
es stimmt, die AM "Bericht" & "Beamer" sind immer gleichzeitig geöffnet, sodass ich mit INDIREKT(Bezug;A1) arbeiten kann. Deine Syntax habe ich kopiert und getestet, aber sie funktioniert leider nicht. Ich verstehe sie auch nicht ganz - besonders den letzte Teil ZELLE("adresse";A3);WAHR. Erläutere mir bitte die komplette Syntax. Kann man dort nicht die Variablen strBeamer und strBlatt verwenden?
Bisher habe ich mit Verknüpfungen Zelle='[Beamer.xls]Anzeige'!$A$1 usw. gearbeitet. Ist es möglich, diese Verknüpfungen weiterhin unter Verwendung der Variablen strBeamer und strBlatt zu nutzen? Wäre mir die liebste Lösung, weil ich sie verstehe.
Danke & Gruß
Gerd

Bild

Betrifft: AW: Arbeitsblattname als Variable
von: fcs
Geschrieben am: 14.09.2015 11:59:21
Hallo Gerd,
damit du mit INDIREKT arbeiten kannst muss du alle variablen Teile (Dateiname, Blattname und Zelladressen) irgendwie bestimmen,damit am Ende eine Formel wie

='[Beamer.xls]Anzeige'!$A$1

herauskommt. Entweder aus einer Zelle einlesen oder in der Formel berechnen oder in der Formel fest vorgeben.
Sinnvoll ist in deinem Fall den Dateinamen und den Blattnamen aus Bericht in zwei beliebige Zellen im Blatt Anzeige zu schreiben. In den INDIREKT-Formeln werden diese Infos dann aus den Zellen ausgelesen. Du könntest diese beiden Zellen auch mit einem Namen versehen und die Namen in den Formeln verwenden.
=INDIREKT("'[" & $B$4 & "]" & $B$5 &"'!"& ZELLE("adresse";A3);WAHR)
=INDIREKT("'[" & Bericht_Datei & "]" & Bericht_Blatt &"'!"& ZELLE("adresse";A3);WAHR)
Der Teil ZELLE("adresse";A3) legt fest aus welcher Zelle des Bericht_Blatt der Wert ausgelesen werden soll - hier aus A3.
Diese Form ist dann hilfreich, wenn man mehrere Werte in einer Zeile oder Spalte per INDIREKT übernehmen will. Man kann die Zellen mit der Formel dann kopieren und die Zelladressen werden automatisch angepasst.
In der einfachen Form der Formel ist dies nicht der Fall.
=INDIREKT("'[" & Bericht_Datei & "]" & Bericht_Blatt &"'!A3";WAHR)
Hier muss man nach dem Kopieren in den Formeln die auszulesenden Zellen manuell korrigieren.
Es ist aber nicht möglich die Variablen strDatei und strBlatt in die Formeln einzusetzen. Das würde nur funktionieren, wenn man per Makro sämtliche Formeln im Blatt Anzeige neu schreiben würde.
        With Workbooks(strBeamer)
            With .Worksheets("Anzeige")
                .Range("A7").FormulaR1C1 = "=INDIRECT(""'[" & strDatei & "]" _
                      & strBlatt & "'!" & "A3" & """,True)"
                .Range("C7").FormulaR1C1 = "=INDIRECT(""'[" & strDatei & "]" _
                      & strBlatt & "'!" & "B3" & """,True)"
                'usw.
            End With
            .Activate
        End With

Ansonsten musst du dich mal mit der Hilfe zu INDIREKT befassen, was die Syntax angeht kann ich dir nicht nichts anderes erzählen.
Gruß
Franz

Bild

Betrifft: AW: Arbeitsblattname als Variable
von: Gerd
Geschrieben am: 14.09.2015 14:31:47
Hallo Franz,
ganz herzlichen Dank für die tollen Anleitungen, Tipps und Erläuterungen. Nach einigem Probieren hat auch alles super funktioniert! Dank deiner Hilfe habe ich sogar die Syntaxen kapiert.
Das ist das erste Mal, dass mir jemand in einem Forum wirklich geholfen hat!!!
Bei allen anderen Hilfeversuchen in diversen Foren (nicht nur zu EXCEL, Makros und VBA) hat sich nie wirkliche Hilfe ergeben. Es sind meist selbst ernannte "Experten", die versuchen zu "helfen" - bisher nur blanke Enttäuschung.
Nochmals danke & Tschüssikovski
Gruß Gerd
Userbild

 Bild

Beiträge aus den Excel-Beispielen zum Thema "Arbeitsblattname als Variable"