Excel Indirekt-Alternative. Geschlossene Dateien?
Hallo,
Ich habe derzeit das Problem, dass ich mittels Indirekt auf Werte aus einer anderen Mappe zugreife. Funktioniert, aber die Mappe muss geöffnet sein.
Deshalb habe ich ein VBA, welches diese Mappe öffnet und wieder schließt.
Da es sich jedoch nicht nur um eine Mappe, sondern ca. 30 handelt dauert das öffnen und schließen ziemlich lange, da die Dateien auf einem Server liegen.
Ich suche nach einer Möglichkeit auf die geschlossenen Mappen zuzugreifen und die Werte zu übernehmen ohne die Mappen zu öffnen. Geht das irgendwie?
Die derzeitige Formel in der entsprechenden Zelle sieht so aus: =INDIREKT(" '["&R10&"]"&$B$1&"'!"&"$C$10")
In R10 steht der Name der Mappe.
In B1 steht der Name des Tabellenblattes.
Beides ist variabel.
Vielen Dank.
1 Antwort
Ja, über einen Umweg kann man auch geschlossene Dateien auslesen, wie genau wird hier beschrieben
Da du in deiner Indirektformel ohnehin nur absolute Bezüge verwendest, sollte es auch keine Probleme geben diese direkt im Makro als Variablen zu vergeben.
Du vermutest den Fehler an der richtigen Stelle: das Ansprechen der Zelle funktioniert in VBA nicht über R10-Schreibweise sondern über .Cells bzw .Range
datei = ActiveSheet.Cells(Zeile, Spalte).Value
bereich = ActiveSheet.Cells(Zeile, Spalte).Range("Bereichsname").Value
also z.B um R10 (welches Zeile=10 und Spalte=18 entspricht) auszulesenkannst du wahlweise
datei = ActiveSheet.Cells(10, 18).Value oder
datei = ActiveSheet.Cells(Zeile, Spalte).Range("R10").Value
verwenden
Das sich das ganze für jede Zeile durchführt kannst du mit einer Schleife erreichen, also in dieser Art:
for i=1 to 2
datei = ActiveSheet.Cells(10+i-1, 18).Value
Next i
wenn ich dich richtig verstehe, sollte das hier machen was du möchtest:
Sub Werteuebertragen()
'
'** Dimensionierung der Variablen
Dim pfad As String, datei As String, blatt As String, zelle As String
'** Angaben zur auszulesenden Zelle
pfad = "C:\Beispielpfad\"
blatt = ThisWorkbook.Sheets("Tabelle1").Cells(1, 2).Value '$B$1
bezug = ThisWorkbook.Sheets("Tabelle1").Cells(10, 3).Value '$C$10
'** Eintragen in Zelle
For i = 10 To 26
datei = ThisWorkbook.Sheets("Tabelle1").Cells(i, 18).Value 'R10 bis R26
ThisWorkbook.Sheets("Tabelle1").Cells(i, 2) = GetValue(pfad, datei, blatt, bezug) 'B10 bis B26
Next i
End Sub
Private Function GetValue(pfad, datei, blatt, zelle)
'** Daten aus geschlossener Arbeitsmappe auslesen
'*** Dimensionierung der Variablen
Dim arg As String
'Sicherstellen, dass das datei vorhanden ist
If Right(pfad, 1) <> "\" Then pfad = pfad & "\"
If Dir(pfad & datei) = "" Then
GetValue = "Datei nicht gefunden"
Exit Function
End If
'** Das Argument erstellen
arg = "'" & pfad & "[" & datei & "]" & blatt & "'!" & Range(zelle).Range("C10").Address(, , xlR1C1)
'** Auslesen über Excel4Macro
GetValue = ExecuteExcel4Macro(arg)
End Function
Hab jetzt schon viel rumprobiert und der Code stimmt wahrscheinlich auch. Trotzdem kommt eine Fehlermeldung:
Laufzeitfehler '1004':
Die Methode 'Range' für das Objekt'_Global' ist fehlgeschlagen
Sub Werteuebertragen()
'
'** Dimensionierung der Variablen
Dim pfad As String, datei As String, blatt As String, zelle As String
'** Angaben zur auszulesenden Zelle
pfad = ThisWorkbook.Path
blatt = ThisWorkbook.Sheets(1).Cells(1, 2).Value '$B$1
zelle = ThisWorkbook.Sheets(1).Cells(10, 3).Value '$C$10
'** Eintragen in Zelle
For i = 10 To 26
datei = ThisWorkbook.Sheets(1).Cells(i, 18).Value 'R10 bis R26
ThisWorkbook.Sheets(1).Cells(i, 2) = GetValue(pfad, datei, blatt, zelle) 'B10 bis B26
Next i
End Sub
Private Function GetValue(pfad, datei, blatt, zelle)
'** Daten aus geschlossener Arbeitsmappe auslesen
'*** Dimensionierung der Variablen
Dim arg As String
'Sicherstellen, dass das datei vorhanden ist
If Right(pfad, 1) <> "\" Then pfad = pfad & "\"
If Dir(pfad & datei) = "" Then
GetValue = "Datei nicht gefunden"
Exit Function
End If
'** Das Argument erstellen
arg = "'" & pfad & "[" & datei & "]" & blatt & "'!" & Range(zelle).Range("A1").Address(, , xlR1C1)
'** Auslesen über Excel4Macro
GetValue = ExecuteExcel4Macro(arg)
End Function
Der Debugger zeigt den Fehler hier:
arg = "'" & pfad & "[" & datei & "]" & blatt & "'!" & Range(zelle).Range("A1").Address(, , xlR1C1)
Bzw. erst in diesem Teil:
Range(zelle).Range("A1").Address(, , xlR1C1)
Leider ist das gerade der Teil den ich sowieso nicht verstehe. Dieser gibt doch die Zelle an, die ausgelesen werden soll?
Aber auch mit
Range(zelle).Range("C10").Address(, , xlR1C1)
ändert sich nichts.
Ist der Code evtl. richtig, jedoch habe ich in den Zellen was falsch gemacht, da in den ganzen Foren-Codes die ich gefunden habe auch überall "A1" stand?
Vielen Dank für deine Zeit und deine Bemühungen mir da weiter zu helfen.
LG KeinPlanhaber
Wie es genau funktioniert, verstehe ich auch noch nicht ganz, aber beim herumspielen hab ich festgestellt das dieser Teil:
Range(zelle).Range("A1").Address(, , xlR1C1)
relative Zellkoordinaten in Bezug zu A1 erzeugt, demnach müsste der Verursacher des Fehlers die Variable "zelle" sein.
Anscheinend hatte ich gestern nicht richtig gelesen und gedacht in C10 stehen die Koordinaten der Zelle die du auslesen willst, funktioniert es wenn du
zelle = ThisWorkbook.Sheets(1).Cells(10, 3).Value '$C$10
durch
zelle= "C10"
ersetzt?
Ach ja. Natürlich. Manchmal ist der Fehler gar nicht so schwer.
Hätte man auch selber drauf kommen können!
Jetzt funktioniert es bestens. Alles perfekt und deutlich schneller.
Vielen, vielen Dank!!!
Das ist echt super. Toll beschreiben.
Leider mache ich etwas mit der Pfadeingabe falsch:
Ich vermute hier nen Fehler drin zu haben:
Der Dateiname steht in Zelle R10, der Blattname in B1, die Zelle, aus welcher ich den Wert haben möchte ist C10.
Das ganze soll in mehreren Zellen geschehen, also es fängt in B10 an und soll den Wert von C10 aus Blatt (Wert aus Zelle B1) der Datei (Wert aus Zelle R10) übertragen.
In Zelle B11 soll dann der Wert von C10 des Blattes (Wert aus Zelle B1) der Datei (Wert aus R11) übertragen werden. Usw. (bis B26)
Wo ist der Fehler?
Das mit dem Argument habe ich auch nicht ganz verstanden.