Auf geschlossene xlsm Datei zugreifen

Forumthread: Auf geschlossene xlsm Datei zugreifen

13.09.2020 15:46:32

Tobias

Hallo liebe Community,
ich habe folgendes Problem:
Ich habe eine Master-Datei, in welcher ich Daten wie Mitarbeiternamen oder Städte mit Referenznummern stetig aktualisiere.
Jeder Mitarbeiter soll eine eigene Anwenderdatei führen.
Dort soll er via DropDown (durch VBA programmiert) die Namen auswählen können oder die Städte (bei jenen wird dann via SVerweis die Referenznummer gesucht).
Beide Dateien sollen auf einem Server im gleichen Ordner liegen.
Soweit so gut.
Nun möchte ich gerne, dass sich die Anwender-Datei die Daten aus der geschlossenen Masterdatei zieht.
Ich habe dies auf meinem PC getestet, bekomme es aber leider nicht in die VBA Programmierung eingebettet.
Beispiel Masterdatei:
https://www.herber.de/bbs/user/140210.xlsm
Beispiel Anwender Datei:
https://www.herber.de/bbs/user/140211.xlsm
Hier habe ich, ohne VBA, den Verweis hinbekommen: ='C:\Users\Tobia\Desktop\Ordner\[Master_Bsp.xlsm]Master'!A2
Mir fehlt also die notwendige Eingabe zwischen den " " siehe Foto:
Userbild

Über Eure Hilfe wäre ich sehr dankbar.
Einen schönen Sonntag,
Tobias

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

AW: Auf geschlossene xlsm Datei zugreifen

13.09.2020 16:34:57

Nepumuk

Hallo Tobias,
im Modul der Tabelle:

Option Explicit

Private mavntList1 As Variant
Private mavntList2 As Variant
Private mavntList3 As Variant

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    ComboBox1.Visible = False

    '--- Prüfen, ob Combobox gezeigt werden muss
    If Target.Row <= 1 Then Exit Sub 'abbruch bei Überschrift
    If Target.Cells.Count > 1 Then Exit Sub 'abbruch wenn mehrere Zellen
    Select Case Target.Column
        Case 1, 2, 3 'hier die relevanten Spalten aufführen
        Case Else
            Exit Sub
    End Select

    '--- Combobox positionieren
    ComboBox1.Top = Target.Top - 1
    ComboBox1.Left = Target.Left - 1
    ComboBox1.Height = Target.Height + 5
    ComboBox1.Width = Target.Width + 16

    '--- Zellbereiche zufügen
    Select Case Target.Column
        Case 1: ComboBox1.List = List1
        Case 2: ComboBox1.List = List2
        Case 3: ComboBox1.List = List3
    End Select

    '--- ausgabezelle
    ComboBox1.LinkedCell = Target.Address

    '--- Anzeigen und Focus
    ComboBox1.Visible = True
    ComboBox1.Activate


End Sub

Friend Property Get List1() As Variant
    List1 = mavntList1
End Property

Friend Property Let List1(ByRef pravntList As Variant)
    mavntList1 = pravntList
End Property

Friend Property Get List2() As Variant
    List2 = mavntList2
End Property

Friend Property Let List2(ByRef pravntList As Variant)
    mavntList2 = pravntList
End Property

Friend Property Get List3() As Variant
    List3 = mavntList3
End Property

Friend Property Let List3(ByRef pravntList As Variant)
    mavntList3 = pravntList
End Property

Im Modul der Mappe (DieseArbeitsmappe):

Option Explicit

Private Sub Workbook_Open()

    Dim objWorkbook As Workbook

    Set objWorkbook = GetObject(Pathname:=ThisWorkbook.Path & "\Masterdatei.xlsm")

    With objWorkbook.Worksheets("Master")
        Tabelle1.List1 = .Range(.Cells(2, 1), .Cells(.Rows.Count, 1).End(xlUp)).Value2
        Tabelle1.List2 = .Range(.Cells(2, 2), .Cells(.Rows.Count, 2).End(xlUp)).Value2
        Tabelle1.List3 = .Range(.Cells(2, 3), .Cells(.Rows.Count, 3).End(xlUp)).Value2
    End With

    Call objWorkbook.Close(SaveChanges:=False)
    Set objWorkbook = Nothing

