Office: Daten aus geschlossener Exceltabelle auslesen

Hallo zusammen,

ich bräuchte da noch einmal dringend eure Hilfe.

Ich versuche Daten aus einer geschlossenen Excel Tabelle auszulesen und erhalte immer wieder eine Fehlermeldung "Index ausserhalb des Bereiches...".

Der Fehler taucht bei der Set Anweisung für rng_Quelle auf:


Erst starte ich die SUB...

Sub Bereich_auslesen()

'** Dimensionierung der Variablen
Dim Pfad As String, datei As String, Blatt As String, Bereich As String, alias As String
Dim int_Value As Integer
Dim date_Value As Date


int_Punktenachher = 0
int_Punktevorher = 0

'** Angaben zur auszulesenden Zelle
Pfad = ActiveWorkbook.Path & "Ergebnisse"
datei = "Ergebnisse.xlsx"
Blatt = "ISMS"
Bereich = "ISMS_Daten"
alias = p_strAlias

'*** Wert über die Function GetBestValuePunkte auslesen

int_Value = GetBestValuePunkte(Pfad, datei, Blatt, Bereich, alias)
lblBestpunkte.Caption = int_Value

End Sub


Und hier die Function dazu...

Private Function GetBestValuePunkte(Pfad, datei, Blatt, Bereich, alias)
'** Daten aus geschlossener Arbeitsmappe auslesen

'*** Dimensionierung der Variablen
Dim int_Zaehler As Integer
Dim int_Punktevorher As Integer
Dim int_Punktenachher As Integer
Dim rng_Quelle As Range
'Dim arg As String

'!!! Hier entsteht der Fehler in der nächsten Zeile
Set rng_Quelle = Workbooks(datei).Sheets(Blatt).Range(Bereich)

'***Sicherstellen, dass das datei vorhanden ist
If Right(Pfad, 1) "" Then
Pfad = Pfad & ""

If Dir(Pfad & datei) = "" Then
GetValue = "datei Not Found"
Exit Function
End If

End If

'***Den maximalen Wert für den entsprechenden Alias auslesen

For int_Zaehler = 1 To rng_Quelle.Rows.Count
If rng_Quelle(int_Zaehler, 1) = alias Then
rng_Quelle(int_Zaehler, 2).Select
int_Punktenachher = Selection

If int_Punktenachher > int_Punktevorher Then
int_Punktevorher = int_Punktenachher
End If
Else
End If

Next int_Zaehler

'** Auslesen über Excel4Macro
GetBestValuePunkte = ExecuteExcel4Macro(int_Punktevorher)

End Function

Zusatz:
Die Datei existiert im angegebenen Pfad und besitzt auch die Tabelle "ISMS" und den Bereich "ISMS_Daten"

Vielen Dank für eure Hilfe
Maurice

:)

 

Hallo Maurice,

Workbooks(datei).Sheets(Blatt).Range(Bereich)
erwartet, daß die in der Variablen 'datei' hinterlegte Arbeitsmappe (AM) in der Auflistung der gerade geöffneten Arbeitsmappen der gleichen Excelinstanz vorhanden ist; sprich - diese AM mußßßß geöffnet sein.
Ich frage mich, aus wievielen Internet-Code-Stücken dieser Code zusammengewürfelt ist?
Außerdem ist der Begriff 'geschlossene Arbeitsmappe' sowieso irreführend. Irgendwie ist jede Datei geöffnet, will man Daten daraus aulesen.

Gruß von Luschi
aus klein-Paris

 

Erst einmal frage ich mich immer wieder, warum eine Mappe geschlossen sein/bleiben muss, wenn ich damit arbeiten will (das als Ergänzung zu Luschis Anmerkung).

Und:

Da habe ich einen heißen Tipp für dich!

 

\@Günther,

