Cannot delete a module when running the code in module with same name

[timotai]

Hi all

Heres a Brain teaser:

I have a spreadsheet that I copy through code and then edit the copy and delete the macros. The reason I want the macros delted on the copy is because I am sending it over e-mail and some of the exhange servers of the people I am sending it to do not allow VB code in attachments and so reject the mail.

Here is my code:

Application.Workbooks(savename & ".xls").VBProject.VBComponents.Remove Application.Workbooks(savename & ".xls").VBProject.VBComponents("Module1")
Application.Workbooks(savename & ".xls").VBProject.VBComponents.Remove Application.Workbooks(savename & ".xls").VBProject.VBComponents("Module2")
Application.Workbooks(savename & ".xls").VBProject.VBComponents.Remove Application.Workbooks(savename & ".xls").VBProject.VBComponents("Module5")
Application.Workbooks(savename & ".xls").VBProject.VBComponents.Remove Application.Workbooks(savename & ".xls").VBProject.VBComponents("Module8")

Module 2, 5, and 8 will delete however module 1 won't. I believe that this is related to the fact that the code above resides in module 1 in the master copy. Now to set one thing right I am running the code on the master copy so I am not trying to delete the same macro I am running.

I did atttempt to put code in module 5 to delete module 1 but then it didn't remove module 1 and 5.

I have used the following code to delete the text:

Set VBCodeMod = Application.Workbooks(savename & ".xls").VBProject.VBComponents("Module1").CodeModule
With VBCodeMod
    StartLine = 1
    HowManyLines = .CountOfLines
   .DeleteLines StartLine, HowManyLines
End With

And this works but the module still stays there, it wont delete.

So where I am so far is that as far as I am aware I can get rid of all the VB code, but because there is a module there Excel still pops up a warning message when I open it saying that there is a macro on the spreadsheet and do I want to enable it.

So my question is:

[COLOR=red yellow]
How do I Remove module 1?
[/color]​

]

 

[TonyJollans]

Hi timotai,

No need to delete the code if you'r going to delete the module, so just try this ..

[blue]With Application.Workbooks(savename & ".xls").VBProject
    Set vbcodemod = .VBComponents("Module1") '.CodeModule
    .VBComponents.Remove (vbcodemod)
End With[/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.

 

[timotai]

Ive tried this but the same happens. It runs the code but the module doesn't delete.

The module is a exact replicar of the one that is running.

What seems crazy to me is that just because they have the same name it seems they won't delete. But they are in different projects. It just doesn't make sense.

Thanks for your help though

 

[TonyJollans]

Hi timotai,

Sorry, I didn't read your post properly. I can't recreate the problem. How are you creating your copy of the workbook? And (in case it makes a difference) what version of Excel are you using?

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.

 

[timotai]

Im using excel 97. I am creating it by using the savecopyas method and then reopening the file to edit it. I have been trying and trying. One idea I have, but don't know if/how it is possible. I was thinking if I could export the worksheet and save it under the name I want there wouldn't be any macros attached to it anyway.

 

[timotai]

I found a post with the following code:

ActiveSheet.Copy
ActiveWorkbook.SaveAs FileName:= _
"\\Resrstredega003\Groupsd2\GIPROJ\TSU\Support Services Weekly & Monthly Reports\WEEKLY_REPORTS\" & Directory & "\" & File & "\" & savename & ".xls"


Saves it without the modules.

Thanks so much for you help though

Tim

 

[TonyJollans]

Hi Tim,

Yes, that will work if you only have a single sheet in your workbook. I tested doing as you described earlier and had no problems in Excel 2003 or 2000 but am not currently able to test on 97 - I will try it later.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.

 

[QuakeReaper]

Timotai,

I have posted my findings (on another forum) on a similar problem that I had. Here it is. I hope this helps you out.

http://www.visualbasicforum.com/showthread.php?p=931066#post931066

Good luck.

^..^ ^..^ ^..^ ^..^ ^..^ ^..^ ^..^ ^..^ ^..^