End Sub

Ohne einmal öffnen geht es nicht. Das passiert aber ohne für den User sichtbar zu werden.
Gruß
Nepumuk

AW: Auf geschlossene xlsm Datei zugreifen

13.09.2020 19:06:54

Nepumuk

Hallo Tobias,
ich habe es noch etwas schneller bekommen:

Option Explicit

Private mavntList1 As Variant
Private mavntList2 As Variant
Private mavntList3 As Variant

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    ComboBox1.Visible = False

    '--- Prüfen, ob Combobox gezeigt werden muss

    If Target.Row > 1 Then

        If Target.CountLarge = 1 Then

            Select Case Target.Column

                Case 1, 2, 3 'hier die relevanten Spalten aufführen

                    With ComboBox1

                        '--- Combobox positionieren
                        .Top = Target.Top - 1
                        .Left = Target.Left - 1
                        .Height = Target.Height + 5
                        .Width = Target.Width + 16

                        '--- Zellbereiche zufügen
                        .List = CallByName(Me, "List" & CStr(Target.Column), VbGet)

                        '--- Ausgabezelle
                        .LinkedCell = Target.Address

                        '--- Anzeigen und Focus
                        .Visible = True
                        .Activate

                    End With
            End Select
        End If
    End If
End Sub

Public Property Get List1() As Variant
    List1 = mavntList1
End Property

Friend Property Let List1(ByRef pravntList As Variant)
    mavntList1 = pravntList
End Property

Public Property Get List2() As Variant
    List2 = mavntList2
End Property

Friend Property Let List2(ByRef pravntList As Variant)
    mavntList2 = pravntList
End Property

Public Property Get List3() As Variant
    List3 = mavntList3
End Property

Friend Property Let List3(ByRef pravntList As Variant)
    mavntList3 = pravntList
End Property

Gruß
Nepumuk

AW: Auf geschlossene xlsm Datei zugreifen

14.09.2020 13:25:45

Tobias

Hallo Nepumuk und vielen Dank für deine schnelle Antwort!
Ich habe beide Varianten ausprobiert. Leider bekomme ich jedes Mal die Fehlermeldung:
Userbild

Diese wird mir immer im Debugger unter dem Abschnitt "Zellbereiche zufügen" angezeigt.
Userbild

Was mache ich falsch?
Liebe Grüße
Tobias

AW: Auf geschlossene xlsm Datei zugreifen

14.09.2020 13:42:03

Nepumuk

Hallo Tobias,
du musst die Mappe einmal schließen und wieder öffnen. Die Datenarrays werden beim öffnen der Mappe initialisiert (Workbook_Open).
Gruß
Nepumuk

AW: Auf geschlossene xlsm Datei zugreifen

14.09.2020 14:43:14

Tobias

Danke! Das hat funktioniert :-)
Wenn ich die Daten in der Tabelle der Masterdatei nun in den Spalten 1-24 (A-X) habe und in der Anwenderdatei diese Listen (Spalten) aufteilen möchte, muss ich doch bei "Case" die jeweiligen Spalten angeben:

Inwiefern muss ich diese Abschnitte anpassen?
Userbild

Vielen Dank für deine schnelle und kompetente Hilfe, lieber Nepumuk!
Gruß
Tobias

AW: Auf geschlossene xlsm Datei zugreifen

14.09.2020 15:44:16

Nepumuk

Hallo Tobias,
da muss an mehreren Stellen geändert werden.
Im Modul "DieseArbeitsmappe":

Option Explicit