....weil es wirklich um Längen schneller ist.
Ich hatte hier auch schon mal eine Lösung dazu geliefert, sehe aber nicht ein, weshalb ich ohne Notwendigkeit nach meinen eigenen Beiträgen googeln sollte....*wink.gif*
Also Maurice, ich kann Dir versichern, es gibt dazu bereits eine fertige Lösung. Aber wie gesagt, suchen kannst Du sicher allein...

 

\@Luschi
Vielen Dank für die Erläuterung zur AM.

Zu Deiner Frage:

Ich habe verzweifelt nach einer Lösung gesucht und diese gefunden. Die habe ich dann an meine Umgebung versucht anzupassen und bin gescheitert.
Wenn Du von Anfang an schon Profi gewesen bist, dann entschuldige ich mich für die Belästigung, aber ich dachte dieses Forum wäre auch für Einsteiger und Fortgeschrittene.
Aber besonders hilfreich warst Du mir jedenfalls nicht.

@Drogist
Danke für den Tipp... aber auch hier möchte ich darauf aufmerksam machen, dass es nicht darum geht, dass ich euch drängen möchte, sondern lediglich (schlecht formuliert) auf mein Not hinweisen wollte, um nicht den Eindruck zu hinterlassen, dass ich nicht selbst schon viele Suchen im Internet vorgenommen hätte.
Ich denke aber auch hier stand die Hilfe nicht im Vordergrund für Dich, sondern viel mehr die Maßregelung. Vielen Dank dafür.

@ R J
Dir danke ich für den Tipp, da Du offensichtlich der einzige bist, der sich dem eigentlichen Thema annehmen wollte.
Ich werde weiter versuchen den Beitrag zu finden. Habe es auch schon versucht, bin aber nicht erfolgreich gewesen.
Daher mein Gesuch nach "dringender" Hilfe, da mich dieses Thema jetzt schon 2 Tage kostet und das der letzte Schritt zu meinem ansonsten sehr gut funktionierenden Tool ist, das (@Luschi) u.a. mit einigen Anregungen aus dem Internet entstanden ist.

Vielen Dank
Maurice

 

...hier mal ein Beispieöl zur Datenübermahme mittels Ado-Ich empfehle, den Code in ein separates Modul zu schreiben.
Anpassungen bitte selbst vornehmen. ich verspreche, ich kümmere mich nicht darum, denn es ist schon ein ordentlicher Brocken....*wink.gif*
Code:

Aufgerufen werden muss die Prozedur Hole_Daten_via_ADO. Die Parameter sind selbsterklärend.

 

\@Maurice: Interessante Beurteilung der Antworten. Luschis Beitrag enthält den Hinweis auf Deine Fehlerzeile. Es ist aber manchmal nicht so einfach, zwei unterschiedliche Gedanken parallel zu erfassen ...

@Ralf: Als Datenbänkler bin ich auch schnell bei SQL, ADODB und DAO. Aber: SQL braucht eine Liste zum Greifen, die Vorgabe Bereich ist da viel unspezifischer und somit ohne weitere Kenntnis schon mal fehleranfällig.
Und wie schon gesagt: Das Öffnen einer ADODB-Connection ist so eine Art des Öffnens. Wenn man es nicht so sieht, hat das gar nichts zu sagen: Die Sonne ist auch nachts da.

 

\@ Ralf (R J)
... eigentlich sollte der TE mir seine Motivation darlegen. Die Nachfragen nach "Auslesen aus geschlossenen Dateien" nimmt gefühlt inflationär zu und ich habe oft den Eindruck, dass der/die TE gar nicht begründen kann, warum.

