123jenny789
Goto Top

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

Content-ID: 303485

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

Ausgedruckt am: 04.11.2024 um 23:11 Uhr

colinardo
colinardo 03.05.2016, aktualisiert am 04.05.2016 um 15:56:42 Uhr
Goto Top
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

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
Grüße Uwe
123Jenny789
123Jenny789 04.05.2016 um 10:39:19 Uhr
Goto Top
Hi Uwe,

vielen lieben Dank für deine schnelle Hilfe! Sieht schon mal seeeeehr gut aus!

Leider habe ich irgendwie Probleme meinen Quellordner einzufügen: "Fehler beim Kompilieren
Erwartet Zeilennummer oder Sprungmarke oder Anweisung oder Anweisungsende"

In folgendem Ordner sind die Dateien abgelegt:
file:\\europe.corp\WINFS\FR-org\Fr-30\001%20Zentrale%20Steuerakte\90%20-%20Förderung\01_Investitionszulage\01_Anträge\BMW%20AG\Zusammenfassung%20Anträge%202009%20-%202013%20für%20Inventarnummernsuche%20mit%20Makro

Könntest du mir vielleicht sagen, wie ich diesen Pfad einfügen muss face-sad

Vielen lieben Dank!
Jenny
colinardo
Lösung colinardo 04.05.2016 aktualisiert um 13:59:51 Uhr
Goto Top
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.
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"  
Sollte es ein WebDAV Ordner sein wäre es klüger diesen erst auf ein Laufwerksbuchstaben zu mappen.

Grüße Uwe
123Jenny789
123Jenny789 04.05.2016 um 14:51:50 Uhr
Goto Top
Hi Uwe,

wow - es funktioniert face-smile Ich bin begeistert.
Allerdings gibt es noch ein kleines Problem face-confused Wenn ich nach einer Inventarnummer suche, die in einer Spalte mit der Bezeichnung INV_NR hinterlegt ist, dann findet das Makro keine Werte.
Weißt du woran das liegen kann?

Danke und LG,
Jenny
colinardo
colinardo 04.05.2016 aktualisiert um 14:58:05 Uhr
Goto Top
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.
123Jenny789
123Jenny789 04.05.2016 um 15:02:29 Uhr
Goto Top
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?
colinardo
colinardo 04.05.2016 aktualisiert um 15:27:23 Uhr
Goto Top
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.
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.
123Jenny789
123Jenny789 04.05.2016 um 15:35:25 Uhr
Goto Top
Hi Uwe,

ich habe schon ein Problem gefunden. Es gibt auch Spalten in der der Wert ist, die mit "Pos_Betr incl Skonto " überschrieben sind.
Dachte ich bekomm das selber ins Marko, hab es aber selbstverständlich nicht hingebracht face-sad
Könntest du hier nochmal unterstützen?

Vieeeeelen Dank!
colinardo
colinardo 04.05.2016 aktualisiert um 15:57:54 Uhr
Goto Top
Habe es im ersten Code oben so angepasst, dass du es leicht selber erweitern kannst. (Zeile 34)
123Jenny789
123Jenny789 04.05.2016 um 16:28:25 Uhr
Goto Top
Funktioniert fast perfekt: Das System bringt eine Teilmenge bricht dann aber mit folgender Fehlermeldung ab:
Laufzeitfehler '432':
Datei- oder Klassenname während Automatisierungsoperation nicht gefunden

Wenn ich dann den Debugger öffne ist folgende Zeile (Zeile 29 oben) gelb markiert:
Set wbSearch = GetObject(file.Path)

Vielen Dank, ich hoffe das war die letzte Frage.....
colinardo
colinardo 04.05.2016 um 16:49:01 Uhr
Goto Top
Dann ist entweder eines deiner Sheets beschädigt oder der Pfad ist zu lang oder hat Sonderzeichen. Deswegen sagte ich ist es besser den Pfad kürzer auf ein Laufwerk zu mappen. Ich kann zwar den Fehler abfangen und das File überspringen, aber das ist ja nicht Sinn der Sache, außer du willst das.