Private Sub Workbook_Open()

    Dim objWorkbook As Workbook
    Dim lngColumn As Long

    Set objWorkbook = GetObject(Pathname:=ThisWorkbook.Path & "\Masterdatei.xlsm")

    With objWorkbook.Worksheets("Master")

        For lngColumn = 1 To 24

            Call CallByName(Tabelle1, "List" & CStr(lngColumn), VbLet, _
                .Range(.Cells(2, lngColumn), .Cells(.Rows.Count, lngColumn).End(xlUp)).Value)

        Next
    End With

    Call objWorkbook.Close(SaveChanges:=False)
    Set objWorkbook = Nothing

End Sub

Im Modul der Tabelle:

Option Explicit

Private mavntList1 As Variant
Private mavntList2 As Variant
Private mavntList3 As Variant
Private mavntList4 As Variant
Private mavntList5 As Variant
Private mavntList6 As Variant
Private mavntList7 As Variant
Private mavntList8 As Variant
Private mavntList9 As Variant
Private mavntList10 As Variant
Private mavntList11 As Variant
Private mavntList12 As Variant
Private mavntList13 As Variant
Private mavntList14 As Variant
Private mavntList15 As Variant
Private mavntList16 As Variant
Private mavntList17 As Variant
Private mavntList18 As Variant
Private mavntList19 As Variant
Private mavntList20 As Variant
Private mavntList21 As Variant
Private mavntList22 As Variant
Private mavntList23 As Variant
Private mavntList24 As Variant

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    ComboBox1.Visible = False

    '--- Prüfen, ob Combobox gezeigt werden muss

    If Target.Row > 1 Then

        If Target.CountLarge = 1 Then

            Select Case Target.Column

                Case 1 To 24 'hier die relevanten Spalten aufführen

                    With ComboBox1

                        '--- Combobox positionieren
                        .Top = Target.Top - 1
                        .Left = Target.Left - 1
                        .Height = Target.Height + 5
                        .Width = Target.Width + 16

                        '--- Zellbereiche zufügen
                        .List = CallByName(Me, "List" & CStr(Target.Column), VbGet)

                        '--- Ausgabezelle
                        .LinkedCell = Target.Address

                        '--- Anzeigen und Focus
                        .Visible = True
                        .Activate

                    End With
            End Select
        End If
    End If
End Sub

Public Property Get List1() As Variant
    List1 = mavntList1
End Property

Friend Property Let List1(ByRef pravntList As Variant)
    mavntList1 = pravntList
End Property

Public Property Get List2() As Variant
    List2 = mavntList2
End Property

Friend Property Let List2(ByRef pravntList As Variant)
    mavntList2 = pravntList
End Property

Public Property Get List3() As Variant
    List3 = mavntList3
End Property

Friend Property Let List3(ByRef pravntList As Variant)
    mavntList3 = pravntList
End Property

Public Property Get List4() As Variant
    List4 = mavntList4
End Property

Friend Property Let List4(ByRef pravntList As Variant)
    mavntList4 = pravntList
End Property

Public Property Get List5() As Variant
    List5 = mavntList5
End Property

Friend Property Let List5(ByRef pravntList As Variant)
    mavntList5 = pravntList
End Property

Public Property Get List6() As Variant
    List6 = mavntList6
End Property

Friend Property Let List6(ByRef pravntList As Variant)
    mavntList6 = pravntList
End Property

Public Property Get List7() As Variant
    List7 = mavntList7
End Property

Friend Property Let List7(ByRef pravntList As Variant)
    mavntList7 = pravntList
End Property

Public Property Get List8() As Variant
    List8 = mavntList8
End Property

Friend Property Let List8(ByRef pravntList As Variant)
    mavntList8 = pravntList
End Property

Public Property Get List9() As Variant
    List9 = mavntList9
End Property

Friend Property Let List9(ByRef pravntList As Variant)
    mavntList9 = pravntList
End Property

Public Property Get List10() As Variant
    List10 = mavntList10
End Property

Friend Property Let List10(ByRef pravntList As Variant)
    mavntList10 = pravntList
End Property

Public Property Get List11() As Variant
    List11 = mavntList11
