Delete VBA Code of another Excel

[jeema]

Hi,

I am writing VBA to copy the current Excel to a different location.

After copying I need to remove the VBA code of the excel in the target location.

I use the code from cpearson.com/excel/vbe.aspx

[VBA]Dim VBComp As VBIDE.VBComponent
Dim CodeMod As VBIDE.CodeModule

Set VBProj = ActiveWorkbook.VBProject

For Each VBComp In VBProj.VBComponents
If VBComp.Type = vbext_ct_Document Then
Set CodeMod = VBComp.CodeModule
With CodeMod
.DeleteLines 1, .CountOfLines
End With
Else
VBProj.VBComponents.Remove VBComp
End If
Next VBComp[/VBA]
The problem is if I use ActiveWorkbook.VBProject it uses the current workbook and it deletes the VBS in current workbook.

But I need to delete the VBA code from a different excel in a different place.

Could someone help me??

Regards

[GTO]

I am writing VBA to copy the current Excel to a different location...

Can you show us how you are doing this bit? An example workbook would be nicest, or at least the code.

Thanks,

Mark

[Bob Phillips]

[vba]

Set VBProj = Workbooks("MyOtherWorkbook.xls").VBProject
[/vba]

[GTO]

Or, maybe we don't need to see the rest...

[Bob Phillips]

Or, maybe we don't need to see the rest...

... is that 'shooting the bull'?

[jeema]

I use the below code to copy


[VBA]Sub TheSub()
ActiveWorkbook.Save

On Error Resume Next
Application.ScreenUpdating = False
TargetFileName = "c:\test\copy_" & ThisWorkbook.Name
Workbooks(ThisWorkbook.Name).SaveCopyAs TargetFileName

DeleteAllVBACode (TargetFileName)

End Sub
[/VBA]

I use the below code to delete the VBA


[VBA]Sub DeleteAllVBACode(TargetFileName As String)
Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent
Dim CodeMod As VBIDE.CodeModule

Set VBProj = ActiveWorkbook.VBProject

For Each VBComp In VBProj.VBComponents
If VBComp.Type = vbext_ct_Document Then
Set CodeMod = VBComp.CodeModule
With CodeMod
.DeleteLines 1, .CountOfLines
End With
Else
VBProj.VBComponents.Remove VBComp
End If
Next VBComp
End Sub
[/VBA]
It is not working even I use the below code


[VBA]Set VBProj = Workbooks(TargetFileName).VBProject [/VBA]


Please help.

Regards

[GTO]

... is that 'shooting the bull'?

Well I don't suppose a Bar/Pub is a legal prerequisite , but it sounds nice


Hi jeema,

Check out the VBA Help topic for SaveCopyAs, but in short, it does as named. The copy is saved to disk, but the copy is not opened. You'll need to open the new copy and then work on it.

Hope that helps,

Mark