Excel 2013 Suche nach einem Wert in mehreren Excel-Dateien und Ausgabe weiterer Werte zum Suchergebnis incl. Angabe der Quelldatei mittel Makro
Hallo Zusammen,
ich habe mich nun schon durch mehrere ähnliche Breitäge gekäpmft bin aber mit meinen mangelnden Makro-Kenntnissen leider nicht zum Ziel gekommen.
Insofern versuche ich hier mal mein Glück und hoffe auf Hilfe:
Ich habe in Summe 42 Datein (zwischen 13 - 1800 KB groß), die größtenteils mehrere Tabellenblätter (alle unterschiedlich bezeichnet) beinhalten.
Über ein Makro würde ich gerne einen bestimmten Wert (z.B. 1579682 oder 0010025203 --> formatiert als Text) in diesen Dateien suchen.
Der Wert kann dabei in mehreren Dateien und auch in einer Datei mehrmals vorkommen. Die Quelldateien haben dabei leider alle ein verschiedenes Format, sodass der Sucherwert mal in Spalte B, C oder Z sein kann. Die Spalte in der gesucht werden soll ist jedoch entweder mit "Inventarnummer" oder mit "INV_NR" bezeichnet.
In einer neuen Excel-Datei sollen nun zu jedem gefunden Eintrag ein Betrag (Teilanschaffungskosten) ausgegeben werden, der in der gleichen Zeile steht. Auch hier gilt, dass der Ausgabewert in jeder der Dateien in einer anderen Spalte sein kann - die Bezeichnung der Spalte ist allerdings "Betrags Hauswähr" oder "POS_BTR NETTO". Zusätzlich soll angezeigt werden, in welcher Datei der Wert gefunden wurde.
Als Beispiel:
Es gibt die Dateien Excel 1 - 20. Sucht man nach dem Wert 1579682 so kommt dieser in den Tabellen 1 -> 5x, 13 --> 80x und 19 -> 2x pro Tabellenblatt (bei z.B. 3 Tabellenblättern also 6x)
Die neue Excel Datei sollte also nun 91 Einträge haben:
Inventarnummer I Teilanschaffungskosten I Quelldatei
1579682 I 1200,00 I Excel 1
1579682 I 980,00 I Excel 1
1579682 I 450,00 I Excel 1
1579682 I 1900,00 I Excel 1
1579682 I 2400,00 I Excel 1
1579682 I 3000,00 I Excel 13
1579682 I 8900,00 I Excel 13
1579682 I 2000,00 I Excel 13
1579682 I 320,00 I Excel 19
Ich hoffe ich konnte den Sachverhalt nachvollziehbar darstellen sodass mir jemand weiterhelfen kann.
Vielen Dank bereits jetzt!
Jenny
ich habe mich nun schon durch mehrere ähnliche Breitäge gekäpmft bin aber mit meinen mangelnden Makro-Kenntnissen leider nicht zum Ziel gekommen.
Insofern versuche ich hier mal mein Glück und hoffe auf Hilfe:
Ich habe in Summe 42 Datein (zwischen 13 - 1800 KB groß), die größtenteils mehrere Tabellenblätter (alle unterschiedlich bezeichnet) beinhalten.
Über ein Makro würde ich gerne einen bestimmten Wert (z.B. 1579682 oder 0010025203 --> formatiert als Text) in diesen Dateien suchen.
Der Wert kann dabei in mehreren Dateien und auch in einer Datei mehrmals vorkommen. Die Quelldateien haben dabei leider alle ein verschiedenes Format, sodass der Sucherwert mal in Spalte B, C oder Z sein kann. Die Spalte in der gesucht werden soll ist jedoch entweder mit "Inventarnummer" oder mit "INV_NR" bezeichnet.
In einer neuen Excel-Datei sollen nun zu jedem gefunden Eintrag ein Betrag (Teilanschaffungskosten) ausgegeben werden, der in der gleichen Zeile steht. Auch hier gilt, dass der Ausgabewert in jeder der Dateien in einer anderen Spalte sein kann - die Bezeichnung der Spalte ist allerdings "Betrags Hauswähr" oder "POS_BTR NETTO". Zusätzlich soll angezeigt werden, in welcher Datei der Wert gefunden wurde.
Als Beispiel:
Es gibt die Dateien Excel 1 - 20. Sucht man nach dem Wert 1579682 so kommt dieser in den Tabellen 1 -> 5x, 13 --> 80x und 19 -> 2x pro Tabellenblatt (bei z.B. 3 Tabellenblättern also 6x)
Die neue Excel Datei sollte also nun 91 Einträge haben:
Inventarnummer I Teilanschaffungskosten I Quelldatei
1579682 I 1200,00 I Excel 1
1579682 I 980,00 I Excel 1
1579682 I 450,00 I Excel 1
1579682 I 1900,00 I Excel 1
1579682 I 2400,00 I Excel 1
1579682 I 3000,00 I Excel 13
1579682 I 8900,00 I Excel 13
1579682 I 2000,00 I Excel 13
1579682 I 320,00 I Excel 19
Ich hoffe ich konnte den Sachverhalt nachvollziehbar darstellen sodass mir jemand weiterhelfen kann.
Vielen Dank bereits jetzt!
Jenny
Bitte markiere auch die Kommentare, die zur Lösung des Beitrags beigetragen haben
Content-ID: 303485
Url: https://administrator.de/contentid/303485
Ausgedruckt am: 04.11.2024 um 23:11 Uhr
11 Kommentare
Neuester Kommentar
Hallo Jenny, Willkommen auf Administrator.de!
Habe dir mal ein Paket mit einem Makrosheet und zwei Beispieldateien welche Demodaten enthalten zusammengestellt:
search_in_workbooks_and_copy_data_303485.zip
Hier noch der Code aus dem Sheet
Grüße Uwe
Habe dir mal ein Paket mit einem Makrosheet und zwei Beispieldateien welche Demodaten enthalten zusammengestellt:
search_in_workbooks_and_copy_data_303485.zip
Hier noch der Code aus dem Sheet
Sub SearchAndCopyData()
'Variablen
Dim fso As Object, strFind As String, wsTarget As Worksheet, file As Object, sh As Worksheet, rngCol As Range, c As Range, firstAddress As String, dblKosten As Double, strFolder As String, strHeader As Variant
'Ordner in dem sich die xlsx-Dateien befinden (im Beispiel der aktuelle Pfad in dem sich diese Mappe befindet)
strFolder = ThisWorkbook.Path
'Objekte
Set fso = CreateObject("Scripting.FileSystemObject")
'Sheet festlegen in das die Daten kopiert werden
Set wsTarget = Sheets(1)
'Eingabeaufforderung für die Nummer
strFind = InputBox("Bitte geben sie Ihre Nummer ein:", "Nummer suchen", "123456")
If strFind <> "" Then
'Screenflicker und Dialoge unterdrücken
Application.ScreenUpdating = False
Application.DisplayAlerts = False
'Ausgabebereich löschen
wsTarget.Range("A2:C10000").Clear
'Für jede Datei im Ordner ...
For Each file In fso.GetFolder(strFolder).Files
'Wenn es eine 'xlsx' Datei ist
If LCase(fso.GetExtensionName(file.Name)) = "xlsx" Then
'Mappe öffnen
Set wbSearch = GetObject(file.Path)
' Alle Sheets der Datei durchsuchen
For Each sh In wbSearch.Sheets
With sh.UsedRange
'Suche Spaltenüberschrift 'POS_BTR NETTO' oder 'Betrags_Hauswähr' oder 'Pos_Betr incl Skonto'
For Each strHeader In Array("POS_BTR NETTO", "Betrags_Hauswähr", "Pos_Betr incl Skonto")
Set rngCol = sh.UsedRange.Find(strHeader, LookIn:=xlValues, LookAt:=xlWhole)
If Not rngCol Is Nothing Then Exit For
Next
'Wurde eine der Spalten gefunden, suche Inventarnummer
If Not rngCol Is Nothing Then
Set c = .Find(strFind, LookIn:=xlValues, LookAt:=xlWhole)
If Not c Is Nothing Then
firstAddress = c.Address
Do
'Nummer wurde gefunden ... extrahiere Wert
dblKosten = sh.Cells(c.Row, rngCol.Column).Value
' und schreibe Ihn in unser Sammelsheet in die nächste freie Zeile zusammen mit Inventarnummer und Pfad zur Datei
wsTarget.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Resize(1, 3).Value = Array(strFind, dblKosten, file.Path)
'Suche weiter
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End If
End With
Next
' schließe die Mappe
wbSearch.Close False
End If
Next
'Spaltenbreiten automatisch anpassen
wsTarget.Range("A:C").EntireColumn.AutoFit
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End If
End Sub
Hallo Jenny,
ist das ein WebDAV- oder ein normal über SMB erreichbarer Ordner?
Erstens solltest du das führende file: entfernen und zweitens die URL enkodierten Zeichen (%20) durch ihr richtigen ersetzen und dann das ganze zwingend in Anführungszeichen setzen.
Sollte es ein WebDAV Ordner sein wäre es klüger diesen erst auf ein Laufwerksbuchstaben zu mappen.
Grüße Uwe
ist das ein WebDAV- oder ein normal über SMB erreichbarer Ordner?
Erstens solltest du das führende file: entfernen und zweitens die URL enkodierten Zeichen (%20) durch ihr richtigen ersetzen und dann das ganze zwingend in Anführungszeichen setzen.
strFolder = "\\europe.corp\WINFS\FR-org\Fr-30\001 Zentrale Steuerakte\90 - Förderung\01_Investitionszulage\01_Anträge\BMW AG\Zusammenfassung Anträge 2009 - 2013 für Inventarnummernsuche mit Makro"
Grüße Uwe
Suchst du nach einer exakten Nummer oder sollen auch "ähnliche" Werte gefunden werden? Sind die Inventarnummern aus Formeln zusammengesetzt oder eventuell haben sie auf den ersten Blick nicht sichtbare Leerzeichen in der Zelle.
Im jetzigen Zustand sucht das Makro nach exakt deiner angegebenen Nummer in den Zellwerten, da reicht schon ein nicht sichtbares Zeichen damit es nichts findet, das lässt sich aber wie gesagt anpassen wenn das gewünscht ist.
Im jetzigen Zustand sucht das Makro nach exakt deiner angegebenen Nummer in den Zellwerten, da reicht schon ein nicht sichtbares Zeichen damit es nichts findet, das lässt sich aber wie gesagt anpassen wenn das gewünscht ist.
Zitat von @123Jenny789:
Ich suche nach einer exakten Nummen die aber als Text formatiert ist. Sie bestehen nicht aus Formeln, sondern sind direkt in der jeweiligen Zelle eingegeben. Es sind keine Leerzeichen enthalten.
OK dann ist das i.O.Ich suche nach einer exakten Nummen die aber als Text formatiert ist. Sie bestehen nicht aus Formeln, sondern sind direkt in der jeweiligen Zelle eingegeben. Es sind keine Leerzeichen enthalten.
Muss ich die Werte in Zahlen umwandeln lassen durch Excel?
Nein.Die Nummern müssen so gefunden werden, außer deine Sheets haben einen besonderen Aufbau, dazu müsstest du mir mal ein Beispiel-Sheet zur Verfügung stellen damit ich es mit mal genauer ansehen kann.
Achtung:
Wenn es in diesem Sheet natürlich keine deiner genannten Spaltennamen für den Wert(Betrag) gibt, wird dieser auch nicht in den Suchergebnissen auftauchen, das habe ich so programmiert.