Try this for the activeworkbook
copy in in a standard module
Chnage this line to the folder where you want the file
TempFilePath = "C:\Users\Ron Desktop\Test\"



Dim wb2 As Workbook

Sub TestDeleteVBA()
'Working in 2000-2010
Dim wb1 As Workbook
Dim TempFilePath As String
Dim TempFileName As String
Dim FileExtStr As String
Dim I As Long

Set wb1 = ActiveWorkbook

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

'Make a copy of the file/Open it//Delete VBA
'If you want to change the file name then change only TempFileName
TempFilePath = "C:\Users\Ron Desktop\Test\"
TempFileName = "Copy of " & wb1.Name & " " & Format(Now, "dd-mmm-yy h-mm-ss")
FileExtStr = "." & LCase(Right(wb1.Name, _
Len(wb1.Name) - InStrRev(wb1.Name, ".", , 1)))

wb1.SaveCopyAs TempFilePath & TempFileName & FileExtStr
Set wb2 = Workbooks.Open(TempFilePath & TempFileName & FileExtStr)

With wb2
Call DeleteAllVBA
.Close SaveChanges:=True
End With

Set wb2 = Nothing

With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub

Public Sub DeleteAllVBA()
Dim VBComp As Object
Dim VBComps As Object
Set VBComps = wb2.VBProject.VBComponents
For Each VBComp In VBComps
Select Case VBComp.Type
Case 1, 3, _
2
VBComps.Remove VBComp
Case Else
With VBComp.CodeModule
.DeleteLines 1, .CountOfLines
End With
End Select
Next VBComp
End Sub








NoodNutt wrote:

Saving & Removing VB Code from WB
20-Apr-10

Hi everyone

1. I am looking for something in VB that will save a copy of my workbook "in
the background" to another location on a network drive.

2. During the SaveAs process delete all VB code from the "Saved As Workbook"

So, would it be easier to just save the WB, then run Chip Pearsons
"DeleteAllVBACode()" whilst the Saved As file is closed before the intended
other user has an opportunity to open it, or can this process be done during
the SaveAs process.

I know Ron DeBruin has handy bits of code that can create a "TempFile" when
e-mailing to others, then kill that "TempFile".

I was toying with the idea of modding it so that I save my file as a
TempFile
Then do the "DeleteAllVBACode()" on the TempFile
Then do the SaveAs over to the other network location
Then kill the "TempFile"

But I am unsure were I would insert the "DeleteAllVBACode()" command-line.

And what would really be super handy is to have it all automated when I
close the workbook at the end of the day by inserting something into the
Workbook_BeforeClose(Cancel As Boolean) function.

Appreciate any help

TIA
Mark.

Previous Posts In This Thread:


Submitted via EggHeadCafe - Software Developer Portal of Choice
IIS 7.0 Extensionless UrlRewriting (Short urls)
http://www.eggheadcafe.com/tutorials...nless-url.aspx