Jan 28, 2010

40582

Zellwert aus 100 Tabellenblättern auslesen

Hallo zusammen,

leider habe ich nichts auf die Schnelle gefunden, ich hätte ein kleines Problem (hoffe ich).

Ich muss aus einer Datei, die über 100 Excelblätter enthält aus jedem Tabellenblatt den Wert der Zelle „B 35“ auslesen und in eine Gesamtübersicht untereinander eintragen. Die Reihenfolge ist völlig egal, die Werte sollten einfach untereinander stehen.

Kann mir jemand behilflich sein !?

Vielen Dank im Voraus !!

Tom

Please also mark the comments that contributed to the solution of the article

Content-Key: 134603

Url: https://administrator.de/contentid/134603

Printed on: April 25, 2024 at 22:04 o'clock

19 Comments

Latest comment

Hallo bender70!

Sorry, habe mich mal wieder verlesen und meinen Kommentar wieder entfernt

Gruß Dieter

Hallo bender70!

Soferne diese Tabellen fortlaufend benannte Namen haben (was Du aus welchen Gründen auch immer geheim gehalten hast

), sollte ein Eintrag und eine Formel genügen:

in A2: Tabelle1
in B2: =INDIREKT(A2 & "!B35")

Diese beiden Zellen kannst Du markieren und nach unten ausfüllen ...

Grüße
bastla

Hallo Tom

Ich vermute, dass du die Zellinhalte der Zellen B35 aller Blätter addieren willst, weil du sie untereinander in beliebiger Reihenfolge haben möchtest.

Dafür gibt es eine bessere Möglichkeit: Sogenannte 3D-Bezüge. Damit kannst du gezielt immer die gleiche Zelle in einer Reihe von Arbeitsblättern ansprechen.

Für die Summe aller Zellen B35 in den Blättern Tabelle1 bis Tabelle100 müsstest du schreiben:

=summe(tabelle1:tabelle100!B35)

Noch ein Hinweis: Eine Arbeitsmappe mit über 100 Blättern, auf die alle gleichzeitig zugegriffen werden muss, riecht mir ganz nach einer relationalen Datenbank, die sich mit Access wesentlich besser handeln lässt. Überlege mal, ob ein Umstieg sinnvoll wäre.

Gruß Hans

Erstmal vielen Dank für die vielen und schnellen Antworten !

ich habe Euch leider einige wichtige Infos unterschlagen:

Es ist nicht eine Excel-Datei mit 100 Sheets, sondern es sind 100 verschiedene Exceltabellen mit verschiedensten Namen wie z.B. "Schraube 2,5x25" "Schraube gelackt"...

Es geht nur um die Kostenstelle in der Zelle B35, d.h. ich muss die auch nicht alle addiern, ich will damit nur überprüfen ob die da auch steht... 100 Stck könnte ich natürlich alle mal anklicken, aber es gibt weitere Dateien... also insgesamt 2.500 Tabellen...

Viele Grüsse
Tom

Oh weh, Tom,

es ist schade um jede Minute Arbeitszeit, die du in dieses verkorkste System investierst. Steige um auf eine richtigige Datenbank. 2500 Tabellen! Wahnsinn! Für eine echte Datenbank ist das aber überhaupt kein Problem und du hast dann alles in einer einzigen Datei.

Wenn du HIlfe zum Umstieg auf eine echte Datenbak brauchst, kannst du dich an mich wenden.

Gruß
Hans

ungetestet und in VBS eventuell so:

Dim Pfadangabe, Ordner, objxls
Dim Variable(100) , Zaehler

Zaehler = 0

