Vornehmen von Änderungen in einer Gruppe von Arbeitsmappen (Microsoft Excel)
Im Laufe der Zeit ist es sehr einfach, eine große Anzahl von Excel-Arbeitsmappen zu erstellen und zu sammeln. Angenommen, Sie hatten eine ganze Reihe von Arbeitsmappen, in denen Sie dieselbe Änderung vornehmen mussten. Beispielsweise müssen Sie möglicherweise den in Zelle A10 jedes Arbeitsblatts in jeder Arbeitsmappe gespeicherten Wert ändern.
Wenn Sie nur wenige Arbeitsmappen ändern mussten, ist die Aufgabe ziemlich einfach: Laden Sie jede Arbeitsmappe und nehmen Sie die Änderung an jeder dieser Arbeitsmappen vor. Wenn Sie über ein paar hundert Arbeitsmappen verfügen, in denen die Änderung vorgenommen werden muss, wird die Aufgabe schwieriger.
Wenn Sie davon ausgehen, dass Sie diese Aufgabe nur einmal ausführen müssen, besteht die einfachste Lösung darin, eine Textdatei zu erstellen, die den Pfad und den Dateinamen jeder Arbeitsmappe enthält, eine Arbeitsmappe pro Zeile. Beispielsweise könnten Sie eine Datei mit Einträgen wie diesen erhalten:
c:\myfiles\first workbook.xlsx c:\myfiles\second workbook.xlsx c:\myfiles\third workbook.xlsx
Die Datei kann so viele Zeilen wie nötig enthalten. es ist nicht wirklich wichtig. Wichtig ist, dass jede Zeile ein gültiger Pfad und Dateiname ist und dass die Datei keine Leerzeilen enthält.
Sie können eine solche Datei am einfachsten erstellen, indem Sie ein Eingabeaufforderungsfenster anzeigen, zu dem Verzeichnis mit den Arbeitsmappen navigieren und den folgenden Befehl eingeben:
dir /b > myfilelist.txt
Jede Datei im Verzeichnis landet in der Datei myfilelist.txt. Sie müssen die Textdatei in einen Texteditor laden und auschecken, damit Sie überflüssige Einträge löschen können. (Zum Beispiel wird myfilelist.txt in der Liste angezeigt.) Sie müssen auch den Pfadnamen am Anfang jeder Zeile in der Datei hinzufügen.
Sobald die Datei vollständig ist, können Sie Excel starten und die Textdatei mit einem Makro lesen, jede in der Textdatei aufgeführte Arbeitsmappe laden, jedes Arbeitsblatt in dieser Arbeitsmappe durchgehen, die entsprechenden Änderungen vornehmen und die Arbeitsmappe speichern. Das folgende Makro führt diese Aufgaben gut aus.
Sub ChangeFiles1() Dim sFilename As String Dim wks As Worksheet Open "c:\myfiles\myfilelist.txt" For Input As #1 Do While Not EOF(1) Input #1, sFilename ' Get workbook path and name Workbooks.Open sFilename With ActiveWorkbook For Each wks In .Worksheets ' Specify the change to make wks.Range("A1").Value = "A1 Changed" Next End With ActiveWorkbook.Close SaveChanges:=True Loop Close #1 End Sub
Dieser Ansatz funktioniert zwar hervorragend, wenn Sie nur einen einzigen Stapel von Arbeitsmappendateien verarbeiten müssen, er kann jedoch wesentlich flexibler gestaltet werden, wenn Sie davon ausgehen, dass Sie in Zukunft solche Änderungen vornehmen müssen. Der größte Aufwand besteht natürlich darin, die Datei myfilelist.txt jedes Mal zusammenzustellen, wenn Sie einen Stapel von Dateien verarbeiten möchten. Flexibilität wird hinzugefügt, wenn das Makro einfach ein Verzeichnis verwenden und dann jede Arbeitsmappe aus diesem Verzeichnis laden könnte.
Sub ChangeFiles2() Dim MyPath As String Dim MyFile As String Dim dirName As String Dim wks As Worksheet ' Change directory path as desired dirName = "c:\myfiles\" MyPath = dirName & "*.xlsx" MyFile = Dir(MyPath) If MyFile > "" Then MyFile = dirName & MyFile Do While MyFile <> "" If Len(MyFile) = 0 Then Exit Do Workbooks.Open MyFile With ActiveWorkbook For Each wks In .Worksheets ' Specify the change to make wks.Range("A1").Value = "A1 Changed" Next End With ActiveWorkbook.Close SaveChanges:=True MyFile = Dir If MyFile > "" Then MyFile = dirName & MyFile Loop End Sub
Dieses Makro verwendet das Verzeichnis, das Sie für die Variable dirName angegeben haben.
Jede Arbeitsmappendatei (die mit der Erweiterung .Xlsx endet) wird geladen und verarbeitet.
Ein anderer Ansatz besteht darin, das Makro den Benutzer fragen zu lassen, welches Verzeichnis verarbeitet werden soll. Sie können dazu das Standarddialogfeld Excel-Datei verwenden, wie im folgenden Makro gezeigt.
Public Sub ChangeFiles3() Dim MyPath As String Dim MyFile As String Dim dirName As String With Application.FileDialog(msoFileDialogFolderPicker) ' Optional: set folder to start in .InitialFileName = "C:\Excel\" .Title = "Select the folder to process" If .Show = True Then dirName = .SelectedItems(1) End If End With MyPath = dirName & "\*.xlsx" myFile = Dir(MyPath) If MyFile > "" Then MyFile = dirName & MyFile Do While MyFile <> "" If Len(MyFile) = 0 Then Exit Do Workbooks.Open MyFile With ActiveWorkbook For Each wks In .Worksheets ' Specify the change to make wks.Range("A1").Value = "A1 Changed" Next End With ActiveWorkbook.Close SaveChanges:=True MyFile = Dir If MyFile > "" Then MyFile = dirName & MyFile Loop End Sub
_Hinweis: _
Wenn Sie wissen möchten, wie die auf dieser Seite (oder auf einer anderen Seite der ExcelTips-Websites) beschriebenen Makros verwendet werden, habe ich eine spezielle Seite vorbereitet, die hilfreiche Informationen enthält.
ExcelTips ist Ihre Quelle für kostengünstige Microsoft Excel-Schulungen.
Dieser Tipp (8939) gilt für Microsoft Excel 2007, 2010, 2013, 2016, 2019 und Excel in Office 365. Eine Version dieses Tipps für die ältere Menüoberfläche von Excel finden Sie hier: