How to delete macros from thousands of files?

  • Thread starter Thread starter FinnLisa
  • Start date Start date
F

FinnLisa

I need to find a way to delete all the macros from several thousand Word and
Excel files in one operation. Is there a way to do this? (Please bear in mind
that I am severely programming-impaired. Type slowly and loudly!)
 
Excel questions should be addressed to the Excel forums.
Are you sure the macros are stored in the documents and not in the document
template?
Which Word version?
--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP

My web site www.gmayor.com

<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
 
Yes, they're in the documents themselves, which are mostly Word 2003 but
could be in other versions as well.
 
One possibilty is to save as RTF which does not support macros. The
following will do that.

*May I suggest that you copy the documents into a folder and run the macro
on copies of the documents in case there are some formatting anomalies.*

Although Word will open RTF docs without problem, you may then wish to
change the line
oDoc.SaveAs strPath & strFileName, wdFormatRTF
to
oDoc.SaveAs strPath & strFileName, wdFormatDocument
and re-run the macro to save all as Word document format - without the
macros.

Sub BatchProcess()
Dim strFileName As String
Dim strPath As String
Dim oDoc As Document
Dim fDialog As FileDialog
Set fDialog = Application.FileDialog(msoFileDialogFolderPicker)

With fDialog
.Title = "Select folder and click OK"
.AllowMultiSelect = False
.InitialView = msoFileDialogViewList
If .Show <> -1 Then
MsgBox "Cancelled By User", , _
"List Folder Contents"
Exit Sub
End If
strPath = fDialog.SelectedItems.Item(1)
If Right(strPath, 1) <> "\" _
Then strPath = strPath + "\"
End With

If Documents.Count > 0 Then
Documents.Close SaveChanges:=wdPromptToSaveChanges
End If
If Left(strPath, 1) = Chr(34) Then
strPath = Mid(strPath, 2, Len(strPath) - 2)
End If
strFileName = Dir$(strPath & "*.doc")

While Len(strFileName) <> 0
Set oDoc = Documents.Open(strPath & strFileName)
Application.DisplayAlerts = wdAlertsNone
oDoc.SaveAs strPath & strFileName, wdFormatRTF
Application.DisplayAlerts = wdAlertsAll
oDoc.Close SaveChanges:=wdDoNotSaveChanges
strFileName = Dir$()
Wend
End Sub

Another method uses some code I borrowed from an on-line source to delete
the macros individually from the document. This has the advantage of
minimizing any formatting anomalies. In order to run this macro, you will
need to add a reference to
Microsoft Visual Basic for Applications Extensibility from the vba editor >
tools > options

Sub BatchProcess()
Dim strFileName As String
Dim strPath As String
Dim oDoc As Document
Dim VBComps As VBComponents
Dim VBComp As VBComponent
Dim fDialog As FileDialog
Set fDialog = Application.FileDialog(msoFileDialogFolderPicker)

With fDialog
.Title = "Select folder and click OK"
.AllowMultiSelect = False
.InitialView = msoFileDialogViewList
If .Show <> -1 Then
MsgBox "Cancelled By User", , _
"List Folder Contents"
Exit Sub
End If
strPath = fDialog.SelectedItems.Item(1)
If Right(strPath, 1) <> "\" _
Then strPath = strPath + "\"
End With

If Documents.Count > 0 Then
Documents.Close SaveChanges:=wdPromptToSaveChanges
End If
If Left(strPath, 1) = Chr(34) Then
strPath = Mid(strPath, 2, Len(strPath) - 2)
End If
strFileName = Dir$(strPath & "*.doc")

While Len(strFileName) <> 0
Set oDoc = Documents.Open(strPath & strFileName)
Set VBComps = ActiveDocument.VBProject.VBComponents
For Each VBComp In VBComps
Select Case VBComp.Type
Case vbext_ct_StdModule, _
vbext_ct_MSForm, _
vbext_ct_ClassModule
VBComps.Remove VBComp
Case Else
With VBComp.CodeModule
.DeleteLines 1, .CountOfLines
End With
End Select
Next VBComp
oDoc.Close SaveChanges:=wdSaveChanges
strFileName = Dir$()
Wend
End Sub

Again I recommend running the macro on COPIES of the documents.
--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP

My web site www.gmayor.com

<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
 
Back
Top