Sleeper: Accessing an Excel Module

[wleearmitage]

What I need to do is run a procedure from one Workbook that will open up another workbook and then open up an existing module for that workbook.

I have the code to open the workbook and to open the VBE environment window.

Anyone know how to open up a specific module, and pass the password if it is protected?

I am looking at some of the following lines of code but cannot get any of them to work in part or in hole.

Application.VBE.VBProjects.Open
VBProject.VBComponents("ModuleName").CodeModule

Thanks,

Lee

[Richie(UK)]

Hi Lee,

Welcome to the forum.

For a good primer on this topic have a look at Chip Pearson's site.

Note, however, that there is no built-in way to lock and unlock VBAProjects, even if you do have the password. There simply aren't the required methods/properties. (There is a workaround using SendKeys - but this is not a 100% reliable method and most (all?) professionals would avoid its use.)

[Zack Barresse]

.. and most (all?) professionals would avoid its use.)

Rather unfortunately, this isn't always the case; as sad as it is. A related example of setting a VBA project password can be found here.

Richie, on an unrelated topic (while I have you here ) you posted some syntax one time on this site for opening a workbook with only one sheet (a template type/name). I can't find it anymore and forgot to save it for myself. I can also neither find it in any help files or in any other forum. Do you remember what I'm talking about?

[Richie(UK)]

Hi Zack,

Well, there will always be creative people who try to work around the limitations that face them. Sometimes what the create may be useful to others, sometimes not.

I see I missed an interesting thread there (some people get a little tetchy don't they?).

I think the code that you refer to was something like this:

Sub Test()
    Dim wbk As Workbook
Set wbk = Workbooks.Add(xlWBATWorksheet)
    MsgBox "New book sheet count = " & wbk.Worksheets.Count
End Sub

You can find it in the Help files if you dig - look up the Add method and then select the Workbooks object for 'Applies to'. You will see that a number of template constants can be given as an argument. If you don't specify a template you get a 'normal' workbook with the number of sheets determined by the users preferences.

Its tricky searching forums when you can't quite remember what it is that you are actually trying to find - been there, done that. Nearly as bad as those old text-based adventure games on the good old C64 :

Enter the Green room (The door is closed.)
Open the door (I don't understand!).
Open the Green door (The Green door is locked.)
Unlock the Green door (You can't unlock the Green door.)
Unlock the Green door with the Green key (You have unlocked the Green door.)
Enter the Green room (The door is closed.)
Open the Green door (You have opened the Green door.)
Enter the Green room (The Green ogre has killed you. The End!)

Aaaaaarrrrggghhhhh!!!!!!!!!!!!!!! (Richie has smashed the computer. The End!)

[Zack Barresse]

ROFLMAO!!!

Thanks Richie!

[/hijack]

[johnske]

....I see I missed an interesting thread there (some people get a little tetchy don't they?).....

ROFLMFAO They do indeed, I missed that one too, or I wooda put my 2p in as well...