jeema
05-06-2010, 02:10 AM
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
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
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
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
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
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