Microsoft Office Tutorials and References
In Depth Information
Copying a Workbook with Macros Removed
'(Security setting in Excel XP and above)¶
On Error Resume Next¶
'This line fails if access does not exist¶
Set VBC = ActiveWorkbook.VBProject.VBComponents(1)¶
On Error GoTo err_h¶
If VBC Is Nothing Then¶
'Access denied, ask the user to turn it on¶
MsgBox "Access to the VB Project is not allowed." & _¶
vbNewLine & vbNewLine & _¶
"To allow it, go to Tools | Macro, Security..., " & _¶
"and check the 'Trust access to the Visual Basic Project'" & _¶
" checkbox under the 'Trusted sources' tab", vbCritical¶
Exit Sub¶
End If¶
'Release memory¶
Set VBC = Nothing¶
'Save the workbook with the filename that the user indicated¶
ActiveWorkbook.SaveCopyAs Ans¶
'Now open it (disable events just in case)¶
Application.EnableEvents = False¶
Set WB = Workbooks.Open(Ans)¶
'Turn events back on¶
Application.EnableEvents = True¶
For Each VBC In WB.VBProject.VBComponents¶
Select Case VBC.Type¶
Case 1, 2, 3 'vbext_ct_StdModule, vbext_ct_ClassModule,¶
'Object is a standard module, class module or UserForm,¶
'so remove it¶
WB.VBProject.VBComponents.Remove VBC¶
Case Else 'vbext_ct_ActiveXDesigner, vbext_ct_Document¶
'everything else (Workbook or Sheet module), delete¶
'its contents¶
With VBC.CodeModule¶
.DeleteLines 1, .CountOfLines¶
End With¶
End Select¶
Next VBC¶
'Save the Workbook¶
'Close it¶
WB.Close SaveChanges:=False¶
'Done !¶
MsgBox Prompt:="Done !"¶
Exit Sub¶
MsgBox "Error " & Err.Number & ", " & Err.Description, vbCritical¶
End Sub¶
Search JabSto ::

Custom Search