Ordner = InputBox("Bitte Pfad eingeben ","...","C:\")  
If Not Right(Ordner,1) = "\" then Ordner = Ordner & "\"  

set fs = createobject("Scripting.FileSystemObject")  

Set objXLS = WScript.CreateObject("Excel.Application")  

Listordner Ordner

Sub ListOrdner(ordner)
	'On Error resume next  
	
	Set ordner = fs.getfolder(ordner)

	For Each file In ordner.files
		Pfadangabe =file.path
		objXLS.Workbooks.Open Pfadangabe
		objXLS.Visible = False

		Variable(Zaehler) = OBJXLS.Range("B35").Text  


		objXLS.Quit
		
		Zaehler = Zaehler + 1
	Next

End Sub

Set objXLS = WScript.CreateObject("Excel.Application")  
objXLs.Workbooks.Add
objXLS.Visible = True

for i = 0 to Ubound(Variable)
If not Variable(i) = "" then  
	objXLs.Cells(i + 1, 1).Value = Variable(i)
end if
next


set fs = nothing
Set objXLS = nothing

das sollte erst einmal bis 100 Exceldateien funktionieren. Teste mal

Gruss
Tsuki

@ Hans: dieses System hat mal ein Kollege "konstruiert", ich würde es gerne umstellen, aber leider fehlt die Zeit und die Einsicht von Chefe ...

@TsukiSan: Leider will es nicht laufen

ich habe aber etwas gefunden was man vielleicht umschreiben könnte:

Das Programm löscht immer die erste Zelle aus jeder Tabelle, die man vorher ausgewählt hat, vielleicht kann man das so umschreiben, dass das Programm statt löschen einfach die Zelle kopiert und dann in einen Gesamttabelle untereinander schreibt !?

Public Sub Dateien_oeffnen()
'Code für ein allgemeines Modul
On Error GoTo errExit
Dim WBQ As Workbook
Dim WBZ As Workbook
Dim vntDateien As Variant
Dim lngAnzahl As Long

Set WBZ = ActiveWorkbook

With Application
.DisplayAlerts = False
.ScreenUpdating = False
End With

' Verzeichnis und Dateien auswählen
vntDateien = _
Application.GetOpenFilename("Datei (*.xls),*.xls", False, False, False, True)
' Dateien öffnen, A1 der Tabelle1 löschen, speichern und schließen
For lngAnzahl = LBound(vntDateien) To UBound(vntDateien)
Workbooks.OpenText Filename:=vntDateien(lngAnzahl)
Set WBQ = ActiveWorkbook
WBQ.Worksheets("Tabelle1").Range("A1").Clear '< Zelle A1 der Datei löschen
WBQ.Save
WBQ.Close
Next

With Application
.DisplayAlerts = True
.ScreenUpdating = True
End With

MsgBox "Es wurden " & lngAnzahl - 1 & " Dateien geöffnet und bearbeitet.", 64
Application.DisplayAlerts = True
Exit Sub

'Fehlerbehandlung
errExit:
With Application
.DisplayAlerts = True
.ScreenUpdating = True
End With

If Err.Number = 13 Then
MsgBox "Es wurden keine Datei ausgewählt", 64
Else
MsgBox "Es ist ein Fehler aufgetreten." & vbCr _
& Err.Number & vbCr _
& Err.Description, 48
End If
End Sub

Viele Grüsse
Tom

Hallo @all!

Wenn schon von der bestehenden Struktur ausgegangen werden soll, könnten es zumindest dynamische Bezüge auf die einzelnen Dateien sein - das ließe sich aus der der "Sammel"-Datei etwa mit folgendem VBA-Script umsetzen:

Sub ErstelleBezuege()
'"Sammel"-Tabelle  
Spalte = "A"  
StartZeile = 3

'"Daten"-Tabellen  
Ordner = "D:\XL-Dateien" 'Ordner mit den einzelnen Dateien  
Tabelle = "Tabelle1"     'Tabelle, in welcher die Daten in B35 stehen  
Bezug = "B35"            'Zelle mit den gesuchten Daten  

Zeile = StartZeile
Set fso = CreateObject("Scripting.FileSystemObject")  
For Each File In fso.GetFolder(Ordner).Files
    If LCase(fso.GetExtensionName(File.Name)) = "xls"  
        Cells(Zeile, Spalte).Formula = "='" & File.ParentFolder & "\[" & File.Name & "]" & Tabelle & "'!" & Bezug  
        Zeile = Zeile + 1
    End If
Next
End Sub

Das Script sollte Formeln der Art

='D:\XL-Dateien\[Schraube gelackt.xls]Tabelle1'!B35

für alle im angegebenen Ordner enthaltenen Dateien erzeugen.

Grüße
bastla

Sorry@all,

es war mir nicht bewußt, dass es in VBA sein muss.
Was ist denn die Fehlermeldung@bender70? (Ist nur für mich, damit ich meine Fehler beseitigen kann.)
Was läuft denn nicht?

Gruss
Tsuki

@tsuki

es war mir nicht bewußt, dass es in VBA sein muss.

Muss es wohl nicht - bietet sich aber an ...

Grüße
bastla

Achso! Na dann!

Trotzdem interessiert mich, was bei meinem VBS-Script nicht läuft.
Hatte heute mal Zeit und es getestet. Es hat genau getan, was es sollte. Mh

(Win XP Prof SP2, Office2000 Prof)

Naja, wohl nicht so wichtig.

Viele Grüße

Tsuki

@tsuki

Trotzdem interessiert mich, was bei meinem VBS-Script nicht läuft.

Nachvollziehbar; auch ich habe die Angabe der konkreten Fehlermeldung vermisst ...

Für mich sieht Dein Script - allerdings ohne es getestet zu haben - OK aus.

Was ich allerdings anders gemacht hätte, ist das Eintragen in die Tabelle - wenn Du nämlich die Zeilennummer der Zelle von der Schleifenvariablen i abhängig machst ("objXLs.Cells(i + 1, 1).Value = Variable(i)", ist eigentlich das vorhergehende "If" unnötig, da es egal sein sollte, ob die Zielzelle übersprungen oder mit dem Inhalt "" gefüllt wird ...

In solchen Fällen verwende ich einen eigenen Zähler (meist die Variable "Zeile") für die Zeillenummer ...

Grüße
bastla

Hallo bastla,

danke dir für deine Ausführung!
Das ist richtig, was du schreibst!
Ich hatte es nur auf die schnelle zusammen "gekrickselt". Dieses

i+1

ist sicher nie elegant, weil ja bei Excel es mit

anfängt.

Nuja, mein script sollte zumindest ausbaufähig sein. Man könnte die "Leerstellen" oder was auch immer in den Exceldateien abfragen, sie markiert in das neue Formular eintragen. Alles kein Problem. Aber wie du auch schon richtig bemerkt hast hat der TO die eigentliche Fehlermeldung nicht niedergeschrieben. Von daher.....
Mein Script war nur so eine Idee.

Viele Grüße
Tsuki

<Edit>
Achso, eins noch, das IF bla bla bla in der Schleife zum eintragen in die neue Tabelle bezog sich auf das "Ubound" der Variable. Falls weniger wie 100 (oder in meinem Fall 101) Excel-Dateien im ausgewählten Ordner stehen, soll keine "Pseudonull" eingetragen werden.
Naja, ganz 100pro ist mein Script nicht, aber es zeigt die "Angelroute" (wie biber es beschreibt)
</Edit>

@tsuki

as IF bla bla bla in der Schleife zum eintragen in die neue Tabelle bezog sich auf das "Ubound" der Variable. Falls weniger wie 100 (oder in meinem Fall 101) Excel-Dateien im ausgewählten Ordner stehen, soll keine "Pseudonull" eingetragen werden.

Sinnvolle Überlegung ...

... allerdings ließe sich das Problem der (vorweg) unbekannten Arraygröße relativ elegant lösen: Anstatt den ausgelesenen Wert in das Array einzutragen, könntest Du ihn zunächst einem String hinzufügen, also etwa

Daten = Daten & ";" & OBJXLS.Range("B35").Text

Die Umwandlung in ein Array geht dann per "Split":

Variable = Split(Mid(Daten, 2), ";")

Das Trennzeichen (im Beispiel ";") darf natürlich in den eigentlichen Daten nicht vorkommen und muss daher ev mittels eines anderen Zeichens realisiert werden.

Grüße
bastla

@bastla
das einfachste liegt immer so fern

Du hast recht, bezüglich der grenzenlosen Arrays!

Man kann natürlich auch die Variable definieren indem man schreibt

Dim blabla()

Dann ist es ja auch "offen" und später mit "Ubound" kann man die "Größe" ermitteln, richtig?

Ich danke dir für deine Ausführungen!

Viele Grüße

Tsuki

@tsuki

Dann ist es ja auch "offen" und später mit "Ubound" kann man die "Größe" ermitteln, richtig?

Braucht dann allerdings ein "ReDim Preserve" ...

Grüße
bastla

Braucht dann allerdings ein "ReDim Preserve" ...

Richtig! Das war es!

Danke dir!

Tsuki

Oh, sorry, es funktioniert, mein Fehler !!

Vielen Dank !!!

Grüsse
TOM

wenigstens hat er

48075

vor seinem Austritt den Beitrag noch auf gelöst gesetzt.

Danke!

German solved Question Microsoft Office Microsoft

Hotly discussed

Check of ZFW Firewallgleixnerd - 5 Comments

Zellwert aus 100 Tabellenblättern auslesen

Hallo zusammen,leider habe ich nichts auf die Schnelle gefunden, ich hätte ein kleines Problem (hoffe ich).

Hallo zusammen,

leider habe ich nichts auf die Schnelle gefunden, ich hätte ein kleines Problem (hoffe ich).