Ich weiß es, dass es eine Frage der Geschwindigkeit ist. Und ich weiß auch, dass es noch andere Gründe geben kann. Ich selbst gehe da seit einiger Zeit den "Goldenen Mittelweg" und verwende ein leicht SQL-angelehntes Tool (genauer gesagt: Power Query), weil da die Endanwender eher die Möglichkeit haben, Änderungen vorzunehmen. Und dazu kommt meine Erfahrung mit MS in Sachen DB-Verbindung, wo immer wieder einmal das Hohelied auf DDE, DAO, ADO, ... gesungen wurde und klammheimlich der Vorgänger in die Ecke zu den Bösen Buben geschickt wurde. Hier habe ich die Hoffnung, dass das Ganze etwas zeitstabiler ist. - Aber bitte, ich will jetzt keine Diskussion in der Sache vom Zaune brechen, wenn mir ein Weg vertraut ist und auch funktioniert werde ich ihn auch weiter beschreiten, egal was mir sonst noch angeboten wird (wenn es nicht enorme Vorteile bietet).

 

\@Helfergemeinschaft
Performancegründe können bei diesem Code wohl nicht im Vordergrund stehen:
Code:

Daher dürfte es wohl auch zulässig sein, die Mappe zeitweise zu öffnen, die Schleife die Zellen abklappern zu lassen und die Mappe wieder zu schließen.

Abgesehen davon wäre es auch möglich, die Auswertung per Formel ohne "Öffnen" der Mappe auszuführen:
Code:

Aber irgendwie habe ich den Eindruck, dass das den TE ohnehin alles nicht interessiert.

 

Diese pauschale Aussage ist so unrichtig. Damit sich hier keine falschen Legenden bilden, betrachten wir uns einige Punkte.

Eine geschlossene Mappe könnte implizieren, dass es da nicht mal eine Excelinstanz gibt. Die Objekte von Excel, Word, Access sind ordentlich groß. Wenn man diese (jedes für sich) erst per Late Binding instanzieren muss, braucht das schon ein wenig Zeit. Startet man diese Objekte per Early Binding, also unter Nutzung eines Verweises, dann werden sie schon bei Start der eigenen Anwendung geladen. Damit dauert der Start etwas länger, und im laufenden finden dann nur ggf. Speicherumschichtungen statt (Arbeitsspeicher Auslagerungsdatei).
Ein Zugriff per ADODB bzw. DAO ist da schneller. Hier wird aber nur ein Zugriff auf Daten / Tabellen organisiert. Die ganze Welt der Oberfläche und Verarbeitung (Formulare, Formate, Formeln, bedingte Formatierungen) bleibt da unberücksichtigt.
Wenn man nun aber in Excel bereits arbeitet, hat man schon eine Excelinstanz. Die kann man unmittelbar für weitere Arbeitsmappen nutzen.

Als zweiter Punkt: Startet man eine Datei über deren Application-Objekt, wird sie mehr ober weniger vollständig geladen. Da ist es leicht nachvollziehbar, dass es einen Unterschied macht, ob eine Arbeitsmappe nur ein einziges Arbeitsblatt mit einer Liste (also pure Daten) enthält oder eine Vielzahl von Arbeitsblättern mit allerlei Inhalt und Verknüpfungen und automatischen Selbstberechnungen und -überprüfungen. Ein ADODB- bzw. DAO-Zugriff beschränkt sich wie gesagt von Haus aus auf Datenzugriff.

Weiter hat man dann die Thematik des eigentlichen Datenzugriffes: Es macht einen Unterschied, ob man ganze Bereiche übernimmt und verarbeitet (Bereich=>Array, Array=> Bereich) oder man Zellinhalte einzeln abfragt, verarbeitet und irgendwohin zurückschreibt. Massendatenverarbeitung schlägt performancetechnisch i.d.R. eine serielle Einzelverarbeitung.

Um also "um Längen schneller" zu sein, müssen einige Punkte zusammenkommen. Das Kriterium "geschlossene Datei" ist da als laufzeitverlängernd einzuordnen. Durch eine geschlossene Tür geht man nicht schneller als durch eine bereits offene. Man muss sie erst öffnen, ehe man durchgehen kann.

Ein weiterer Aspekt wäre dann die eigentliche Datenverarbeitung.
Code:

