I have a workbook, with a bunch of VBA code that refreshes the data in the
Auto_Open event. Once opened, I would like for the end user to be able to
save an archived copy of all of the data, spreadsheets and graphs in the
workbook, but with all the VBA code removed.

Whatever method is used, I would also need to make sure they don't overwrite
the original spreadsheet as well.

Any ideas? Thanks!

Dim sName as String, bk as Workbook
Dim VBComp As Object
Dim VBComps As Object
Application.ScreenUpdating = False
sName = Left(thisworkbook.Fullname,len(thisworkbook.Fullna me)-4) & _
format(now,"yyyymmdd_hhmmss") & ".xls"
ActiveWorkbook.SaveCopyAs sName
set bk = workbooks.Open(sName)
Set VBComps = bk.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

bk.Close SaveChanges:=True
Application.ScreenUpdating = True


Part of the code is from Chip Pearson's site:
http://www.cpearson.com/excel/vbe.htm

--
Regards,
Tom Ogilvy




"Joel" wrote in message
...
I have a workbook, with a bunch of VBA code that refreshes the data in
the
Auto_Open event. Once opened, I would like for the end user to be able to
save an archived copy of all of the data, spreadsheets and graphs in the
workbook, but with all the VBA code removed.

Whatever method is used, I would also need to make sure they don't
overwrite
the original spreadsheet as well.

Any ideas? Thanks!