Zelle mit VBA in einer geschlossenen Arbeitsmappe auslesen

[dev5fr]

Hi,

ich habe eine Funktion die leider nicht das tut, was ich gern möchte. Der Teufel steckt im Detail, vielleicht habt ihr eine Idee was ich anders machen könnte.
Es soll eine Zelle in einem anderen, geschlossenen Dokument ausgelesen werden.

Code:

Function GetValueFromClosedWorkbook(filePath As String, sheetName As String, cellAddress As String) As Variant
    Dim wb As Workbook
    Dim ws As Worksheet
   
    On Error Resume Next
    Set wb = Workbooks.Open(filePath, ReadOnly:=True)
    On Error GoTo 0
   
    If Not wb Is Nothing Then
        Set ws = wb.Sheets(sheetName)
        If Not ws Is Nothing Then
            GetValueFromClosedWorkbook = ws.Range(cellAddress).Value
        Else
            GetValueFromClosedWorkbook = "Sheet not found"
        End If
        wb.Close False
    Else
        GetValueFromClosedWorkbook = "Workbook not found"
    End If
End Function

Der Aufruf dieser Funktion lautet so:

Code:

=GetValueFromClosedWorkbook("https://home-my.sharepoint.com/familie/dev5fr_home/Documents/" & A7, "Tabelle1", "D4")

In der Zelle A7 steht der Dateiname drin. Wenn ich die Funktion so verwende, dann beschwert sich EXCEL mit einer Fehlermeldung - das ist keine Formel - und springt im Eingabeprompt an die Stelle wo der Zellbezug steht, also hier "A7".

Was mache ich falsch?
omClosedWorkbook("https://bosch-my.sharepoint.com/personal/dev5fr_bosch_com/Documents/Dokumente/" & A7, "Tabelle1", "D4")

[Gast 123]

Hallo

Tipp von mir.  Stelle von Hand eine Verknüpfung zur anderen Datei her und schließe diese.
Jetzt hast du in der Zelle die gültige Formel mit Pfad und Tabellenangabe stehen.
Übernehme diese Formel in ein Makro mit Range()Formula bis es klappt. s. unten
Danach wandelt der Code diese Formel in einen Wert um. Das ist alles.

mfg Gast 123

Code:

Sub Test()
Selection.Formula = "='E:\Forum Herber Apr 2025\[Test.xlsm]Tabelle1'!$B$2"
Selection.Value = Selection.Value  'in Werte umwandeln
End Sub

---

Nachtrag:   bitte bei der Formel auf die Feinheiten mit dem ' Zeichen vor E und nach Tabelle achten! Sonst klappt es nicht!

[HKindler]

Hi,

du kannst so etwas nicht als Zellformel verwenden. Einige Dinge wie z.B. eine Datei öffnen sind bei UDFs (User Defined Functions) einfach nicht erlaubt.

PS: Die Fehlermeldung von Excel erhältst du, weil du "," statt ";" in einer Formel verwendest. Aber selbst wenn du das korrigierst, wirst du nicht das erreichen, was du willst. Denn es wird dir immer nur der Text "Workbook not found" angezeigt werden. Eben weil das Öffnen der Datei fehl schlägt.

[dev5fr]

Hey, danke für dein Feedback.

Wenn ich das Script mit =Test() aufrufe, dann kommt die Fehlermeldung "Die Syntax dieses Namens ist nicht richtig".

---

Hallo Helmut,
dann geht das, was ich vorhabe gar nicht oder nur so, wie ich es versuche nicht?

[schauan]

Hallöchen,

Sub Test() ruft man nicht als Formel auf - Wie Du am Anfang schon hattest geht das nur mit einer Function.
Hier noch was anderes:

Daten aus geschlossener Mappe (1) - Mit Excel4Macro

[DeltaX]

Hier ausführlicher
Getting Data From Closed Workbook Using GetValue - VBA Visual Basic for Applications (Microsoft) | Tek-Tips
vba - Excel #VALUE! error when using GetValue - Stack Overflow

Code:

Private Function GetValue(path, file, sheet, ref)
'   Retrieves a value from a closed workbook
Dim arg As String
    '   Make sure the file exists
    If Right(path, 1) <> "\" Then path = path & "\"
    If Dir(path & file) = "" Then
        GetValue = "File Not Found"
        Exit Function
    End If
    '   Create the argument
    arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
          Range(ref).Range("A1").Address(, , xlR1C1)
    '   Execute an XLM macro
    GetValue = ExecuteExcel4Macro(arg)
End Function

[Gast 123]

Hallo

was ich dir geschickt habe ist ein simples Makro was in ein normales Modul gehört.
Du musst im Code natürlich deinen Pfad und deine Mappe angeben, und die Tabelle mit VBA -CodeNamen- angeben!
Dann sollte es klappen.  Habe es mit meiner Test Datei getestet. Den CodeNamen findest du im VBA Editor oder s. unten.

mfg Gast 123

Code:

Sub test()
MsgBox Sheets("Tabelle1").CodeName
End Sub

[HKindler]

Hi,

auch diese Function()s kann man schlicht nicht innerhalb einer Excel-Formel verwenden.

Man könnte höchstens im Modul das entsprechenden Arbeitsblattes folgendes definieren:

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1:A4")) Is Nothing Then
    Range("B1") = GetValue(Range("A1"), Range("A2"), Range("A3"), Range("A4"))
End If
End Sub

Private Function GetValue(path, file, sheet, ref)
'   Retrieves a value from a closed workbook
    Dim arg As String
'   Make sure the file exists
    If Right(path, 1) <> "\" Then path = path & "\"
    If Dir(path & file) = "" Then
        GetValue = "File Not Found"
        Exit Function
    End If
'   Create the argument
    arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
      Range(ref).Range("A1").Address(, , xlR1C1)
'   Execute an XLM macro
    GetValue = ExecuteExcel4Macro(arg)
End Function

Das würde die Zellen A1:A4 überwachen. Wobei A1 der Pfad, A2 die Datei, A3 das Blatt und A4 die Zelle sind. Bei Änderung einer der Zellen wird der Wert in B1 aktualisiert. Aber das ist alles andere als flexibel, da die einzelnen Zellen fest im Makro hinterlegt sind und sich nicht ohne weiteres ändern lassen.

Wenn man oben auf GetValue() verzichten will, dann kann man statt

Code:

Range("B1") = GetValue(Range("A1"), Range("A2"), Range("A3"), Range("A4"))

auch folgendes verwenden

Code:

Range("B1").Formula = "='" & Range("A1") & "\[" & Range("A2") & "]" & Range("A3") & "'!" & Range("A4")
Range("B1") = Range("B1")

Aber so etwas wie GetValue() direkt in einer Excel-Formel zu verwenden, da sehe ich keinen Weg.