Salu Kollegen,
sehe ich das richtig, daß der Zugriff auf Zellwerte geschlossener Workbooks (Mapen) mit einer normalen Excel-Formel möglich ist, nicht aber mit VBA?
Hintergrund: Ich möchte eine Spalte mit Werten füllen, die mal aus der einen, mal aus einer anderen Mappe kommen. Da selbst das teure (volatile Funktion) INDIREKT das nicht ermöglicht, wollte ich das mit einer Funktion umsetzen.
Vielen Dank und schöne Grüße
-Rob.
Grüezi Rob
sehe ich das richtig, daß der Zugriff auf Zellwerte
geschlossener Workbooks (Mapen) mit einer normalen
Excel-Formel möglich ist, nicht aber mit VBA?
Eine direkte Bezugs-Formel kannst Du immer in eine Zelle schreiben (lassen) und dort dann den Wert aus der externen Datei haben.
Dasselbe mit VBA läuft auf einen Code hinaus, der die Bezugs-Formel in die Zelle schreibt und anschliessend durch den importierten Wert ersetzt.
Ich habe das mal in eine VBA-Funktion zusammengefasst, die dann automatisiert und auch über Schleifen aufgerufen werden kann und die Werte so aus nicht geöffneten Mappen importiert:
Public Function GetDataClosedWB(SourcePath As String, \_
SourceFile As String, \_
SourceSheet As String, \_
SourceRange As String, \_
TargetRange As Range, \_
Optional blFormula As Boolean) As Boolean
'Holt einen Bereich aus einer \_geschlossenen\_ Arbeitsmappe
'Nur in VBA zu verwenden; nicht aus einer Tabellenzelle heraus
'© [email protected]
Dim strQuelle As String
Dim Zeilen As Long
Dim Spalten As Byte
On Error GoTo InvalidInput
If Right(SourcePath, 1) "\" Then SourcePath = SourcePath & "\"
strQuelle = "'" & SourcePath & "[" & SourceFile & "]" & \_
SourceSheet & "'!" & \_
Range(SourceRange).Cells(1, 1).Address(0, 0)
Zeilen = Range(SourceRange).Rows.Count
Spalten = Range(SourceRange).Columns.Count
With TargetRange.Cells(1, 1).Resize(Zeilen, Spalten)
.Formula = "=IF(" & strQuelle & "="""",""""," & strQuelle & ")"
If Not blFormula Then .Value = .Value
End With
GetDataClosedWB = True
Exit Function
InvalidInput:
MsgBox "Die Quelldatei oder der Quellbereich ist ungültig!", \_
vbExclamation, "Get data from closed Workbook"
GetDataClosedWB = False
End Function
Dann hier ein Beispiel wie der Code aufgerufen werden kann/soll:
Public Sub HoleDaten()
Dim Pfad As String
Dim Dateiname As String
Dim Blatt As String
Dim Bereich As String
Pfad = "C:\Users\Test\Documents\Stick\"
Dateiname = "MeineMappe.xlsm"
Blatt = "Test1"
Bereich = "A9:smiley:9"
GetDataClosedWB Pfad, \_
Dateiname, \_
Blatt, \_
Bereich, \_
Range("D1"), \_
True
End Sub
Mit freundlichen Grüssen
Thomas Ramel
Lösung: Funktion GetValue()
Salu Thomas,
Eine direkte Bezugs-Formel kannst Du immer in eine Zelle
schreiben (lassen) und dort dann den Wert aus der externen
Datei haben.Dasselbe mit VBA läuft auf einen Code hinaus, der die
Bezugs-Formel in die Zelle schreibt und anschliessend durch
den importierten Wert ersetzt.Ich habe das mal in eine VBA-Funktion zusammengefasst, die
dann automatisiert und auch über Schleifen aufgerufen werden
kann und die Werte so aus nicht geöffneten Mappen importiert:
Mensch, vielen vielen Dank für diese Rundumsorglos-Lösung. Das ist ja superst! Der Weg ist zwar recht umständlich, aber wenn er funktioniert ist mir sehr geholfen.
All das ist nötig, da unsere freigegebene Mappe oft hängengeblieben bzw. abgestürzt ist. Daher verteilen wir nun die Informationen auf einzelne Mappen, die nicht mehr von mehreren gleichzeitig bearbeitet werden müssen und aggregieren die Inhalte in einer anderen Mappe. Letztere benötigt dann eben den Zugriff auf geschlossene Mappen.
Allerbesten Dank nochmals, Sternchen und viele Grüße zum Fest :o)
-Rob.