Bestimmte Zellen aus einer Vielzahl an Exceldokumenten in eine Zieldatei Einpflegen

  • Excel

Es gibt 13 Antworten in diesem Thema. Der letzte Beitrag () ist von petaod.

    Bestimmte Zellen aus einer Vielzahl an Exceldokumenten in eine Zieldatei Einpflegen

    Hallihallo,

    ich bin sehr interessiert VBA zu lernen, bis jetzt fehlt aber leider das Fachvokabular um folgende Aufgabe zu lösen:

    Ich bekomme regelmäßig einen Ordner mit ca 100-150 (Quell-)Exceldateien, aus denen ich letztlich jedes mal dieselben Zellen markieren muss und in eine Zieltabelle kopiere. Dies ist mühselig und ich weiß dass es sicher für einen VBA Programmierer ein leichtes ist. Für mich leider nicht.

    Also ich brauche 4 feste Zeilen (z.B A1, B1,E1, F1) aus Worksheet 2 und dann nochmal 3 Zellen (F1, F3, E4) aus Worksheet 1. Diese sollen allesamt in eine Zeile der Zieltabelle, so dass sich bei den 150 Dateien am Ende 150 Zeilen in meiner Zieltabelle ergeben.

    Ich wäre wirklich sehr dankbar, wenn mir jemand diese mühselige Handarbeit erleichtern könnte....

    Vielen lieben Dank schonmal!
    Ich versuch's mal mit einem unvollständigen Ansatz zum Vervollständigen.
    Die Files suchen:

    Visual Basic-Quellcode

    1. Set FS = CreateObject("Scripting.FileSystemObject")
    2. Set Folder = FS.GetFolder("c:\x")
    3. For Each File In Folder.Files
    4. If File.Name Like "*.xlsx" Then
    5. ProcessFile File.Path
    6. End If
    7. Next

    Die Files verarbeiten:

    Visual Basic-Quellcode

    1. Sub ProcessFile(ByVal File As String)
    2. Set wb=Workbooks.Open(File)
    3. Set ws1=wb.Sheets(1)
    4. Set ws2=wb.Sheets(2)
    5. r = Cells(Rows.Count,1).End(xlUp).Row+1
    6. Cells(r,1).Value=ws2.Range("A2").Value
    7. Cells(r,5).Value=ws1.Range("F1").Value
    8. wb.Close False
    9. End Sub

    Ungetestet, aber hoffentlich lauffähig.

    P.S.: Der Code gehört in den Codebereich des Ziel-Arbeitsblatts.
    --
    If Not Program.isWorking Then Code.Debug Else Code.DoNotTouch
    --

    bayer007 schrieb:

    Hallihallo,

    Also ich brauche 4 feste Zeilen (z.B A1, B1,E1, F1) aus Worksheet 2 und dann nochmal 3 Zellen (F1, F3, E4) aus Worksheet 1. Diese sollen allesamt in eine Zeile der Zieltabelle, so dass sich bei den 150 Dateien am Ende 150 Zeilen in meiner Zieltabelle ergeben.



    Was meinst Du mit "4 feste Zeilen'? A1 entspricht z.B. Zeile 1 und Spalte A. Ich denke du meinst Zelle A1, B1, E1 und F1. Richtig? Und in welchen Bereich der Ziel-Datei sollen die Zellen anschließend kopiert werden? Weil die Zellen aus Worksheet 2 und 1 kannst Du nicht in eine Zeile kopieren, weil sich F1 in beiden wiederholt. Die würden dann überschrieben.
    Ganz genau, ich meine natürlich diese Zellen aus den Quelldateien. Und in der Zieldatei sollen sich einfach in aufeinander folgende Spalten. Da ich ja 7 Werte der Quelldatei entnehmen möchte einfach in die ersten 7 Spalten. (Das ist eine völlig neue Tabelle wo einfach diese 7 Werte für jedes Exceldokument rein sollen. Also wie oben geschrieben dann 150 Zeilen mit je 7 Spalten)
    ...Du musst eigentlich nur den Pfad (varSourceFolder) abändern bzw. auch noch die .xlsx -Endung der Excel-Dateien je nach Version. Habe es getestet und es funktioniert, leider weiß ich nicht wie lange es für 150 Dateien dauert.

    PS: Kann man den Quellcode auch mit entsprechender Formatierung/Einrückung hier ins Forum einfügen?

    Visual Basic-Quellcode

    1. Option Explicit
    2. Sub ConcatExcelFiles()
    3. Application.ScreenUpdating = False
    4. Dim varSourceFolder As Variant
    5. Dim strSourceFile As String
    6. Dim arrWks1(1 To 3) 'F1, F3, E4 aus Worksheet 1
    7. Dim arrWks2(4 To 7) 'A1, B1, E1, F1 aus Worksheet 2
    8. Dim i As Integer
    9. Dim lastRow As Integer
    10. 'Neues Workbook für die Zusammenfassung erstellen
    11. Workbooks.Add
    12. With ActiveWorkbook
    13. .SaveAs Environ("userprofile") & "\Desktop\Zusammenfassung.xlsx"
    14. End With
    15. varSourceFolder = Environ("userprofile") & "\Desktop\ConcatFilesFolder\" 'Hier Pfad vom Ordner mit den 150 Excel-Files angeben
    16. strSourceFile = Dir(varSourceFolder & "*.xlsx")
    17. Do While varSourceFolder <> ""
    18. On Error GoTo Errhandler
    19. Workbooks.Open (varSourceFolder & strSourceFile)
    20. With ActiveWorkbook
    21. arrWks1(1) = Sheets(1).Range("F1")
    22. arrWks1(2) = Sheets(1).Range("F3")
    23. arrWks1(3) = Sheets(1).Range("E4")
    24. arrWks2(4) = Sheets(2).Range("A1")
    25. arrWks2(5) = Sheets(2).Range("B1")
    26. arrWks2(6) = Sheets(2).Range("E1")
    27. arrWks2(7) = Sheets(2).Range("F1")
    28. .Close savechanges:=False
    29. End With
    30. With Workbooks("Zusammenfassung.xlsx")
    31. lastRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
    32. For i = 1 To 3
    33. .Sheets(1).Cells(lastRow, i) = arrWks1(i)
    34. Next i
    35. For i = 4 To 7
    36. .Sheets(1).Cells(lastRow, i) = arrWks2(i)
    37. Next i
    38. End With
    39. strSourceFile = Dir
    40. Loop
    41. Exit Sub
    42. Errhandler:
    43. Workbooks("Zusammenfassung").Close savechanges:=True
    44. End Sub

    Dieser Beitrag wurde bereits 2 mal editiert, zuletzt von „cry.baby“ ()

    @cry.baby:
    Das ist zwar etwas umständlich und auch nicht gerade elegant.
    Aber es könnte funktionieren.

    Da du jeweils mit ActiveWorkbook arbeitest, bist du zu dem Zwischenspeicher-Array verdammt (aber warum 2 davon?).

    Die ungarische Notation entspricht nicht dem Microsoft-Standard.
    Sie macht den Code schwerer lesbar.
    Aber das werte ich als Philosophie-Frage.

    cry.baby schrieb:

    lastRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
    funktioniert eher zufällig, weil ActiveSheet gerade auf Sheet1 zeigt.
    Ein falscher Mausklick während der Ausführung und es gibt Chaos.
    --
    If Not Program.isWorking Then Code.Debug Else Code.DoNotTouch
    --

    Dieser Beitrag wurde bereits 1 mal editiert, zuletzt von „petaod“ ()

    petaod schrieb:

    @cry.baby:
    Das ist zwar etwas umständlich und auch nicht gerade elegant.
    Aber es könnte funktionieren.
    Es funktioniert. Habe es ausprobiert. :P Was heißt elegant? Bin offen für Anregungen als Hobbyprogrammierer!

    petaod schrieb:

    Da du jeweils mit ActiveWorkbook arbeitest, bist du zu dem Zwischenspeicher-Array verdammt (aber warum 2 davon?).
    Hm, gute Frage! Man kann es natürlich in ein Array packen. Jetzt wo Du es sagst... :whistling:

    petaod schrieb:

    Die ungarische Notation entspricht nicht dem Microsoft-Standard.
    Sie macht den Code schwerer lesbar.
    Aber das werte ich als Philosophie-Frage.
    Was hat das zu bedeuten? :?:

    petaod schrieb:

    funktioniert eher zufällig, weil ActiveSheet gerade auf Sheet1 zeigt.
    Ein falscher Mausklick während der Ausführung und es gibt Chaos.
    Das heißt besser noch auf das Worksheet verweisen?

    Zitat repariert. ~Thunderbolt

    Dieser Beitrag wurde bereits 1 mal editiert, zuletzt von „Thunderbolt“ ()

    cry.baby schrieb:

    Was heißt elegant?
    Beide Workbooks sind offen und damit beide Sheets erreichbar.
    Es gibt keine Notwendigkeit für einen Zwischenpuffer.

    cry.baby schrieb:

    Was hat das zu bedeuten?
    Ungarische Notation ist die Methode, den Variablen Prefixes mit dem Datentyp voranzustellen.
    In Zeiten moderner IDEs ist das Humbug.
    Und erschwert das Lesen.
    Schau dir mal die Excel-internen Objekte und deren Properties an.
    Du wirst keine ungarische Notation finden.
    Aus gutem Grund.
    In den Microsoft Naming Conventions steht irgendwo explizit
    ​Do not use Hungarian Notation!.

    cry.baby schrieb:

    besser noch auf das Worksheet verweisen?
    Auf jeden Fall.
    --
    If Not Program.isWorking Then Code.Debug Else Code.DoNotTouch
    --
    Ich finde die Prefixes zur Zeit noch sehr nützlich. Bin VBA-Anfänger und wenn ich mir später meinen Code anschaue ist es für mich sinnvoll auf den ersten Blick zu wissen, ob eine Variable als Objekt deklariert wurde oder Text, Zahl etc. -> das erleichtert mir das Verständnis

    Unnötiges Zitat entfernt. ~Thunderbolt

    Dieser Beitrag wurde bereits 1 mal editiert, zuletzt von „Thunderbolt“ ()

    Wie gesagt, es ist eine eher philosophische Diskussion, auf die es keine eindeutiges richtig oder falsch gibt.

    Spätestens, wenn du dich von der prozeduralen Programmierung verabschiedest und objektorientiert arbeitest, wirst du wahrscheinlich auch etwas umdenken.

    Wenn Microsoft genauso denken würde, würde aus der Konstruktion

    Visual Basic-Quellcode

    1. Set wb = Workbooks("Zusammenfassung")
    2. Set ws = wb.Sheets(1)
    3. LastRow = ws.Cells(Rows.Count,1).End(xlUp).Row

    so was:

    Visual Basic-Quellcode

    1. ​Set wkbWb = collWorkbooks("Zusammenfassung")
    2. Set wksWs = wkbWb.collSheets(1)
    3. lngLastRow = wksWs.rngCells(rngRows.lngCount,1).lngEnd(constXlUp).lngRow

    oder so ähnlich.
    Ich weiss jetzt nicht, was lesbarer ist...
    --
    If Not Program.isWorking Then Code.Debug Else Code.DoNotTouch
    --
    Zugegeben, Letzteres is ne Qual wenn man das so betrachtet. 8|

    Welche Schreibweise ist dann in VB-Kreisen 'etabliert'?

    Pascal case exampleHotelManager, DetergentQuality, AccountNumber etc. Capitalize first character of important words

    Camel case examplehotelManager, detergentQuality, AccountNumber etc. Start with lower case and then capitalize first character of important words

    Hungarian notation exampleAn input text box – txtAccountNumber, an array – arrayCustomers etc. Just don’t use this. Just…please don’t. <- :D

    Vollzitat entfernt. Die Farbe „Rot“ ist der Moderation vorbehalten → Farbe geändert. ~Thunderbolt

    Dieser Beitrag wurde bereits 1 mal editiert, zuletzt von „Thunderbolt“ ()

    cry.baby schrieb:

    Welche Schreibweise ist dann in VB-Kreisen 'etabliert'?
    Üblicherweise Pascal Case.
    https://docs.microsoft.com/en-us/dotnet/visual-basic/programming-guide/program-structure/naming-conventions

    Edit:
    Das Coding von Microsoft Frameworks (.Net, Excel, WinApi...) unterliegt einem noch strengeren Standard:
    docs.microsoft.com/en-us/dotne…eneral-naming-conventions

    --
    If Not Program.isWorking Then Code.Debug Else Code.DoNotTouch
    --

    Dieser Beitrag wurde bereits 1 mal editiert, zuletzt von „petaod“ ()