End Property

Friend Property Let List11(ByRef pravntList As Variant)
    mavntList11 = pravntList
End Property

Public Property Get List12() As Variant
    List12 = mavntList12
End Property

Friend Property Let List12(ByRef pravntList As Variant)
    mavntList12 = pravntList
End Property

Public Property Get List13() As Variant
    List13 = mavntList13
End Property

Friend Property Let List13(ByRef pravntList As Variant)
    mavntList13 = pravntList
End Property

Public Property Get List14() As Variant
    List14 = mavntList14
End Property

Friend Property Let List14(ByRef pravntList As Variant)
    mavntList14 = pravntList
End Property

Public Property Get List15() As Variant
    List15 = mavntList15
End Property

Friend Property Let List15(ByRef pravntList As Variant)
    mavntList15 = pravntList
End Property

Public Property Get List16() As Variant
    List16 = mavntList16
End Property

Friend Property Let List16(ByRef pravntList As Variant)
    mavntList16 = pravntList
End Property

Public Property Get List17() As Variant
    List17 = mavntList17
End Property

Friend Property Let List17(ByRef pravntList As Variant)
    mavntList17 = pravntList
End Property

Public Property Get List18() As Variant
    List18 = mavntList18
End Property

Friend Property Let List18(ByRef pravntList As Variant)
    mavntList18 = pravntList
End Property

Public Property Get List19() As Variant
    List19 = mavntList19
End Property

Friend Property Let List19(ByRef pravntList As Variant)
    mavntList19 = pravntList
End Property

Public Property Get List20() As Variant
    List20 = mavntList20
End Property

Friend Property Let List20(ByRef pravntList As Variant)
    mavntList20 = pravntList
End Property

Public Property Get List21() As Variant
    List21 = mavntList21
End Property

Friend Property Let List21(ByRef pravntList As Variant)
    mavntList21 = pravntList
End Property

Public Property Get List22() As Variant
    List22 = mavntList22
End Property

Friend Property Let List22(ByRef pravntList As Variant)
    mavntList22 = pravntList
End Property

Public Property Get List23() As Variant
    List23 = mavntList23
End Property

Friend Property Let List23(ByRef pravntList As Variant)
    mavntList23 = pravntList
End Property

Public Property Get List24() As Variant
    List24 = mavntList24
End Property

Friend Property Let List24(ByRef pravntList As Variant)
    mavntList24 = pravntList
End Property

Gruß
Nepumuk

AW: Auf geschlossene xlsm Datei zugreifen

14.09.2020 16:12:46

max.kaffl@gmx.de

Hallo Tobias,
Nachtrag: Ich habe gerade gedacht ich spinne. Es hat auf einmal nicht mehr funktioniert. Ich habe aber die Ursache gefunden. Die Bezugsart in der Tabelle war auf R1C1 umgestellt damit ich die Spalten nicht an den Fingern abzählen muss. Diese Bezugsart wird bei der LinkedCell-Eigenschaft nicht angenommen. Damit das nicht bei einem Benutzer passiert, nimm bitte folgende Änderung vor:

Und nein, es genügt nicht die Address-Methode anzupassen, das habe ich getestet.
Gruß
Nepumuk

AW: Auf geschlossene xlsm Datei zugreifen

14.09.2020 16:55:23

Tobias

Mensch Nepumuk, vielen Dank! Einfach klasse. Du hast mir sehr weitergeholfen.

Beliebteste Forumthreads (12 Monate)

Auf geschlossene xlsm Datei zugreifen

Forumthread: Auf geschlossene xlsm Datei zugreifen

8
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

Beliebteste Forumthreads (12 Monate)

Auf geschlossene xlsm Datei zugreifen

Forumthread: Auf geschlossene xlsm Datei zugreifen

8 Beiträge zum Forumthread Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

VBA Beispiele zu diesem und ähnlichen Themen

Beliebteste Forumthreads (12 Monate)

8
Beiträge zum Forumthread

Beiträge zu diesem Forumthread