Hier wird undifferenziert der gesamte Inhalt einer Tabelle einfach nur geholt (entspricht einem Copy). Wenn man sich vor Augen hält, dass SQL dazu entwickelt wurde, gerade große Datenmengen performant zu verarbeiten und dazu entsprechende Methoden der Massendatenverarbeitung bereitstellt, so wird mit dem puren Datenholen dieser Teil nicht genutzt und ggf. benötigte Performance verschenkt.

Das ist dann eine weitere Möglichkeit.

Ingesamt aber sollte man erst prüfen, ob die externe Mappe unter dem verwendeten Pfad existiert, ehe man einen Zugriff darauf startet. Im Eingangscode wird es umgedreht umgesetzt.

 

Gut, dann gebe ich jetzt auch noch meinen Senf dazu.

Bezüglich der Maßregelung: Mag nicht erfreulich sein, ist aber richtig. Vor allem aus Sicht der Helfer, die den ganzen Ablauf zum siebenundneunzigsten mal durchmachen.

Aber zum eigentlichen Punkt:

Auch hier finde ich den Einwand richtig, dass es vermutlich nicht notwendig ist, das Ganze mit geschlossener Quelldatei zu praktizieren. Denn wenn ich 2 (in Worten zwei) volle Tage in etwas investiere, was mir eigentlich keinen (nennenswerten) Vorteil bringt, dann passt das so nicht. Mit der "Standardlösung" über Datei öffnen wärst du in einer Stunde durchgewesen.
Aber vielleicht gibt es ja wirklich einen gewichtigen Grund, dass die Quelle geschlossen bleiben muss. Dann nenne diesen bitte.

Sieh auch bitte meine Aussage nicht als Kritik, sondern als Anregung andere Wege zu gehen, als den, den man fix vor Augen hat.

 

\@Helferrunde
Immer wieder eine Freude, so ein angeregtes Fachgesimpel mit Euch.

Da kann man ja schon fast dankbar sein, dass es der Fragende vorzieht, Rückfragen zu ignorieren und stattdessen den Helfern vors Bein zu treten - sonst wäre die Lösung vermutlich schon längst fertig und das Thema zu.

nicht nur das. Der Code strotzt nur so vor logischen, syntaktischen und generellen Fehlern. Anpassung und Hilfe unmöglich, siehe vor.

/signed

 

Hallo zusammen,

ihr habt alle recht und seid auch sehr kompetent in dem was ihr schreibt. Ich möchte nur darauf hinweisen (und nicht irgenwen ans Bein pinkeln), dass es für Fragende nicht leicht ist zu fragen, wenn man mit solchen Kommentaren (absichtlich oder unabsichtlich) nieder gemacht wird.

Ich habe die Kommentare jetzt alle gesichtet und versuche diese so gut wie möglich im Code einzubringen.

Meine Frage nach der geschlossenen Datei entstand bei der Absicht mehrere Personen gleichzeitig auf die Datequelle zugreifen zu lassen und somit eine Fehlermeldung zu vermeiden.

Aber ich habe jetzt einen Weg gefunden, diese Meldung zu verhindern und die Dateien auch gleichzeitig von n Usern abgreifen zu lassen.

Vielen Dank für die gut gemeinte Unterstützung von der Helferrunde (ernst gemeint)
Maurice

 

Zum gedanklichen Abschluss:

Das EIGENTLICHE Ziel einer Maßnahme darf man ruhig gleich eingangs mitteilen.

Wenn jeder an sich denkt, ist an jeden gedacht. (Ralf Nölleke).
Nicht dass die Helferrunde dringend einer näheren Erläuterung bedarf, da hat jeder so seine Erfahrungen und Wege. Aber es könnte ja sein, dass ein weiterer Antwortsuchender sich die ähnliche Frage stellt und dann nur "vor Begeisterung" in die Hände klatschen kann ob der umfassenden Erläuterung der gefundenen Lösung.