PDA

View Full Version : Delete VBA Code of another Excel



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

GTO
05-06-2010, 03:25 AM
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
05-06-2010, 03:26 AM
Set VBProj = Workbooks("MyOtherWorkbook.xls").VBProject

GTO
05-06-2010, 03:33 AM
Or, maybe we don't need to see the rest...:omg2:

Bob Phillips
05-06-2010, 03:42 AM
Or, maybe we don't need to see the rest...:omg2:

... is that 'shooting the bull'? :devil2:

jeema
05-06-2010, 06:53 PM
I use the below code to copy


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


I use the below code to delete the 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

It is not working even I use the below code


Set VBProj = Workbooks(TargetFileName).VBProject


Please help.

Regards

GTO
05-06-2010, 09:58 PM
... is that 'shooting the bull'? :devil2:

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


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