Import Module

[WoodyHays]

I have read your VBA macros tips dated 03/26/00 titled Programmatically copy a macro to a new workbook.

Great, but it just adds the new module. How can I delete the first module via the macro to prevent a build up of old modules?

The example code is written to be executed externally of the target workbooks. I want to put the import code in the workbook module so that the user can update the module when necessary via a button on a sheet. The users will have moved the file to various places and I won't know the path so I need for them to be able to manage the import independently.

This is a great way to keep distributed files current. A great tool!!

Does this make sense?

Thanks,
Woody Hays

[Tom Schreiner]

Hi Woody.
See Ivan's reply...

http://www.mrexcel.com/board/viewtop...8364&forum=2&5

Tom

[NateO]

This has a lot of goods with respect to programming to the vbe, see deleting module from a project, this way, you don't have to blast everything out (if you don't want to).

No disrespect to Ivan's code (it looks solid (as usual) ), this just gives you some options.

http://www.cpearson.com/excel/vbe.htm

_________________
Cheers, NateO

[ This Message was edited by: NateO on 2002-05-15 16:40 ]

[WoodyHays]

Thanks, I was able to get the code working to rename the current module, import a revised module and delete the old, renamed, one.

It all works great except that I have the project protected and the macro won't execute with the project protected. I need to put the macro in the Auto_open macro, but I can't.

I have tried many iterations of the following and can't get it to work.

"ActiveWorkbook.VBProject.VBE.Potection.UnProtect ("Password")"

What is the syntax to unprotect a project?

Thanks,
wgh

[NateO]

Syntax isn't quite the issue, you need a procedure to go with yours. Our MVP Ivan has two [choice] suggestions. See:

http://www.mrexcel.com/board/viewtop...c=5559&forum=2

[WoodyHays]

Great answer! No wonder I couldn't figure it out. One problem though. When I run the code, below, it inserts the password on the line where the cursor is with every execution.

I assume "test" is the password in this case.

Something must need changing, but I don't have a clue what SendKeys is doing. I will research it, but if you have an idea what to do here I would apprecaite some guidance. Maybe a buffer needs to get cleared?


Sub UnprotectVBProject()
Dim wbproj As Object

On Error Resume Next
Set wbproj = ActiveWorkbook.VBProject
Application.SendKeys "test", True
Application.SendKeys "~", True
wbproj.VBE.SelectedVBComponent.Activate
On Error GoTo 0

End Sub

Thanks, This is an awsome site.
wgh

[WoodyHays]

Never mind. The code worked perfectly, I just didn't know how to use it correctly.

Never used "SendKeys" before. Very interesting.
wgh