Alte Makros und Abfragen in Excel mit VBA löschen
Schritt-für-Schritt-Anleitung
Um alle Makros und Abfragen auf externe Daten in alten Excel-Arbeitsmappen zu löschen, kannst Du den folgenden VBA-Code verwenden. Dieser Code durchläuft alle Excel-Dateien in einem bestimmten Ordner und entfernt die entsprechenden Elemente.
- Öffne Excel und drücke
Alt + F11, um den VBA-Editor zu öffnen.
- Erstelle ein neues Modul, indem Du mit der rechten Maustaste auf "VBAProject" klickst und "Einfügen" > "Modul" wählst.
- Kopiere den folgenden Code in das Modul:
Sub QueryTablesUndVBACodeAusSheetsEntfernen()
Dim wbExt As Workbook, ws As Worksheet
Dim strPfad As String, strDateiname As String
Dim strGeanderteDateien As String
Dim intAnz As Integer, wsQ As Excel.QueryTable
Dim bolAenderung As Boolean, sh As Shape
strPfad = "C:\Dein\Ordner\" ' Pfad der Dateien bitte anpassen!
On Error Resume Next
strDateiname = Dir(strPfad & "*.xls*") ' Alle XLS* - Dateien bearbeiten
While strDateiname <> ""
Set wbExt = Application.Workbooks.Open(strPfad & strDateiname)
bolAenderung = False
For Each ws In wbExt.Worksheets
' Alle Abfragen löschen
For Each wsQ In ws.QueryTables
wsQ.Delete
bolAenderung = True
Next
' Prüfen, ob VBA-Code vorhanden ist und ggf. löschen
intAnz = wbExt.VBProject.VBComponents(ws.CodeName).CodeModule.CountOfLines
If intAnz > 0 Then
wbExt.VBProject.VBComponents(ws.CodeName).CodeModule.DeleteLines 1, intAnz
bolAenderung = True
End If
' Zugewiesene Makros von Objekten löschen
For Each sh In ws.Shapes
sh.OnAction = ""
Next
Next
If bolAenderung Then
wbExt.Save ' Speichern, falls Änderungen vorgenommen wurden
strGeanderteDateien = strGeanderteDateien & wbExt.Name & vbCr
End If
wbExt.Close False ' Schließen ohne zu speichern
strDateiname = Dir ' Nächste Datei überprüfen
Wend
If UBound(Split(strGeanderteDateien, vbCr)) > 0 Then
Worksheets.Add
Sheets(1).[A1].Resize(UBound(Split(strGeanderteDateien, vbCr))) = _
Application.Transpose(Split(strGeanderteDateien, vbCr)) ' Geänderte Dateien ausgeben
End If
MsgBox "Es wurden " & UBound(Split(strGeanderteDateien, vbCr)) & _
" Dateien modifiziert!", vbOKOnly, "Modifikation abgeschlossen:"
End Sub
- Passe den
strPfad an den Ordner an, in dem sich Deine Excel-Dateien befinden.
- Führe den Code aus, indem Du im VBA-Editor auf
F5 drückst oder das Makro im Excel-Menü startest.
Häufige Fehler und Lösungen
- Fehler beim Zugriff auf das VBA-Projekt: Stelle sicher, dass in Excel unter "Extras" > "Makros" > "Sicherheit" die Option "Zugriff auf das VBA-Projekt vertrauen" aktiviert ist.
- Makros werden nicht gelöscht: Überprüfe, ob die Dateien Schreibschutz haben. Du musst diese zuerst entsperren.
- Meldung über Makros erscheint weiterhin: Wenn die Meldung trotz Löschung der Makros beim Öffnen der Datei erscheint, speichere die Datei einfach ohne Änderungen erneut.
Alternative Methoden
Wenn Du nicht mit VBA arbeiten möchtest, kannst Du auch manuell die Makros und Abfragen in jeder Datei löschen. Dies ist jedoch bei einer großen Anzahl von Mappen, wie in Deinem Fall 500, sehr zeitaufwendig. Eine andere Möglichkeit wäre, ein spezialisiertes Tool zu verwenden, um Excel-Makros zu verwalten und zu löschen.
Praktische Beispiele
Angenommen, Du hast folgende Dateien in Deinem Ordner:
- Datei1.xlsm
- Datei2.xlsm
- Datei3.xlsm
Durch die Ausführung des obenstehenden VBA-Codes werden alle Makros und Abfragen in diesen Dateien entfernt, und Du erhältst eine Liste der geänderten Dateien.
Tipps für Profis
- Teste den Code immer an einer Kopie Deiner Daten, um Datenverlust zu vermeiden.
- Halte Deine Excel-Version und VBA-Referenzen auf dem neuesten Stand, um Probleme mit dem Code zu vermeiden.
- Verwende die
Debug.Print-Anweisung, um während der Ausführung Informationen im Direktfenster anzuzeigen; dies kann bei der Fehlersuche helfen.
FAQ: Häufige Fragen
1. Kann ich den Code auch für andere Dateiformate verwenden?
Ja, aber Du musst den Filter in der Zeile strDateiname = Dir(strPfad & "*.xls*") anpassen.
2. Gibt es eine Möglichkeit, die Löschaktion rückgängig zu machen?
Leider gibt es keine Rückgängig-Funktion für VBA. Stelle sicher, dass Du immer eine Sicherungskopie Deiner Dateien hast.
3. Funktioniert der Code auch in Excel 365?
Ja, der Code sollte in Excel 365 sowie in früheren Versionen funktionieren, solange die richtigen Berechtigungen gesetzt sind.
