Excel Dateien durchsuchen und Werte in neue Excel Datei auslesen
Hallo und Danke für Eure stets guten Antworten und Hilfen!
Folgendes Problem habe ich:
WinXP - Office2003
Ich habe etliche Excel Dateien mit je einem Tabellenblatt - in diesen Dateien stehen ähnliche Daten (Name, Vorname, etc.) leider aber nicht in in gleichen Felden (mal ist Name auf A1 der "Wert" dazu auf B1 - mal auf A2 und B2). Ich möchte nun alle Dateien durchsuchen und die "Werte" in eine neue Excel Datei auslesen lassen.
Ich denke ich komme nicht an Makros vorbei - die sind aber leider garnicht meine Welt - vielleicht gibts ja auch ne Möglichkeit das ganze als Shell-Script zu machen.
Vielleich Dank für Eure ANtworten
Gruß MArkus
Folgendes Problem habe ich:
WinXP - Office2003
Ich habe etliche Excel Dateien mit je einem Tabellenblatt - in diesen Dateien stehen ähnliche Daten (Name, Vorname, etc.) leider aber nicht in in gleichen Felden (mal ist Name auf A1 der "Wert" dazu auf B1 - mal auf A2 und B2). Ich möchte nun alle Dateien durchsuchen und die "Werte" in eine neue Excel Datei auslesen lassen.
Ich denke ich komme nicht an Makros vorbei - die sind aber leider garnicht meine Welt - vielleicht gibts ja auch ne Möglichkeit das ganze als Shell-Script zu machen.
Vielleich Dank für Eure ANtworten
Gruß MArkus
Bitte markiere auch die Kommentare, die zur Lösung des Beitrags beigetragen haben
Content-ID: 43626
Url: https://administrator.de/forum/excel-dateien-durchsuchen-und-werte-in-neue-excel-datei-auslesen-43626.html
Ausgedruckt am: 23.12.2024 um 11:12 Uhr
33 Kommentare
Neuester Kommentar
Hallo Markus!
Um das Ganze automatisieren zu können, wären zusätzliche Informationen ganz gut:
Gibt es eine Systematik bei den Dateinamen?
Falls nein: Wie viele sind "etliche"? (Hintergrund: Ist es praktikabel, alle Dateien gleichzeitig zu öffnen?)
Habe ich richtig verstanden, dass jeweils der Inhalt einer Zelle ("Wert"), die rechts neben einer Zelle mit gleich bleibendem Inhalt (also "Name" ist in allen Tabellen vollkommen identisch) steht, übernommen werden soll?
Kommt der "Name" in jeder Mappe bzw Tabelle nur einmal bzw nur in Spalte A vor?
Die übernommenen "Werte" sollen vermutlich in der Zieltabelle in einer Spalte untereinander gespeichert werden?
Grüße
bastla
Um das Ganze automatisieren zu können, wären zusätzliche Informationen ganz gut:
Gibt es eine Systematik bei den Dateinamen?
Falls nein: Wie viele sind "etliche"? (Hintergrund: Ist es praktikabel, alle Dateien gleichzeitig zu öffnen?)
Habe ich richtig verstanden, dass jeweils der Inhalt einer Zelle ("Wert"), die rechts neben einer Zelle mit gleich bleibendem Inhalt (also "Name" ist in allen Tabellen vollkommen identisch) steht, übernommen werden soll?
Kommt der "Name" in jeder Mappe bzw Tabelle nur einmal bzw nur in Spalte A vor?
Die übernommenen "Werte" sollen vermutlich in der Zieltabelle in einer Spalte untereinander gespeichert werden?
Grüße
bastla
Hallo Markus!
Alle Dateien in einem Ordner genügt.
Erstelle die Zieldatei (in meinem Beispiel mit dem Namen "Alle.xls") in einem anderen Ordner und füge folgendes Makro hinzu:
Anzupassen sind die Daten im ersten Absatz sowie weiter unten der vorgegebene Suchbereich Range("a1:a100").
Nach Durchlauf des Makros (vielleicht nur einmal mit 3 Dateien testen ) enthält die Zieldatei die entsprechenden Einträge und kann (in beliebigem Format, auch csv) gespeichert werden (wird nicht durch das Makro erledigt).
HTH
bastla
Alle Dateien in einem Ordner genügt.
Erstelle die Zieldatei (in meinem Beispiel mit dem Namen "Alle.xls") in einem anderen Ordner und füge folgendes Makro hinzu:
Option Explicit
Sub GetData()
Dim oMe As Object
Set oMe = Workbooks("Alle.xls").Worksheets("Tabelle1") 'ZielDatei/-Tabelle (also die gerade geöffnete) ;-)
Const sDateiPfad As String = "D:\Test\" 'Pfad für zu durchsuchende Excel-Dateien; mit Backslash am Ende
Const iSbAnzahl = 3 'Nach 3 Begriffen suchen
Dim sSuchbegriff(iSbAnzahl) As String
sSuchbegriff(1) = "Name"
sSuchbegriff(2) = "Vorname"
sSuchbegriff(3) = "Strasse"
Dim i As Integer
Dim sWbName As String
Dim rFound As Range
Dim vWert As Variant
Dim iZeile As Integer
iZeile = 2
Dim oFS As Object, oDatei As Object
Set oFS = CreateObject("Scripting.FileSystemObject")
For Each oDatei In oFS.GetFolder(sDateiPfad).Files
sWbName = oDatei.Name
Workbooks.Open (sDateiPfad & sWbName)
For i = 1 To iSbAnzahl
Set rFound = Workbooks(sWbName).Worksheets(1).Range("a1:a100").Find(sSuchbegriff(i), LookIn:=xlValues)
If Not rFound Is Nothing Then
vWert = Cells(rFound.Row, rFound.Column + 1).Value
oMe.Cells(iZeile, i).Value = vWert
End If
Next
Workbooks(sWbName).Saved = True
Workbooks(sWbName).Close
iZeile = iZeile + 1
Next
End Sub
Anzupassen sind die Daten im ersten Absatz sowie weiter unten der vorgegebene Suchbereich Range("a1:a100").
Nach Durchlauf des Makros (vielleicht nur einmal mit 3 Dateien testen ) enthält die Zieldatei die entsprechenden Einträge und kann (in beliebigem Format, auch csv) gespeichert werden (wird nicht durch das Makro erledigt).
HTH
bastla
Hey Ihr beiden!
Habe ein so ähnliches Problem, jedoch mit etwas geänderten Variablen. Würde jedoch gerne diese VBA-Grundstruktur verwenden, jedoch funktioniert dies nicht so bei mir wie erwartet.
Könntet Ihr deshalb das Excel-Sheet uploaden oder so, dass ich mir ein bild davon machen kann wie es geht?
ware nett
thx, im voraus
rene
Habe ein so ähnliches Problem, jedoch mit etwas geänderten Variablen. Würde jedoch gerne diese VBA-Grundstruktur verwenden, jedoch funktioniert dies nicht so bei mir wie erwartet.
Könntet Ihr deshalb das Excel-Sheet uploaden oder so, dass ich mir ein bild davon machen kann wie es geht?
ware nett
thx, im voraus
rene
Hallo Sleepy00 und willkommen im Forum!
"Das Excel-Sheet" gibt es eigentlich nicht, sondern es ist, wie oben beschrieben, zu erstellen.
Wenn Du Dein Vorhaben etwas näher erklärst, sollten wir hoffentlich auch unter geänderten Voraussetzungen etwas Brauchbares basteln können ...
Grüße
bastla
"Das Excel-Sheet" gibt es eigentlich nicht, sondern es ist, wie oben beschrieben, zu erstellen.
Wenn Du Dein Vorhaben etwas näher erklärst, sollten wir hoffentlich auch unter geänderten Voraussetzungen etwas Brauchbares basteln können ...
Grüße
bastla
Hallo,
ich nehme das Thema und das oben geschriebene Makro nochmal auf, da es im Grunde meine Bedürfnisse beinahe trifft und toll funktioniert.
Möchte aus mehreren Excel-Dateien Werte in einer Datei zusammenfassen (wie Arafat). Die Variation besteht aber darin, dass die einzelnen Excel-Dateien eine variierende Anzahl von Tabellenblättern beinhalten, und von diesen sollen auch nur bestimmte Tabellenblätter bei der Zusammenfassung berücksichtigt werden.
Bsp.: In dem Ordner D:\Test\ liegen 2 Excel-Dateien.
Die erste hat 21 Tabellenblätter, wovon ein Tabellenblatt eine Übersicht ist ('Übersicht'), 20 Tabellenblätter sind Testauswertungen ('Test 1' bis 'Test 20').
Die zweite hat 16 Tabellenblätter, auch hier eine Übersicht und 15 Testauswertungen ('Test 1' bis 'Test 15').
Ziel des Makros ist es wie bei Arafat, bestimmte Werte aus den Dateien in einer neuen Liste zusammenzuführen. Hierbei sollen aber nur Werte von Tabellenblättern berücksichtigt werden, deren Bezeichnung mit 'Test' beginnen.
Könnt Ihr mir bei der Anpassung des Makros helfen??
ich nehme das Thema und das oben geschriebene Makro nochmal auf, da es im Grunde meine Bedürfnisse beinahe trifft und toll funktioniert.
Möchte aus mehreren Excel-Dateien Werte in einer Datei zusammenfassen (wie Arafat). Die Variation besteht aber darin, dass die einzelnen Excel-Dateien eine variierende Anzahl von Tabellenblättern beinhalten, und von diesen sollen auch nur bestimmte Tabellenblätter bei der Zusammenfassung berücksichtigt werden.
Bsp.: In dem Ordner D:\Test\ liegen 2 Excel-Dateien.
Die erste hat 21 Tabellenblätter, wovon ein Tabellenblatt eine Übersicht ist ('Übersicht'), 20 Tabellenblätter sind Testauswertungen ('Test 1' bis 'Test 20').
Die zweite hat 16 Tabellenblätter, auch hier eine Übersicht und 15 Testauswertungen ('Test 1' bis 'Test 15').
Ziel des Makros ist es wie bei Arafat, bestimmte Werte aus den Dateien in einer neuen Liste zusammenzuführen. Hierbei sollen aber nur Werte von Tabellenblättern berücksichtigt werden, deren Bezeichnung mit 'Test' beginnen.
Könnt Ihr mir bei der Anpassung des Makros helfen??
Hallo BTS-18203 und willkommen im Forum!
Mit kleineren Änderungen (zB muss die Sammeldatei nicht mehr "Alle.xls" heißen, sollte sich aber dennoch nicht im selben Ordner wie die zu durchsuchenden Dateien befinden) und der gewünschten Ergänzung (Berücksichtigung aller passenden Blätter der einzelnen Tabellen) sollte es so gehen:
Grüße
bastla
Mit kleineren Änderungen (zB muss die Sammeldatei nicht mehr "Alle.xls" heißen, sollte sich aber dennoch nicht im selben Ordner wie die zu durchsuchenden Dateien befinden) und der gewünschten Ergänzung (Berücksichtigung aller passenden Blätter der einzelnen Tabellen) sollte es so gehen:
Option Explicit
Sub GetData()
Dim oMe As Object, sSuchbegriff(), sBereich As String, iZeile As Integer, sKennz As String
Dim iSbMax As Integer, iLK As Integer, i As Integer, sWbName As String, rFound As Range, vWert As Variant
Dim oFS As Object, oDatei As Object, wsTabelle As Worksheet, bEintrag As Boolean
Set oMe = ThisWorkbook.Worksheets("Tabelle1") 'Zieltabelle (in der gerade geöffneten Datei)
iZeile = 2 'ab dieser Zeile Ergebnisse in die Zieltabelle eintragen
Const sDateiPfad As String = "D:\Test\" 'Pfad für zu durchsuchende Excel-Dateien; mit Backslash am Ende
sKennz = "Test" 'Nur Tabellen, deren Name mit dem Kennzeichen beginnt, verarbeiten
sSuchbegriff = Array("Name", "Vorname", "Strasse") 'Liste der Suchbegriffe
sBereich = "A1:A100"
iSbMax = UBound(sSuchbegriff) 'Höchster Index der Suchbegriffmatrix
iLK = Len(sKennz) 'Länge des Tabellennamen-Kennzeichens
Set oFS = CreateObject("Scripting.FileSystemObject")
For Each oDatei In oFS.GetFolder(sDateiPfad).Files
sWbName = oDatei.Name
Workbooks.Open (oDatei.Path)
For Each wsTabelle In Workbooks(sWbName).Worksheets()
If StrComp(Left(wsTabelle.Name, iLK), sKennz, vbTextCompare) = 0 Then
bEintrag = False
For i = 0 To iSbMax
Set rFound = wsTabelle.Range(sBereich).Find(sSuchbegriff(i), LookIn:=xlValues)
If Not rFound Is Nothing Then
vWert = wsTabelle.Cells(rFound.Row, rFound.Column + 1).Value
oMe.Cells(iZeile, i + 1).Value = vWert
bEintrag = True
End If
Next
If bEintrag Then iZeile = iZeile + 1 'mindestens ein Eintrag erfolgt, daher neue Zeile
End If
Next
Workbooks(sWbName).Saved = True
Workbooks(sWbName).Close
Next
End Sub
Grüße
bastla
Ich würde das Makros gerne auch für eine andere Sache verwenden, bekomme die Abwandlung jedoch nicht hin.
Ist-Zustand: Ich erhalte täglich 12-20 Excel-Listen mit Leistungswerten, die ich gerne in einer Datei zusammenfassen würde und ggf. in eine Access Datei exportieren würde. In den Dateien sind die Leistungen von einzelnen Mitarbeitern in Verschiedenen Themen (Vertriebskanäle) aufgeführt. Im Grunde ist der Aufbau der Datein identisch, es werden jedoch von den 8 möglichen Vertriebskanäle nur die aufgeführt, in denen die Mitarbeiter auch gearbeitet haben. D.h. die Werte eines Vertriebskanals sind nicht immer in der gleichen Spalte aufgeführt.
Beispiele:
[Dateiname: Datei 1]
[Dateiname: Datei 2]
Ziel ist es, aus den einzelnen Dateien, die täglich in einem definierten Laufwerksordner abgelegt werden, eine Datei zu machen, mit der dann weiter gearbeitet werden kann.
Ziel Beispiel:
Könnt Ihr mir bei der Abwandlung des o.g. Makros helfen?
Beste Grüße
BTS-18203
Ist-Zustand: Ich erhalte täglich 12-20 Excel-Listen mit Leistungswerten, die ich gerne in einer Datei zusammenfassen würde und ggf. in eine Access Datei exportieren würde. In den Dateien sind die Leistungen von einzelnen Mitarbeitern in Verschiedenen Themen (Vertriebskanäle) aufgeführt. Im Grunde ist der Aufbau der Datein identisch, es werden jedoch von den 8 möglichen Vertriebskanäle nur die aufgeführt, in denen die Mitarbeiter auch gearbeitet haben. D.h. die Werte eines Vertriebskanals sind nicht immer in der gleichen Spalte aufgeführt.
Beispiele:
[Dateiname: Datei 1]
Namen | Vertriebskanal1 | Vertriebskanal2 | Vertriebskanal3 | |||
ADAM | 15 | 10 | 20 | |||
BIRGIT | 10 | 0 | 15 |
[Dateiname: Datei 2]
Namen | Vertriebskanal1 | Vertriebskanal3 | ||
CÄSAR | 5 | 8 | ||
DETLEF | 20 | 0 |
Ziel ist es, aus den einzelnen Dateien, die täglich in einem definierten Laufwerksordner abgelegt werden, eine Datei zu machen, mit der dann weiter gearbeitet werden kann.
Ziel Beispiel:
Dateiname | Name | Vertriebskanal | Leistung | |||
Datei 1 | ADAM | Vertriebskanal 1 | 15 | |||
Datei 1 | ADAM | Vertriebskanal 2 | 10 | |||
Datei 1 | ADAM | Vertriebskanal 3 | 20 | |||
Datei 1 | BIRGIT | Vertriebskanal 1 | 10 | |||
Datei 1 | BIRGIT | Vertriebskanal 3 | 15 | |||
Datei 2 | CÄSAR | Vertriebskanal 1 | 5 | |||
Datei 2 | CÄSAR | Vertriebskanal 3 | 8 | |||
Datei 2 | DETLEF | Vertriebskanal 1 | 20 |
Könnt Ihr mir bei der Abwandlung des o.g. Makros helfen?
Beste Grüße
BTS-18203
Hallo BTS-18203!
Soferne es zwischen den Daten keine Leerzeilen bzw -spalten gibt, sollte es so gehen:
Auch in diesem Fall sollte sich die Zieldatei nicht im selben Ordner wie die Quelldateien befinden.
Die Zieltabelle wird zu Beginn vollständig gelöscht und abschließend nach Datei und Name sortiert.
Grüße
bastla
Soferne es zwischen den Daten keine Leerzeilen bzw -spalten gibt, sollte es so gehen:
Option Explicit
Sub GetData()
Dim oMe As Object, sUeber() As String, sVK()
Dim iZSpalte As Integer, iZZeile As Integer, iZAbZeile As Integer, iZAbSpalte As Integer, iZSpAnz As Integer
Dim iQZeile As Integer, iQSpalte As Integer, iQAbZeile As Integer, iQAbSpalte As Integer, iQSpAnz As Integer
Dim sWbName As String, sName As String, sV As Variant, iLeist As Integer
'######## ab hier anpassen ########
Set oMe = ThisWorkbook.Worksheets("Tabelle1") 'Zieltabelle (in der gerade geöffneten Datei)
Const sDateiPfad As String = "D:\Test\" 'Pfad für zu durchsuchende Excel-Dateien; mit Backslash am Ende
sUeber = Split("Dateiname Name Vertriebskanal Leistung") 'Spaltenüberschriften
iZAbSpalte = 1 'ab dieser Spalte Ergebnisse in die Zieltabelle eintragen
iZAbZeile = 1 'ab dieser Zeile Ergebnisse in die Zieltabelle eintragen (Spaltenüberschriften; Leerzeichen als Trennzeichen)
iQAbSpalte = 1 'ab dieser Spalte Daten in Quelltabelle enthalten
iQAbZeile = 1 'ab dieser Zeile Daten in Quelltabelle enthalten
'######## bis hier anpassen ########
iZSpAnz = UBound(sUeber)
With oMe
.Cells.Clear 'gesamte Zieltabelle löschen
.Range(.Cells(iZAbZeile, iZAbSpalte), .Cells(iZAbZeile, iZAbSpalte + iZSpAnz)).Value = sUeber 'Überschriften eintragen
End With
iZZeile = iZAbZeile + 1
Dim oFS As Object, oDatei As Object
Set oFS = CreateObject("Scripting.FileSystemObject")
For Each oDatei In oFS.GetFolder(sDateiPfad).Files
sWbName = oDatei.Name
Workbooks.Open (sDateiPfad & sWbName)
With Workbooks(sWbName).Worksheets(1)
iQSpalte = iQAbSpalte + 1 'Vertriebskanäle ab dieser Spalte
iQZeile = iQAbZeile 'Überschriften ab dieser Zeile
Do While .Cells(iQZeile, iQSpalte).Value <> ""
iQSpalte = iQSpalte + 1
Loop
iQSpAnz = iQSpalte - iQAbSpalte - 1
sVK = .Range(.Cells(iQAbZeile, iQAbSpalte + 1), .Cells(iQAbZeile, iQAbSpalte + iQSpAnz)).Value
iQZeile = iQZeile + 1
iQSpalte = iQAbSpalte
Do While .Cells(iQZeile, iQAbSpalte).Value <> ""
iQSpalte = iQAbSpalte
sName = .Cells(iQZeile, iQSpalte).Value
For Each sV In sVK
iQSpalte = iQSpalte + 1
iLeist = .Cells(iQZeile, iQSpalte).Value
With oMe
'Eintragung in Zieltabelle; Reihenfolge siehe Überschriften (sUeber)
.Cells(iZZeile, iZAbSpalte).Value = sWbName
.Cells(iZZeile, iZAbSpalte + 1).Value = sName
.Cells(iZZeile, iZAbSpalte + 2).Value = sV
.Cells(iZZeile, iZAbSpalte + 3).Value = iLeist
iZZeile = iZZeile + 1
End With
Next
iQZeile = iQZeile + 1
Loop
End With
Workbooks(sWbName).Saved = True
Workbooks(sWbName).Close
Next
'Sortierung: zunächst (Key1) nach Datei, dann (Key2)nach Name
With oMe
.Cells(iZAbZeile, iZAbSpalte).Sort _
Key1:=.Cells(iZAbZeile, iZAbSpalte), Order1:=xlAscending, _
Key2:=.Cells(iZAbZeile, iZAbSpalte + 1), Order2:=xlAscending, _
Header:=xlYes, OrderCustom:=1, Orientation:=xlTopToBottom
End With
End Sub
Die Zieltabelle wird zu Beginn vollständig gelöscht und abschließend nach Datei und Name sortiert.
Grüße
bastla
hallo bastla,
ich weiß nicht was ich falsch mache, aber ich versuche es zur zeit mit deinem zweiten skript und es wird zwar durchgeführt, doch es erscheinen keine Namen, etc. bei mir in der Tabelle, was mache ich falsch?
Ich habe meine Dateien alles gleich benannt, einen ordner für die fragebögen geschaffen, jedoch durch sucht er zwar aber er schreibt nichts in die Zieltabelle?
Viele Grüße,
Warnickel
ich weiß nicht was ich falsch mache, aber ich versuche es zur zeit mit deinem zweiten skript und es wird zwar durchgeführt, doch es erscheinen keine Namen, etc. bei mir in der Tabelle, was mache ich falsch?
Ich habe meine Dateien alles gleich benannt, einen ordner für die fragebögen geschaffen, jedoch durch sucht er zwar aber er schreibt nichts in die Zieltabelle?
Viele Grüße,
Warnickel
Hallo,
ich würde das Makro gerne ähnlich verwenden, bekomme aber die Anpassung für meine Zwecke nicht hin.
Ich habe mehrere Dateien mit jeweils mehreren Tabellenblättern, die ausgelesen werden sollen und deren Daten in eine Zieldatei übertragen werden sollen.
Das ganze soll mit einem Suchbegriff geschehen, aber je nach Suchbegriff sollen in Relation zum Suchbegriff unterschiedliche Zellen ausgelesen werden.
Bsp. Suchbegriff Name => +1 Spalte rechts soll ausgegeben werden
Bsp. Suchbegriff Auszahlunt Monatsprämie 1 => +1 Zeile darunter soll ausgegeben werden
Bsp. Suchbegriff Zielerreichung MP 1 => + 1 Spalte rechts soll ausggegeben werden
Bsp. Suchbegriff Jan 11 => + Spalte 1-7 rechts davon sollen ausgegeben werden
D.h. ich muss irgendwie für jeden Suchbegriff definieren können, welcher Wert ausgegeben werden soll, dieser soll dann in der Zieldatei jeweils in die Spalte daneben geschrieben werden und die Daten aus dem nächsten Tabellenblatt in einer neuen Zeile etc.
Ich kenn mich mit Makros nicht so gut aus, ein paar kleinere Anpassungen hab ich zwar geschafft, aber jetzt häng ich.
Anbei mein (fehlerhafter) Versuch:
Danke für Eure Hilfe!
Vicky
ich würde das Makro gerne ähnlich verwenden, bekomme aber die Anpassung für meine Zwecke nicht hin.
Ich habe mehrere Dateien mit jeweils mehreren Tabellenblättern, die ausgelesen werden sollen und deren Daten in eine Zieldatei übertragen werden sollen.
Das ganze soll mit einem Suchbegriff geschehen, aber je nach Suchbegriff sollen in Relation zum Suchbegriff unterschiedliche Zellen ausgelesen werden.
Bsp. Suchbegriff Name => +1 Spalte rechts soll ausgegeben werden
Bsp. Suchbegriff Auszahlunt Monatsprämie 1 => +1 Zeile darunter soll ausgegeben werden
Bsp. Suchbegriff Zielerreichung MP 1 => + 1 Spalte rechts soll ausggegeben werden
Bsp. Suchbegriff Jan 11 => + Spalte 1-7 rechts davon sollen ausgegeben werden
D.h. ich muss irgendwie für jeden Suchbegriff definieren können, welcher Wert ausgegeben werden soll, dieser soll dann in der Zieldatei jeweils in die Spalte daneben geschrieben werden und die Daten aus dem nächsten Tabellenblatt in einer neuen Zeile etc.
Ich kenn mich mit Makros nicht so gut aus, ein paar kleinere Anpassungen hab ich zwar geschafft, aber jetzt häng ich.
Anbei mein (fehlerhafter) Versuch:
Sub GetData()
Dim oMe As Object, sBereich As String, iZeile As Integer, iSpalte As Integer, sKennz As String
Dim i As Integer, sWbName As String, rFound As Range
Dim vName As Variant, vVorname As Variant, vBU As Variant, vAbteilung As Variant, vMPK1 As Variant, vMPK2 As Variant, vMPK3 As Variant
Dim oFS As Object, oDatei As Object, wsTabelle As Worksheet, bEintrag As Boolean
Set oMe = ThisWorkbook.Worksheets("Tabelle1") 'Zieltabelle (in der gerade geöffneten Datei)
iZeile = 4 'ab dieser Zeile Ergebnisse in die Zieltabelle eintragen
iSpalte = 1
Const sDateiPfad As String = "H:\Eigene Dateien\Dateienauslesen\" 'Pfad für zu durchsuchende Excel-Dateien; mit Backslash am Ende
Const iSbAnzahl = 7 'Nach x Begriffen suchen
Dim sSuchbegriff(iSbAnzahl) As String
sSuchbegriff(1) = "Name:"
sSuchbegriff(2) = "Vorname:"
sSuchbegriff(3) = "BU:"
sSuchbegriff(4) = "Abteilung:"
sSuchbegriff(5) = "Auszahlung Monatspraemie 1"
sSuchbegriff(6) = "Auszahlung Monatspraemie 2"
sSuchbegriff(7) = "Auszahlung Monatspraemie 3"
sBereich = "A1:Z200"
Set oFS = CreateObject("Scripting.FileSystemObject")
For Each oDatei In oFS.GetFolder(sDateiPfad).Files
sWbName = oDatei.Name
Workbooks.Open (oDatei.Path), Password:="pw", WriteResPassword:="pw"
For Each wsTabelle In Workbooks(sWbName).Worksheets()
For i = 0 To iSbAnzahl
Set rFound = wsTabelle.Range(sBereich).Find(sSuchbegriff(i), LookIn:=xlValues)
If Not rFound Is Nothing Then
vWert = wsTabelle.Cells(rFound.Row, rFound.Column + 1).Value
vVorname = wsTabelle.Cells(rFound.Row, rFound.Column + 1).Value
vBU = wsTabelle.Cells(rFound.Row, rFound.Column + 1).Value
vAbteilung = wsTabelle.Cells(rFound.Row, rFound.Column + 1).Value
vMPK1 = wsTabelle.Cells(rFound.Row + 1, rFound.Column).Value
vMPK2 = wsTabelle.Cells(rFound.Row + 1, rFound.Column).Value
vMPK3 = wsTabelle.Cells(rFound.Row + 1, rFound.Column).Value
With oMe
.Cells(iZeile, i + 1).Value = vName
.Cells(iZeile, i + 2).Value = vVorname
.Cells(iZeile, i + 3).Value = vBU
.Cells(iZeile, i + 4).Value = vAbteilung
.Cells(iZeile, i + 5).Value = vMPK1
.Cells(iZeile, i + 6).Value = vMPK2
.Cells(iZeile, i + 7).Value = vMPK3
bEintrag = True
End With
End If
Next
If bEintrag Then iZeile = iZeile + 1 'mindestens ein Eintrag erfolgt, daher neue Zeile
Next
Workbooks(sWbName).Saved = True
Workbooks(sWbName).Close
Next
End Sub
Danke für Eure Hilfe!
Vicky
Hallo zusammen, bin neu hier, habe ein ähnliches Problem und habe schon alles durchsucht aber nichts passendes gefunden.
Also benutze Excel 2007
habe in einem Verzeichnis lauter Exceldateien stehen, aus diesen Dateien soll immer das Feld H5 in eine neue Tabelle ausgelsen werden und im nächsten feld ein Hyperlink erscheinen so das ich die Datei bei bedarf von dort öffnen kann. Das Feld H5 entält einen Index der für mich die Information ist ob aktuell oder nicht.
Im Moment muss ich jede einzelne Datei öffnen was absolut nervig ist bei ca .500 einzelnen Dateien.
Wäre schön für ein Beispiel wie ich das lösen könnte.
Im Voraus Danke
Also benutze Excel 2007
habe in einem Verzeichnis lauter Exceldateien stehen, aus diesen Dateien soll immer das Feld H5 in eine neue Tabelle ausgelsen werden und im nächsten feld ein Hyperlink erscheinen so das ich die Datei bei bedarf von dort öffnen kann. Das Feld H5 entält einen Index der für mich die Information ist ob aktuell oder nicht.
Im Moment muss ich jede einzelne Datei öffnen was absolut nervig ist bei ca .500 einzelnen Dateien.
Wäre schön für ein Beispiel wie ich das lösen könnte.
Im Voraus Danke
Hallo utroger und willkommen im Forum!
Sollte sich etwa so machen lassen:
Grüße
bastla
Sollte sich etwa so machen lassen:
Sub GetData()
Set oMe = ThisWorkbook.ActiveSheet 'ZielDatei/-Tabelle (= die aktuelle Tabelle der aktuellen Datei)
Const sDateiPfad As String = "D:\Test\" 'Pfad für zu durchsuchende Excel-Dateien; mit Backslash am Ende
sZelle = "H5" 'auszulesende Zelle
iZeile = 2 'ab Zeile 2 in Zieltabelle eintragen
iSpalte = 1 'ab Spalte A in Zieltabelle eintragen
Set oFS = CreateObject("Scripting.FileSystemObject")
For Each oDatei In oFS.GetFolder(sDateiPfad).Files
sWbName = oDatei.Name
If Left(LCase(oFS.GetExtensionName(sWbName)), 3) = "xls" Then
Workbooks.Open (sDateiPfad & sWbName)
oMe.Cells(iZeile, iSpalte).Value = Range(sZelle).Value
oMe.Hyperlinks.Add Anchor:=oMe.Cells(iZeile, iSpalte + 1), Address:=sDateiPfad & sWbName, TextToDisplay:=sWbName
Workbooks(sWbName).Saved = True
Workbooks(sWbName).Close
iZeile = iZeile + 1
End If
Next
End Sub
bastla
erstmal Danke für die Antwort bastla.
Den Hyperlink setzt er in das Tabellenblatt aber in Spalte A wird kein Index aus den auszulesenden Dateien eingetragen.
Also Spalte B steht der Hyperlink und Spalte A bleibt leer wo der Index stehen sollte.
Diese Zeile im Code verstehe ich doch richtig?
Workbooks.Open (sDateiPfad & sWbName)
oMe.Cells(iZeile, iSpalte).Value = Range(sZelle).Value
Also es soll die Datei geöffnet werden, sZelle wäre in diesem Fall Feld H5 und in neue Tabelle vor dem Hyperlink eingetragen werden.
nur er macht es nicht.
würde mich freuen über ein Rückantwort und noch mal DANKE im Voraus.
Grüße
utroger
Den Hyperlink setzt er in das Tabellenblatt aber in Spalte A wird kein Index aus den auszulesenden Dateien eingetragen.
Also Spalte B steht der Hyperlink und Spalte A bleibt leer wo der Index stehen sollte.
Diese Zeile im Code verstehe ich doch richtig?
Workbooks.Open (sDateiPfad & sWbName)
oMe.Cells(iZeile, iSpalte).Value = Range(sZelle).Value
Also es soll die Datei geöffnet werden, sZelle wäre in diesem Fall Feld H5 und in neue Tabelle vor dem Hyperlink eingetragen werden.
nur er macht es nicht.
würde mich freuen über ein Rückantwort und noch mal DANKE im Voraus.
Grüße
utroger
Hallo basla,
wollte nun noch das Feld D5 mit auslesen.
Habe gedacht ich könnte mir den Rest selbst zusammenfügen, habe mich wohl überschätzt.
Er macht das zwar in dem ich den code ein zeitesmal mit aufgenommen habe, aber ich denke das geht noch besser ohne das das Feld "D5" als Hyperlink mit ausgegeben wird und zweimal der Code durchlaufen muss.
hier mal den Code den ich nicht "professionell aber funktioniert" umgestellt habe.
Was bestimmt ein lächeln bei Dir ausführen wird.
Sub GetData()
Set oMe = ThisWorkbook.ActiveSheet 'ZielDatei/-Tabelle (= die aktuelle Tabelle der aktuellen Datei)
Const sDateiPfad As String = "G:\QW_Control-Range\Bauteile\DW8000_Range\" 'Pfad für zu durchsuchende Excel-Dateien; mit Backslash am Ende
sZelle = "H5" 'auszulesende Zelle
iZeile = 2 'ab Zeile 2 in Zieltabelle eintragen
iSpalte = 1 'ab Spalte A in Zieltabelle eintragen
Set oFS = CreateObject("Scripting.FileSystemObject")
For Each oDatei In oFS.GetFolder(sDateiPfad).Files
sWbName = oDatei.Name
If Left(LCase(oFS.GetExtensionName(sWbName)), 3) = "xls" Then
Workbooks.Open (sDateiPfad & sWbName)
oMe.Cells(iZeile, iSpalte).Value = Workbooks(sWbName).ActiveSheet.Range(sZelle).Value
oMe.Hyperlinks.Add Anchor:=oMe.Cells(iZeile, iSpalte + 1), Address:=sDateiPfad & sWbName, TextToDisplay:=sWbName
Workbooks(sWbName).Saved = True
Workbooks(sWbName).Close
iZeile = iZeile + 1
End If
Next
sZelle = "D5" 'auszulesende Zelle
iZeile = 2 'ab Zeile 2 in Zieltabelle eintragen
iSpalte = 2 'ab Spalte A in Zieltabelle eintragen
Set oFS = CreateObject("Scripting.FileSystemObject")
For Each oDatei In oFS.GetFolder(sDateiPfad).Files
sWbName = oDatei.Name
If Left(LCase(oFS.GetExtensionName(sWbName)), 3) = "xls" Then
Workbooks.Open (sDateiPfad & sWbName)
oMe.Cells(iZeile, iSpalte).Value = Workbooks(sWbName).ActiveSheet.Range(sZelle).Value
oMe.Hyperlinks.Add Anchor:=oMe.Cells(iZeile, iSpalte + 1), Address:=sDateiPfad & sWbName, TextToDisplay:=sWbName
Workbooks(sWbName).Saved = True
Workbooks(sWbName).Close
iZeile = iZeile + 1
End If
Next
End Sub
Eilt nicht, aber ich würde Dankbar sein für eine professionelle Lösung
Grüße utroger
wollte nun noch das Feld D5 mit auslesen.
Habe gedacht ich könnte mir den Rest selbst zusammenfügen, habe mich wohl überschätzt.
Er macht das zwar in dem ich den code ein zeitesmal mit aufgenommen habe, aber ich denke das geht noch besser ohne das das Feld "D5" als Hyperlink mit ausgegeben wird und zweimal der Code durchlaufen muss.
hier mal den Code den ich nicht "professionell aber funktioniert" umgestellt habe.
Was bestimmt ein lächeln bei Dir ausführen wird.
Sub GetData()
Set oMe = ThisWorkbook.ActiveSheet 'ZielDatei/-Tabelle (= die aktuelle Tabelle der aktuellen Datei)
Const sDateiPfad As String = "G:\QW_Control-Range\Bauteile\DW8000_Range\" 'Pfad für zu durchsuchende Excel-Dateien; mit Backslash am Ende
sZelle = "H5" 'auszulesende Zelle
iZeile = 2 'ab Zeile 2 in Zieltabelle eintragen
iSpalte = 1 'ab Spalte A in Zieltabelle eintragen
Set oFS = CreateObject("Scripting.FileSystemObject")
For Each oDatei In oFS.GetFolder(sDateiPfad).Files
sWbName = oDatei.Name
If Left(LCase(oFS.GetExtensionName(sWbName)), 3) = "xls" Then
Workbooks.Open (sDateiPfad & sWbName)
oMe.Cells(iZeile, iSpalte).Value = Workbooks(sWbName).ActiveSheet.Range(sZelle).Value
oMe.Hyperlinks.Add Anchor:=oMe.Cells(iZeile, iSpalte + 1), Address:=sDateiPfad & sWbName, TextToDisplay:=sWbName
Workbooks(sWbName).Saved = True
Workbooks(sWbName).Close
iZeile = iZeile + 1
End If
Next
sZelle = "D5" 'auszulesende Zelle
iZeile = 2 'ab Zeile 2 in Zieltabelle eintragen
iSpalte = 2 'ab Spalte A in Zieltabelle eintragen
Set oFS = CreateObject("Scripting.FileSystemObject")
For Each oDatei In oFS.GetFolder(sDateiPfad).Files
sWbName = oDatei.Name
If Left(LCase(oFS.GetExtensionName(sWbName)), 3) = "xls" Then
Workbooks.Open (sDateiPfad & sWbName)
oMe.Cells(iZeile, iSpalte).Value = Workbooks(sWbName).ActiveSheet.Range(sZelle).Value
oMe.Hyperlinks.Add Anchor:=oMe.Cells(iZeile, iSpalte + 1), Address:=sDateiPfad & sWbName, TextToDisplay:=sWbName
Workbooks(sWbName).Saved = True
Workbooks(sWbName).Close
iZeile = iZeile + 1
End If
Next
End Sub
Eilt nicht, aber ich würde Dankbar sein für eine professionelle Lösung
Grüße utroger
Hallo utroger!
Sollte eher so aussehen:
Bei noch mehr zu übernehmenden Zellen würde sich dann eine eigene Schleife anbieten ...
Grüße
bastla
Sollte eher so aussehen:
Sub GetData()
Set oMe = ThisWorkbook.ActiveSheet 'ZielDatei/-Tabelle (= die aktuelle Tabelle der aktuellen Datei)
Const sDateiPfad As String = "D:\Test\" 'Pfad für zu durchsuchende Excel-Dateien; mit Backslash am Ende
sZelle1 = "H5" 'auszulesende Zelle
sZelle2 = "D5" 'weitere auszulesende Zelle
iZeile = 2 'ab Zeile 2 in Zieltabelle eintragen
iSpalte = 1 'ab Spalte A in Zieltabelle eintragen
Set oFS = CreateObject("Scripting.FileSystemObject")
For Each oDatei In oFS.GetFolder(sDateiPfad).Files
sWbName = oDatei.Name
If Left(LCase(oFS.GetExtensionName(sWbName)), 3) = "xls" Then
Workbooks.Open (sDateiPfad & sWbName)
oMe.Cells(iZeile, iSpalte).Value = Workbooks(sWbName).ActiveSheet.Range(sZelle1).Value
oMe.Cells(iZeile, iSpalte + 1).Value = Workbooks(sWbName).ActiveSheet.Range(sZelle2).Value
oMe.Hyperlinks.Add Anchor:=oMe.Cells(iZeile, iSpalte + 2), Address:=sDateiPfad & sWbName, TextToDisplay:=sWbName
Workbooks(sWbName).Saved = True
Workbooks(sWbName).Close
iZeile = iZeile + 1
End If
Next
End Sub
Grüße
bastla
Hallo zusammen,
mein Problem geht vermutlich auch in die Richtung, deshalb häng ichs hier dran.
Die Quelldatei enthält 3 Spalten (Vokabelnummer "NR", deutsches Wort "GER", englische Übersetzung "ENG"). In der Zieldatei befinden sich nur noch die Überschriften "NR" und "ENG", wobei "NR" (mehrere Zeilen) bereits vorgegeben sind und als Suchkriterium dienen! Das Makro soll nun mit Hilfe dieser Vorgaben die Quelldatei durchsuchen und die entsprechenden Felder (ENG) übertragen.
Zur Veranschaulichung:
http://www25.zippyshare.com/v/10032778/file.html
Danke!
mein Problem geht vermutlich auch in die Richtung, deshalb häng ichs hier dran.
Die Quelldatei enthält 3 Spalten (Vokabelnummer "NR", deutsches Wort "GER", englische Übersetzung "ENG"). In der Zieldatei befinden sich nur noch die Überschriften "NR" und "ENG", wobei "NR" (mehrere Zeilen) bereits vorgegeben sind und als Suchkriterium dienen! Das Makro soll nun mit Hilfe dieser Vorgaben die Quelldatei durchsuchen und die entsprechenden Felder (ENG) übertragen.
Zur Veranschaulichung:
http://www25.zippyshare.com/v/10032778/file.html
Danke!
Hallo,
das Thema passt genau in meine Richtung und ich frage an nach Details für mein spezifisches Problem.
Danke für die bisher super Erklärung und Vorlage für ein eigenes Makro.
Das Makro funktioniert soweit erstmal für Zellen mit einfachen Inhalten. Ich muss es nun weiter anpassen. Dazu meine Fragen:
- Was muss ich statt "sZelle" eintragen, wenn ich zusammenhängende Felder habe? Feld T38 bis X38 z.B.
- Teilweise sind die Felder mit SVERWEISEN. Wie kann ich einstellen, dass nur der Text kopiert wird? Müller statt SVERWEIS(....)
Letzte Frage noch zum Ablauf.
Wie kann ich einstellen, dass nicht alle Dateien neu reinkopiert werden, sondern nur noch neue? Manuell kann ich das so machen, dass im Ordner die alten gelöscht werden, aber denke, dass es vom Makro her auch gehen sollte, oder?
Wenn ich das Makro mit F5 starte, überschreibt er standardmäßig immer die erste Zeile oder hängt er sich unten dran?
Danke für die Mühe.
das Thema passt genau in meine Richtung und ich frage an nach Details für mein spezifisches Problem.
Danke für die bisher super Erklärung und Vorlage für ein eigenes Makro.
Das Makro funktioniert soweit erstmal für Zellen mit einfachen Inhalten. Ich muss es nun weiter anpassen. Dazu meine Fragen:
- Was muss ich statt "sZelle" eintragen, wenn ich zusammenhängende Felder habe? Feld T38 bis X38 z.B.
- Teilweise sind die Felder mit SVERWEISEN. Wie kann ich einstellen, dass nur der Text kopiert wird? Müller statt SVERWEIS(....)
Letzte Frage noch zum Ablauf.
Wie kann ich einstellen, dass nicht alle Dateien neu reinkopiert werden, sondern nur noch neue? Manuell kann ich das so machen, dass im Ordner die alten gelöscht werden, aber denke, dass es vom Makro her auch gehen sollte, oder?
Wenn ich das Makro mit F5 starte, überschreibt er standardmäßig immer die erste Zeile oder hängt er sich unten dran?
Danke für die Mühe.
Hallo zusammen,
ich bin froh, dass ich auf diese Seite gestossen bin, denn auch ich moechte gerne Daten aus eine Excel Tabelle auslesen um sie zunaechst im Format zu vereinheitlichen und dann mit xml auszulesen.
Bei mir sind die Daten jedoch deutlich unstrukturierter. Die Daten werden normalerweise mit Bezeichnung und Wert gefuehrt, koennen aber ganz unterschiedlich aufgeschluesselt sein:
1.Der einfachste Fall: Alles steht einzeln in Spalten oder Zeilen
A:B
Vorname:Martin
Nachname: Heinz
oder
A:B
Vorname:Nachname:
Martin:Heinz
2.So gibt es Bezeichnung - Werte die hintereinander in einer Zeile vorkommen.
Bsp:
A:B:C:D:E
1. Vorname:Sven: :Nachname:Mueller
2. Geburtstag:13.10.2013: :Beruf: Ackerer
3.Es gibt Bezeichnung- Werte bei denen Leerzeichen dazwischen stehen.
Bsp:
A:B:C:
Vorname: : Sven:
4. Um die Verwirrung komplett zu machen werden teils unterschiedliche Bezeichnungen vergeben, also bei manchen heisst es geboren am, bei anderen Geburtstag.
Diese Daten wuerde ich gerne in eine Tabelle bringen, um sie mit xml zu exportieren.
Das soll dann so aussehen:
A:B:C:D
1.Vorname:Nachname:Geburtstag:Beruf
2.Sven:Mueller:22.04.2013:Ackerer
3.Martin:Heinz 13.10.2013:Landwirt
Ist das moeglich oder zu unstrukturiert?
Viele Gruesse
oceangirl
ich bin froh, dass ich auf diese Seite gestossen bin, denn auch ich moechte gerne Daten aus eine Excel Tabelle auslesen um sie zunaechst im Format zu vereinheitlichen und dann mit xml auszulesen.
Bei mir sind die Daten jedoch deutlich unstrukturierter. Die Daten werden normalerweise mit Bezeichnung und Wert gefuehrt, koennen aber ganz unterschiedlich aufgeschluesselt sein:
1.Der einfachste Fall: Alles steht einzeln in Spalten oder Zeilen
A:B
Vorname:Martin
Nachname: Heinz
oder
A:B
Vorname:Nachname:
Martin:Heinz
2.So gibt es Bezeichnung - Werte die hintereinander in einer Zeile vorkommen.
Bsp:
A:B:C:D:E
1. Vorname:Sven: :Nachname:Mueller
2. Geburtstag:13.10.2013: :Beruf: Ackerer
3.Es gibt Bezeichnung- Werte bei denen Leerzeichen dazwischen stehen.
Bsp:
A:B:C:
Vorname: : Sven:
4. Um die Verwirrung komplett zu machen werden teils unterschiedliche Bezeichnungen vergeben, also bei manchen heisst es geboren am, bei anderen Geburtstag.
Diese Daten wuerde ich gerne in eine Tabelle bringen, um sie mit xml zu exportieren.
Das soll dann so aussehen:
A:B:C:D
1.Vorname:Nachname:Geburtstag:Beruf
2.Sven:Mueller:22.04.2013:Ackerer
3.Martin:Heinz 13.10.2013:Landwirt
Ist das moeglich oder zu unstrukturiert?
Viele Gruesse
oceangirl
Hallo oceangirl und willkommen im Forum!
Erstelle besser einen eigenen "Frage"-Beitrag - hier dürften nicht allzu viele potenzielle Helfer vorbeikommen ...
... ansonsten: Wenn die Dateien derart unterschiedlich aufgebaut sind, solltest Du vorweg Dateien mit gleicher Struktur in Ordnern zusammenfassen und dann auf jeden Ordner eine angepasste VBA-Variante "los lassen" ...
Grüße
bastla
Erstelle besser einen eigenen "Frage"-Beitrag - hier dürften nicht allzu viele potenzielle Helfer vorbeikommen ...
... ansonsten: Wenn die Dateien derart unterschiedlich aufgebaut sind, solltest Du vorweg Dateien mit gleicher Struktur in Ordnern zusammenfassen und dann auf jeden Ordner eine angepasste VBA-Variante "los lassen" ...
Grüße
bastla
Hallo zusammen,
hallo hilfsbereiter Bastla,
gerne mache ich auch einen neuen Thread auf, aber mein Problem ähnelt den hier Erwähnten sehr.
folgendes Problem: ich habe rund 60 verschiedene Exceldateien mit jeweils 53 Reitern. 52 Reiter sollen vom Bediener ausgefüllt werden und der letzte Reiter enthält alle Daten des gesamten Jahres in Tabellenform (Name des Reiters in allen 60 Dateien ist "Auswertung 2016").
Aufbau des Reiters Auswertung 2016, welcher so auch in die neue große Auswertung übernommen werden soll:
Datum Name Zeit Beschreibung usw....
xx.xx.xx xxx xx:xx xxx
Alle Dateien liegen in einem Ordner, die Reiter und der Aufbau ist komplett identisch, nur die Benennung der Datei ist unterschiedlich (z.B. B01, B02, B03,...,B60, usw.). Nun möchte ich wöchentlich in einem neuen Tabellenblatt und einem neuen Ordner eine Auswertung fahren und alle gesammelten Daten der 60 Dateien in einer Pivottabelle grafisch darstellen. Kann mir jemand mit einem Makro weiterhelfen, eventuell der hilfsbereite bastla?
Vielen Dank und beste Grüße
AlexBerlin
hallo hilfsbereiter Bastla,
gerne mache ich auch einen neuen Thread auf, aber mein Problem ähnelt den hier Erwähnten sehr.
folgendes Problem: ich habe rund 60 verschiedene Exceldateien mit jeweils 53 Reitern. 52 Reiter sollen vom Bediener ausgefüllt werden und der letzte Reiter enthält alle Daten des gesamten Jahres in Tabellenform (Name des Reiters in allen 60 Dateien ist "Auswertung 2016").
Aufbau des Reiters Auswertung 2016, welcher so auch in die neue große Auswertung übernommen werden soll:
Datum Name Zeit Beschreibung usw....
xx.xx.xx xxx xx:xx xxx
Alle Dateien liegen in einem Ordner, die Reiter und der Aufbau ist komplett identisch, nur die Benennung der Datei ist unterschiedlich (z.B. B01, B02, B03,...,B60, usw.). Nun möchte ich wöchentlich in einem neuen Tabellenblatt und einem neuen Ordner eine Auswertung fahren und alle gesammelten Daten der 60 Dateien in einer Pivottabelle grafisch darstellen. Kann mir jemand mit einem Makro weiterhelfen, eventuell der hilfsbereite bastla?
Vielen Dank und beste Grüße
AlexBerlin