Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1144to1148
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Makro und Abfrage externe Daten mit Makro löschen

Makro und Abfrage externe Daten mit Makro löschen
Joni
Hi,
ich habe alte Arbeitsmappen in denen Makros und Abfragen auf externe Daten vorhanden sind. Die Blätter aus diesen Mappen müssen in eine neue Mappe übernommen werden. Die neue Mappe hat ihre eigenen Makros. Da die Makros und die Abfrage der externen Daten im Blatt gespeichert sind werden diese mit in die neue Mappe übernommen. Das ist aber nicht sinnvoll.
Gibt es eine Möglichkeit mittels Makro alle in der alten Mappe vorhandenen Makros und Abfragen auf die externen Daten zu löschen?. Die Werte der externen Daten sollten stehen bleiben und einfach nicht mehr aktualisiert werden.
Das alles händisch zu machen ist sehr umfangreich, da es sich um ca. 500 Mappen handelt.
Es wäre auch möglich aus allen alten Mappen die Makros/Abfragen auf einmal (in einem Sammeldurchlauf) zu löschen. Die Dateien lassen sich zusammensuchen und anschließend wieder verteilen.
Danke für eure Hilfe!
Gruß Joni

4
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Benutzer
Anzeige
Makro und Abfragen per VBA löschen
15.03.2010 17:19:17
Martins
Hallo Joni,
ich habe mal auf die Schnelle einen kleinen (ungetesteten !) VBA-Code zum Entfernen der QueryTables und der VBA-Codes aus den Klassenmodulen der Tabellenblätter entworfen.
Ich denke mal, dass der Code grundsätzlich funktionieren sollte - abgesehen von allgemeinen Schwierigkeiten wie z.B. Datei-Leseschutz, Blattschutz, geschützte VBA-Codes, Option "Zugriff auf VBA-Projekt vertrauen" etc. Kopiere dazu den folgenden Code in ein allgemeines Modul (z.B. "Modul1") im VBA-Editor (Alt+F11) und führe ihn dann aus.
Den Pfad der Dateien strPfad = "C:\Dein\Ordner\" musst Du natürlich VOR Ausführung des Codes anpassen ;-)
Teste den Code bitte zuvor an einer Kopie Deiner Daten und beschreibe ggf. ob und an welcher Stelle Fehler auftreten :
Sub QueryTablesUndVBACodeAusSheetsEntfernen()
'15.03.2010, NoNet - www.excelei.de
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
strPfad = "C:\Dein\Ordner\" 'Pfad der Dateien bitte anpassen - mit "\" am Ende !
On Error Resume Next    'Falls Fehler auftreten : Ohne Fehlermedlung fortsetzen
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 aus Tabellenblatt löschen :
For Each wsQ In ws.QueryTables
wsQ.Delete
bolAenderung = True
Next
'Prüfen, ob VBA-Code im Klassenmodul des Blattes enthalten ist und ggf. löschen
intAnz = wbExt.VBProject.VBComponents(ws.Name).CodeModule.CountOfLines
If intAnz > 0 Then
wbExt.VBProject.VBComponents(ws.Name).CodeModule.DeleteLines 1, intAnz
bolAenderung = True
End If
If bolAenderung Then
wbExt.Save 'Speichern, falls etwas geändert wurde
strGeanderteDateien = strGeanderteDateien & wbExt.Name & vbCr
End If
wbExt.Close False 'Schließen ohne nochmal zu speichern
Next
strDateiname = Dir 'Nächste Datei überprüfen
Wend
MsgBox "Es wurden " & UBound(Split(strGeanderteDateien, vbCr)) & _
"Dateien modifiziert !", vbOKOnly, "Modifikation abgeschlossen :"
Workbooks.Add
Sheets(1).[A1].Resize(UBound(Split(strGeanderteDateien, vbCr))) = _
Split(strGeanderteDateien, vbCr) 'Geänderte Datei ausgeben
End Sub
Gruß, NoNet
Anzeige
Korrektur : Code war von MIR und nicht vom Nachbar
15.03.2010 17:20:13
MIR
_oT = "ohne Text"
AW: Korrektur : Code war von MIR und nicht vom Nachbar
15.03.2010 18:29:53
MIR
Hi NoNet,
hier der erste Zwischenbericht:
kleines Problem:
Die Dateien werden alle abgearbeitet. Im Protokoll wird aber immer die gleiche Datei (sprich so oft wie Änderungen vorgenommen wurden) angeführt.
großes Problem:
Es wurden keine Makros/Abfragen gelöscht. Es ist zwar so, dass wenn ich die Dateien nochmal durchlaufen lasse er keine Meldung mehr bringt, dass etwas geändert wurde, doch die Makros und Abfragen sind immer noch alle da.
Gruß Joni
Code zum Löschen von Queries/VBA ergänzt
15.03.2010 23:12:12
Queries/VBA
Hallo Joni,
da hast Du Recht, es waren noch einige kleine Fehler enthalten.
Ich habe den Code jetzt nochmal modifiziert und getestet, nun funktioniert er (fast) einwandfrei :
Sub QueryTablesUndVBACodeAusSheetsEntfernen()
'15.03.2010, NoNet - www.excelei.de
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 - mit "\" am Ende !
On Error Resume Next    'Falls Fehler auftreten : Ohne Fehlermedlung fortsetzen
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 aus Tabellenblatt löschen :
For Each wsQ In ws.QueryTables
wsQ.Delete
bolAenderung = True
Next
'Prüfen, ob VBA-Code im Klassenmodul des Blattes enthalten 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
For Each sh In ws.Shapes
sh.OnAction = "" 'Zugewiesene Makros von Objekten löschen
Next
Next
If bolAenderung Then
wbExt.Save 'Speichern, falls etwas geändert wurde
strGeanderteDateien = strGeanderteDateien & wbExt.Name & vbCr
End If
wbExt.Close False 'Schließen ohne nochmal 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 Datei  _
ausgeben
End If
MsgBox "Es wurden " & UBound(Split(strGeanderteDateien, vbCr)) & _
" Dateien modifiziert !", vbOKOnly, "Modifikation abgeschlossen :"
End Sub

Einziger Schönheitsfehler : Wenn man die Dateien nun öffnet erscheint weiterhin die Makro-Meldung, auch dann wenn gar keine Makros mehr enthalten sind. Speichert man die Datei dann ohne Änderung nochmal, erscheint die Meldung beim nächsten Öffnen nicht mehr....
Beachte auch bitte, dass man (wie in der ersten Antwort erwähnt) per "Extras - Makros - Sicherheit" den Zugriff auf das VB-Projekt zulassen muss, damit man per VBA anderen VBA-Code löschen kann :
Userbild
Gruß, NoNet
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige